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

Immanuel Scholz immanuel.scholz at gmx.de
Fri Dec 2 16:01:51 GMT 2005


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

Yes, that would be best. Set to "maybe NULL" and only to some value if the
row is the current should result in best speed and memory usage.

In the meantime, the following statement should result in the same values
while remarkable faster to calculate (and perhaps easier to read):

select uid, latitude, longitude, visible, tags from nodes
  where latitude > #{lat2}
    and latitude < #{lat1}
    and longitude > #{lon1}
    and longitude < #{lon2}
    and visible = 1
    and timestamp in (select max(timestamp) from nodes group by uid);

Am I right?

Ciao, Imi.

More information about the dev mailing list