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);
Best Answer
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.