CartoDB geom (coordinates) join: without exact match?

Question

Trying to join two datasets (layers) on CartoDB based on their geographical fields (in CartoDB's the_geom created field). However, the basic ST_Intersection function does not work since its tolerance is too small and these coordinates are off by more than 0.00000001. Is there another function to do this? (Trying to join based on approximate coordinates).

Answer 1

Yes—use a distance-based join instead of exact intersection. Such as the following:

SELECT a.*, b.id AS b_id,

ST_Distance(a.the_geom::geography, b.the_geom::geography) AS dist_m

FROM a

JOIN b

ON ST_DWithin(a.the_geom::geography, b.the_geom::geography, 20); -- 20 m tolerance