3

I have been trying to use OSM data to find the closest point to a road for a given point. I imported the OSM data into postgis using osm2pgsql, and I am getting results back, but they are at the absolute edge of my imported data (and the point I'm trying to specify is near the middle) and distance reported is about 8000km. I'm pretty sure it has to be something to do with mixing different coordinate systems but I'm quite new to this so not sure.

The SQL query I'm trying to use is:

SELECT osm_id,
   highway,
   name,
   ref, 
   ST_X(ST_ClosestPoint(ST_Transform(r.way,4326),
       ST_SetSRID(ST_MakePoint(55.938627,-3.198706),4326))),
   ST_Y(ST_ClosestPoint(ST_Transform(r.way,4326),
      ST_SetSRID(ST_MakePoint(55.938627,-3.198706),4326))), 
   ST_Distance_Sphere(ST_ClosestPoint(ST_Transform(r.way,4326),
      ST_SetSRID(ST_MakePoint(55.938627,-3.198706),4326)),
      ST_SetSRID(ST_MakePoint(55.938627,-3.198706),4326))
FROM planet_osm_roads r 
ORDER BY 7 ASC
LIMIT 10;
  • 1
    55, -3 is the Seyshelles btw. Did you perhaps mean somewhere in Cumbria? This would make a difference :-) – John Powell Dec 17 '14 at 14:49
2

First of all, ST_Distance_Sphere returns in meters, so you are actually looking at 8km, which might be more reasonable. I suspect, also, that you have you lat/lon the wrong way round -- your point is somewhere off the coast of the Seychelles, not Carlisle, and there are not too many roads in the Indian Ocean.

Also, while the query planner will optimize this away, there is no need to repeatedly enter the same point. Instead, create it in a sub-query and reference it, eg:

SELECT 
 osm_id,
 highway,
 name,
 ref, 
 ST_X(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom),      
 ST_Y(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom),
 ST_Distance_Sphere(ST_ClosestPoint(ST_Transform(r.way, 4326), point.geom), point.geom)
FROM 
  planet_osm_roads r,
  (Select ST_SetSRID(ST_MakePoint(-3.198706, 55.938627), 4326) as geom) point
ORDER BY 7 ASC
LIMIT 10;

However, this is still very inefficient. To make better use of a indexes, look at using ST_DWithin (r.way, point, distance) ORDER BY ST_Distance(r.way, point). If you look at the docs for ST_DWithin you will get an idea how it is used.

  • Thanks for your answer, it was indeed that I had lat/long the wrong way around. I tried running the query you suggest though but I get a syntax error near FROM, any idea why that could be? Thanks. – Alistair Black Dec 18 '14 at 10:42
  • @AlistairBlack The last line before the FROM with ST_Distance_Sphere should have a the point.geom argument twice. I've edited the post, but it will be in a moderation queue – raphael Dec 27 '14 at 2:55

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.