1

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

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
  • 1
    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
  • 1
    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

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.