PostGIS function to find zip codes with radius in miles?

Question

I have very little knowledge and understanding Postgres/PostGIS. I am having trouble by using the ST_PointInsideCircle function for radius that uses miles or meters or degrees.
I think this function expects degrees in radius parameter. I have a table with all zip codes with lat/long and I have input with lat/long/radius (miles).
I need to select all the ZIP codes found inside that radius and provided lat/long.

Can anyone help me to find right function or script?

Postgres 9.5 /PostGIS installed.

lat=37.6210700000000031, 
long=-122.429737000000003,
radius=5 miles

my code :

SELECT zip_code from mytable 
  where ST_PointInsideCircle(ST_Point(mytable.latitude,mytable.longitude), 37.6210700000000031, -122.429737000000003, 5); 

Answer 1

The functions are built to use the same units as parameters. As you said yourself the funtion does expect your 5 miles in degrees. You can either transform your miles in degrees. That is dependend on your position on the earth. Or you transform your points to a projected coordinate reference. Then you can use miles (as transformed to meters). The second way might be a bit easier.

Calculating distances depends on where you are to use the correct projection. Taking the coordinates of your example your point is in the UTM zone 10. Using this projection should give you the possibilty to transform your points into projected coordinates and use them for your calculation. The function you need is ST_Transform. In the example it uses the SRID 32610. It will first transfrom your point to UTM zone 10, then creates a point from your coordinates and takes the X and Y coordinate as input for the circle. At the end it translates 5 miles into 8050 meters as UTM is in meters.

ST_PointInsideCircle(ST_Transform(ST_SetSRID(ST_Point(lat,lon),4326),32610),ST_X(ST_Transform(ST_GeomFromText('POINT(-122.429737000000003 37.6210700000000031)',4326),32610)),ST_Y(ST_Transform(ST_GeomFromText('POINT(-122.429737000000003 37.6210700000000031)',4326),32610)),8050)

Answer 2

You really should have had a look around this board, there are so many posts out there with an answer to this task. If you had checked the docs for ST_Buffer, since you intended to use that, you would have read a note about how you should better use ST_DWithin, alongside an example usage...

Anyways, you will have to set the correct SRID for your coordinates and/or transform them if needed. You can use something like this:

SELECT zip_code 
FROM mytable
WHERE ST_DWithin(ST_Transform(ST_SetSRID(ST_Point(mytable.lon, mytable.lat), <epsg>), 4326)::geography,
                 ST_Transform(ST_SetSRID(ST_Point(-122.429737000000003, 37.6210700000000031), <epsg>), 4326), 8046)

where <epsg> is the SRID of your data.


EDIT:

Since you asked for a function in the comments:

CREATE OR REPLACE FUNCTION myfunction(lat NUMERIC, lon NUMERIC, radius DOUBLE PRECISION)
RETURNS TABLE(zip_code INT) AS
  $BODY$
    BEGIN
      RETURN QUERY
        EXECUTE format(
         'SELECT zip_code
          FROM mytable
          WHERE ST_DWithin(ST_SetSRID(ST_Point(lat, lon), 4326)::geography,
                           ST_SetSRID(ST_Point(%1$s, %2$s), 4326)::geography, %3$s * 1609.34)', $1, $2, $3
        );
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

This function assumes all used coordinates are in EPSG:4326 (WGS84)!!!


Call this function like so

SELECT * FROM myfunction(<lat>, <lon>, <radius>);

with <lat>,<lon> in degrees (WGS84) and <radius> in miles.