[OSM-dev] getnodes query optimization

[Immanuel Scholz]

Hi,

> 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.)

So I suggested a while ago too ;-). But the stuff is there for a reason.
In former times, the statement read "uid" instead of every "id" which made
things even worse to understand.


> It looks like this was legacy code from some other point in time when
> things were done differently: can anyone check this out and see if this
> needs to be this way? Perhaps this is just a problem with my small
> dataset or something else I'm missing?

You missing history. The "id" is not unique, and you probably don't want
to select all items in former times that where in your bounding box once.
You want to select only from the current items.

So the inner select just select the current version of every item in the
table.


Yes, your very next idea is correct: We should have a seperate database
table (or even a seperate database) that contain only the latest data.

This would involve some more complicated issues making transactions and
locking necessary (or database replication), since the change of one data
item is no longer a single "insert into". All is on the wish list already
;-)


If you developing an application which read from a planet.osm -
convererted database (read: A database without history), than you don't
need the fancy inner select thingie.

Ciao, Imi