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?

up vote 0 down vote accepted

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;

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.