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

Immanuel Scholz immanuel.scholz at gmx.de
Wed Nov 30 10:42:18 GMT 2005


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.






More information about the dev mailing list