CODEWITHSUNDEEP
postgresqlpostgis
Stephen Smith
Stephen Smith

Reputation: 387

Search for all records within X distance of any records in a table

I have a table with a bunch of geographies of hospitals (roughly 100 rows), and another table with a bunch of geographies of something else (tens of thousands of rows). How do I select ALL of the latter records that are within X radius of ANY of the former records?

Upvotes: 0

Views: 65

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658322

Use ST_DWithin() from PostGIS:

SELECT *
FROM   whatever w
WHERE  EXISTS (
   SELECT FROM hospital h
   WHERE  ST_DWithin(h.the_geog, w.the_geog, $distance_in_meters)
   );

The EXISTS semi-join is not only (probably) fastest, it also avoids duplicates that might come out of similar queries with a plain (OUTER) JOIN.

You should at least have this spatial GiST index:

CREATE INDEX ON hospital USING gist (the_geog);

Related:

Upvotes: 3

Related Questions