0

I don't get why ST_DISTANCE is giving me the same closest node when clearly it is not the one nearest to the starting point. Also, using 2 different starting points, I still have the same result.

My data is this one, I run in my pgadmin

SELECT id, ST_AsText (ST_Transform (the_geom, 4326)) FROM edges_noded order by id;

enter image description here

and I run this commands to get the closest node

SELECT id, 
       ST_Distance(the_geom,ST_SetSRID(ST_MakePoint(124.263770, 8.265921),3857)) AS DIST, 
       ST_AsText (ST_Transform (the_geom, 4326)) 
FROM edges_noded 
LIMIT 1;

SELECT id, 
       ST_Distance(the_geom,ST_SetSRID(ST_MakePoint(124.264505, 8.264620),3857)) AS DIST, 
       ST_AsText (ST_Transform (the_geom, 4326)) 
FROM edges_noded 
LIMIT 1;

these two commands give me the same nearest node.

Does ST_DISTANCE work for this kind of situations?

attached also is a visual of the situation.

the small picture on the bottom left is the paths

1

There are two issues that prevents getting the expected result.

First, you are using lat-long coordinates (4326) but declare them as 3857

ST_SetSRID(ST_MakePoint(124.263770, 8.265921),4326)) AS DIST

Then the query computes the distance to the geom and return the 1st result. Since there is no ordering, a more or less random geom is used, regardless of its distance to the created point.

To get the nearest point, you would need to order by distance and keep the first result. It is a good habit to use the <-> operator as it benefits from the spatial index (less important if only dealing with points though)

Computing distances in lat-long is meaningless as a degree of latitude has a different length - in meters - then a degree of longitude. It is also wrong to compute distances in 3857 as this projection introduces severe distortions. An alternative would be to compute the distance using the geometry datatype.

At last, if you have lots of points, you may want to restrict the distance ordering to nearby locations only.

WITH target as 
  (SELECT ST_SetSRID(ST_Point(124.263770, 8.265921),4326)::geography as the_geog)
SELECT e.id,
       ST_Distance(ST_Transform (e.the_geom, 4326)::geography, target.the_geog) AS dist,
       ST_AsText (ST_Transform (e.the_geom, 4326))
FROM edges_noded AS e, target
--Optionally restrict the ordering to nearby locations
--WHERE ST_DWithin(e.the_geom::geography, target.the_geog, 10000)
ORDER BY ST_Transform (e.the_geom, 4326)::geography <-> target.the_geog
LIMIT 1;
  • I've tried running your code and it shows "Only lon/lat coordinate systems are supported in geography.". and I'm gonna try looking for the wrong part as i wait and hope that maybe you know what's wrong. But thanks anyway – AbdulMalik Mustapha Oct 10 '18 at 16:57
  • 1
    @AbdulMalikMustapha ah yes, it was missing a reprojection. See the updated answer – JGH Oct 10 '18 at 17:32

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.