Optimize fetching road data

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?

Accepted Answer

You're unnecessarily computing the distance twice, but that's the least of your problems. Your query cannot make use of the geometry index on planet_osm_line because you are applying a geometry transformation to the way column. Every time you make this query, every single highway in North America is converted to EPSG:4326 by PostGIS.

Instead, transform the reference point before you let PostGIS find nearby roads. Assuming your database is in EPSG:900913, that would be

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

This query returns near-instantly and gives you the same result. Note I have converted your 0.0009 degrees to approxmiately 100 Mercator units, YMMV.

Optimize fetching road data

Follow this question

Related questions