# PostGIS function to find zip codes with radius in miles?

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,
``````

my code :

``````SELECT zip_code from mytable
where ST_PointInsideCircle(ST_Point(mytable.latitude,mytable.longitude), 37.6210700000000031, -122.429737000000003, 5);
``````
• what does this have to do with zip codes? Aug 10, 2017 at 21:17
• The lat/long are from mytable. My table columns: zip_code,lat,long Aug 10, 2017 at 21:49
• That's cool, I don't think that's at all relevant to the question though. You could just as well say, find points within radius in miles? Aug 10, 2017 at 21:51
• Most people work this problem the opposite way. You download the ZCTAs from the census, and then you check to see what ZCTA has the point you're looking for with point-in-polygon search. It's far safer then your circle method. Aug 10, 2017 at 21:52
• I have to create a function (myfunction) in Postgres. Input parameters are lat float, long float and radius float. Function should return all zip codes from mytable within a given radius from lat/long of another zip code.Thank you agian Aug 10, 2017 at 22:56

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)
``````
• no SRID in my table and no SRID in API. Do I need to use a function to find SRID ( ST_SRID). Also, convert miles to meters, any function for that? Thank you for your help Aug 10, 2017 at 22:09
• SRID should be 4326 (WGS84) which is the common geographic coordinate reference that uses degree. Miles to meters... 5*1609 Aug 11, 2017 at 6:15

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:

``````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.

• -122 is not a valid latitude; you need to correct ordinate order, too. Aug 10, 2017 at 21:54
• @Vince ah sure, thanks, I thought I corrected the order...the values are only copypaste though. They are switched now. Aug 11, 2017 at 6:52
• @Yelena Lepina you are welcome! consider accepting the answer that solved your question. Aug 12, 2017 at 20:35