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, 13:51

aitizazk's gravatar image

aitizazk
215
accept rate: 0%


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.

permanent link

answered 07 Aug, 18:01

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
59.7k73560934
accept rate: 23%

edited 07 Aug, 18:01

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

(08 Aug, 08:04) aitizazk
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×430
×141
×20
×8

question asked: 07 Aug, 13:51

question was seen: 88 times

last updated: 08 Aug, 08:04

powered by OSQA