1

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); 
  • what does this have to do with zip codes? – Evan Carroll Aug 10 '17 at 21:17
  • The lat/long are from mytable. My table columns: zip_code,lat,long – Yelena Lepina Aug 10 '17 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? – Evan Carroll Aug 10 '17 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. – Evan Carroll Aug 10 '17 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 – Yelena Lepina Aug 10 '17 at 22:56
2

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 – Yelena Lepina Aug 10 '17 at 22:09
  • 2
    SRID should be 4326 (WGS84) which is the common geographic coordinate reference that uses degree. Miles to meters... 5*1609 – Matte Aug 11 '17 at 6:15
1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.