Optimize fetching road information from OSM

Question

I am using the following query to fetch road data from planet_osm_line

SELECT   maxspeed,   highway,   ref,   name,
 ST_Distance(
  ST_SetSRID(ST_Point(-98.224861,38.811898),4326), 
  ST_Transform(way, 4326)
 ) as distance   
FROM planet_osm_line   
WHERE highway is not null
AND ST_DWithin(
    ST_SetSRID(ST_Point(-98.224861, 38.811898),4326), 
    ST_Transform(way, 4326), 
    0.0009
)
ORDER BY ST_Distance(
    ST_SetSRID(ST_Point(-98.224861,38.811898),4326), 
    ST_Transform(way, 4326)
) LIMIT 1;

Since i am using north-america database imported with osm2pgsql its taking upto 12 seconds. is there a way where i can optimize this and bring the time close to 1s?

Answer 1

What you are doing is a nearest neighbour search. This can be done much more effective with the postgis nearest neighbour check.

But you are also losing a lot of speed with the transformation of your geometries to lat/lon values which is usually not needed (actually, try to avoid using lat/lon in your calculations when your data is not global). In the following query I am assuming that you query your data in the projection system of your local OSM install, you will have to look that up yourself:

SELECT   maxspeed,   highway,   ref,   name,
 ST_Distance(
  ST_Transform(
    'SRID=4326;POINT(-98.224861,38.811898)'::geometry,
     <your EPSG code>
  ),
  way
 ) as distance   --this is the distance in meters or feet
FROM planet_osm_line   
WHERE highway is not null
ORDER BY geom <-> ST_Transform(
  'SRID=4326;POINT(-98.224861,38.811898)'::geometry,
  <your EPSG code>
)
LIMIT 1;