Search code examples
sqlpostgresqlpostgis

Find records by given latitude and longitude which intersects and circle within 2 mile radius using PostGIS?


I have a Postgres table with some data created by using a shapefile. I need to find all records which intersect within 2 miles radius from a given latitude and longitude. I used some queries including the following one.

SELECT * FROM us_census_zcta WHERE ST_INTERSECTS(geom, CIRCLE(POINT(40.730610, -73.935242), 2));

But none of them worked. What I am doing wrong here? How can I get the results I need?

The SRID is 4269 (NAD 83).

EDIT: After Mike Organek pointed me out that I have switched the lat-long in the above query. And then I tried a few things and the following query gave me 1 record.

SELECT * FROM us_census_zcta WHERE ST_INTERSECTS(geom::geometry, ST_SETSRID(ST_POINT(-73.935242, 40.730610), 4269)::geometry);

But how can I use Circle and find records which intersect within 2 miles radius from that given lat-long?


Solution

  • What you're looking for is ST_DWithin, which will check if records intersect within a given buffer. In order to use it with miles you better cast the geometry column to geography, as it then computes distances in metres:

    For geography: units are in meters and distance measurement defaults to use_spheroid=true. For faster evaluation use use_spheroid=false to measure on the sphere.

    SELECT * FROM us_census_zcta 
    WHERE 
      ST_DWithin(
        geom::geography, 
        ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4269)::geography,
        3218.688); -- ~2 miles
    

    Keep in mind that this cast might affect query performance if the indexes aren't set properly.

    See also: Getting all Buildings in range of 5 miles from specified coordinates