[OSM-dev] getnodes query optimization

SteveC steve at asklater.com
Fri Jun 2 15:44:49 BST 2006


* @ 02/06/06 03:37:30 PM crschmidt at crschmidt.net wrote:
> http://trac.openstreetmap.org/browser/www.openstreetmap.org/ruby/api/osm/dao.rb#L587
> says 
> 
> res = call_sql { "select id, latitude, longitude, visible, tags
> from (select * from (select nodes.id, nodes.latitude, nodes.longitude,
> nodes.visible, nodes.tags from nodes, nodes as a where a.latitude >
> #{lat2}  and a.latitude < #{lat1}  and a.longitude > #{lon1} and
> a.longitude < #{lon2} #{timeclause} and nodes.id = a.id order by
> nodes.timestamp desc) as b group by id) as c where visible = true and
> latitude > #{lat2}  and latitude < #{lat1}  and longitude > #{lon1} and
> longitude < #{lon2}" }
> 
> This seems to have several unneccesary subselects. I'm not sure for the
> exact reason for this, but I loaded data into a local OSM instance, and
> ran the query, and got the same results as I would have with:
> 
> select nodes.id, nodes.latitude, nodes.longitude,
> nodes.visible, nodes.tags from nodes, nodes as a where a.latitude >
> #{lat2}  and a.latitude < #{lat1}  and a.longitude > #{lon1} and
> a.longitude < #{lon2} #{timeclause}
> 
> (The 'order by nodes.timestamp' probably makes a difference, but most
> apps should hopefully not depend on the order of nodes.)

hi

* there are multiple versions of a node in the table

* you need to get the latest version of the nodes, hence the order by

* once you have the latest version you need to check they're still in
the bounding box, hence the sub-select

... from memory. And this, to gloss over many issues, is why OSM isn't
built on your fathers GIS tools.

Why are you looking at that code?

have fun,

SteveC steve at asklater.com http://www.asklater.com/steve/




More information about the dev mailing list