3

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?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Abraham Arnold
  • 301
  • 4
  • 20
  • 1
    I am not certain, but unless you are searching US Census data in Antarctica, I think you have latitude and longitude switched. – Mike Organek Jul 12 '22 at 20:48
  • @MikeOrganek Thank you for informing me that mistake. – Abraham Arnold Jul 12 '22 at 21:27
  • 1
    My pleasure. I really was not certain since I have not done anything with GIS in over ten years. My understanding is that the (X, Y) arrangement in geometry means that longitude comes before latitude even though the convention is (latitude, longitude). – Mike Organek Jul 12 '22 at 21:42
  • Yes actually since it is the convention I used as lat-long. It's my first time working with GIS. Thank you again. :) – Abraham Arnold Jul 12 '22 at 21:53

1 Answers1

2

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

Jim Jones
  • 18,404
  • 3
  • 35
  • 44