[Openstreetmap-dev] Database select sql statement for getting nodes

[Mikel Maron]

Reason is, the 'nodes' tables contains the history of all revisions of
each node. So 'uid' is not unique in the table -- the present position
of a node is the most recent according to timestamp.

This sql got rather complicated, with a problem identified and fixed in
http://www.openstreetmap.org/trac/changeset/609. A node could have been
within the bounding box in a past revision, but the current revision is
outside that bounding box. This SELECT accounts for that case.

Perhaps a more straightforward solution is to add a column 'current',
identifying the most current revision of any node. That would vastly
simplify this SQL, and perhaps result in a significant speed up.

-Mikel

--- Immanuel Scholz <immanuel.scholz at gmx.de> wrote:

> Hi,
> 
> I just looked a bit on the ruby code to get a better understanding of
> what
> happens in the server. I extracted the actual sql statement to
> retrieve
> the nodes in a map? request. Now, it looks rather complicated to me:
> 
> select uid, latitude, longitude, visible, tags from (select * from
> (select
> nodes.uid, 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} and nodes.uid =
> a.uid
> order by nodes.timestamp desc) as b group by uid) as c where visible
> =
> true and latitude > #{lat2}  and latitude < #{lat1}  and longitude >
> #{lon1} and longitude < #{lon2}
> 
> 
> I rewrote this to some pseudo-code to get a better understanding:
> 
> 1.
> BAR = select nodes.uid, 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}
>       and nodes.uid = a.uid order by nodes.timestamp desc
> 
> 2.
> FOO = select * from BAR as b group by uid
> 
> 3.
> select uid, latitude, longitude, visible, tags from FOO as c
>   where visible = true
>     and latitude > #{lat2}
>     and latitude < #{lat1}
>     and longitude > #{lon1}
>     and longitude < #{lon2}
> 
> 
> - first the BAR-line: All nodes which have the same id as a node
> within
> the bounding box are read, ordered by timestamp. This seems funny to
> me,
> since the uid should be unique anyway, isn't it?
> 
> Why the funny "from nodes, nodes"? Why not just "select ... from
> nodes
> where ... order by ..."? So I assume, the uid is NOT unique (which is
> VERY! counterintuitive and should be changed if this is the case!)
> 
> 
> - second the FOO-line: From BAR (the list of all nodes in timestamp
> order), all nodes selected again, grouped by uid.
> If uid is unique, this does not have any effect. FOO equals to BAR.
> If uid in not unique, it only changes the order of appearance,
> putting all
> entries with the same uid together.
> 
> - in the last line, the "as c" is redundant, since unused. Despite
> from
> that, FOO is subselected to all visible nodes within the original
> bounding
> box, effectivly removing all additional nodes which came into the set
> in
> the first select (BAR-line) because of matching uid.
> 
> 
> So I come to the conclusion, that the whole statement should hold the
> very
> same result as:
> 
> 
> if uid is unique:
> 
> select uid, latitude, longitude, visible, tags from nodes
>   where visible = true
>     and latitude > #{lat2}
>     and latitude < #{lat1}
>     and longitude > #{lon1}
>     and longitude < #{lon2}
>   order by timestamp desc
> 
> 
> or if it is not (does mysql support more than one order column?):
> 
> select uid, latitude, longitude, visible, tags from nodes
>   where visible = true
>     and latitude > #{lat2}
>     and latitude < #{lat1}
>     and longitude > #{lon1}
>     and longitude < #{lon2}
>   order by uid, timestamp desc
> 
> 
> which is much more the one I excpected..
> 
> Can someone explain the reasoning behind the original select
> statement?
> 
> 
> Ciao, Imi.
> 
> 
> 
> _______________________________________________
> Openstreetmap-dev mailing list
> Openstreetmap-dev at vr.ucl.ac.uk
> http://bat.vr.ucl.ac.uk/cgi-bin/mailman/listinfo/openstreetmap-dev
>