This is a static archive of our old OSM Help Site. Please post any new questions and answers at community.openstreetmap.org.

Optimize fetching road data

0
1

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?

asked 07 Aug '17, 13:51

aitizazk's gravatar image

aitizazk
21●7●7●10
accept rate: 0%

One Answer:

4

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.

answered 07 Aug '17, 18:01

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

edited 07 Aug '17, 18:01

Woow thanks alot it drastically improved the performance and now results are returned in less than a second.. Awesome

(08 Aug '17, 08:04) aitizazk