# postgis calculate distance between point & polygon/circle

By basic requirement is to find the nearby fences to a particular point(user `lat long alt`) within a N meters.

I have list of polygon/circle in DB with 900913 srid

`ST_Force_3D(st_geomfromtext('polygon((' || p_geometrytext || '))', 900913))`

where `p_geometrytext` is a series of `long lat alt` with "," separator.

I have point like this `79.268309 17.057432 0.0` I tried below code

``````select ST_Distance(
ST_SetSRID((select "Fence" from "GeoFence" where "FenceId"=13), 4326),
ST_GeomFromText('POINT(79.268309 17.057432 0.0)', 4326),
true)
``````

I used 4326 as srid, because 900913 is giving error.

I went throw this doc but unable to use it properly. I am new to postgis, can anybody suggest me, how can i do it? or redirect me to where i can get a solution.

EDIT: This is my polygon (`select st_asewkt((select "Fence" from "GeoFence" where "FenceId"=13))`)

`"SRID=900913;POLYGON((78.421783447257 17.420097759577 0,78.528900146476 17.423373494038 0,78.528213500968 17.333597145753 0,78.421783447257 17.332941682216 0,78.422470092765 17.332941682216 0,78.421783447257 17.420097759577 0))"`

• Please do not cross post. stackoverflow.com/questions/27964509/… As I already explained on that post, you need to use ST_Distance_Sphere to get distance in meters between lat/lon points and if one of your coordinate pairs is in 900913 then use ST_Transform to convert to 4326 first. – John Powell Jan 16 '15 at 6:13
• I tried this `select ST_Distance_Sphere(ST_Transform((select "Fence" from "GeoFence" where "FenceId"=13),4326),ST_GeomFromText('POINT(79.268309 17.057432 0.0)',4326))` but it is giving too big value 8867239.50541838, actually then length should be below 90KM. Anything wrong i did? – Dyapa Srikanth Jan 16 '15 at 6:23
• Yes, you need to convert your fence to 4326, see my answer. – John Powell Jan 16 '15 at 6:26

## 1 Answer

If you want to get the distance between two sets of lat/lon, 4326, points, then you can use ST_Distance_Sphere or for more accuracy, ST_Distance_Spheroid. If your fence table is in 900913, then use, ST_Transform to convert, so that both sets of points are in a common SRID.

``````SELECT
ST_Distance_Sphere(
(select ST_Transform("Fence", 4326) FROM "GeoFence" WHERE "FenceId"=13)),
ST_GeomFromText('POINT(79.268309 17.057432 0.0)', 4326))
``````
• But that is not expected value at all, 8867239.50541838/1000 = 8867.23950541838 KM, but value should be below 90KM. – Dyapa Srikanth Jan 16 '15 at 6:29
• Can you show the coordinates (raw, not transformed) for select "Fence" from "GeoFence" where "FenceId" = 13 please. – John Powell Jan 16 '15 at 6:31
• I added that in my question, is that what you are expecting? – Dyapa Srikanth Jan 16 '15 at 6:33
• OK, the problem is that those coordinates are already in 4326, not 900913, which is in meters, and typically has values in the millions. This query, SELECT ST_Distance_Sphere( st_geomfromtext('POLYGON((78.421783447257 17.420097759577 0,78.528900146476 17.423373494038 0,78.528213500968 17.333597145753 0,78.421783447257 17.332941682216 0,78.422470092765 17.332941682216 0,78.421783447257 17.420097759577 0))',4326), ST_GeomFromText('POINT(79.268309 17.057432 0.0)', 4326)); gives 84400.9 meters, is that what you are looking for? – John Powell Jan 16 '15 at 6:44
• I think you GeoFence table has the wrong srid set. Try running postgis.org/docs/UpdateGeometrySRID.html on it, setting it to 4326. – John Powell Jan 16 '15 at 10:00