[OSM-dev] Temporary tables for the map query?

[Alex Wilson]

I've been looking at the rails_port implementation of the map query. As I
understand it, it seems to be retrieving all nodes within the bounding box
into ruby, then sending this list back out to the db to retrieve all ways,
then sending all the way ids out for finally retrieving the extra nodes and
associated relations. So there's quite a lot of (rather inefficient) data
movement between the ruby code and the database.

I would imagine that it would be more efficient to do this all on the
database using temporary tables: rather than try to explain what I mean,
below is an example of something that works. The last three lines of which
return all the data - so you only bring back only the stuff that's needed,
only send through the bbox coords - and that resultant data can be streamed
easily and doesn't need to be in memory at once (apart from in the db -
which is designed to handle this kind of thing). Is there a particular
reason that this hasn't been done before -  am I missing something obvious?

Alex

--- Example SQL follows

create temporary table temp_way_ids( id int, primary key(id) );
create temporary table temp_node_ids( id int, primary key(id) );

insert into temp_way_ids select distinct way_nodes.id from way_nodes inner
join nodes on way_nodes.node_id=nodes.id where nodes.latitude > 515000000
and nodes.latitude < 520000000 and nodes.longitude > -10000000 and
nodes.longitude < -8000000;

insert into temp_node_ids select distinct way_nodes.node_id from way_nodes
inner join temp_way_ids on way_nodes.id=temp_way_ids.id;

select nodes.* from nodes inner join temp_node_ids on nodes.id=
temp_node_ids.id;
select ways.* from ways inner join temp_way_ids on ways.id=temp_way_ids.id;
select way_nodes.* from way_nodes inner join temp_way_ids on ways_node.id=
temp_way_ids.id;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20080524/3caa194e/attachment.html>