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.<br>
<br>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?<br>
<br>Alex<br><br>--- Example SQL follows<br><br>create temporary table temp_way_ids( id int, primary key(id) );<br>create temporary table temp_node_ids( id int, primary key(id) );<br><br>insert into temp_way_ids select distinct <a href="http://way_nodes.id">way_nodes.id</a> from way_nodes inner join nodes on way_nodes.node_id=<a href="http://nodes.id">nodes.id</a> where nodes.latitude > 515000000 and nodes.latitude < 520000000 and nodes.longitude > -10000000 and nodes.longitude < -8000000;<br>
<br>insert into temp_node_ids select distinct way_nodes.node_id from way_nodes inner join temp_way_ids on <a href="http://way_nodes.id">way_nodes.id</a>=<a href="http://temp_way_ids.id">temp_way_ids.id</a>;<br><br>select nodes.* from nodes inner join temp_node_ids on <a href="http://nodes.id">nodes.id</a>=<a href="http://temp_node_ids.id">temp_node_ids.id</a>;<br>
select ways.* from ways inner join temp_way_ids on <a href="http://ways.id">ways.id</a>=<a href="http://temp_way_ids.id">temp_way_ids.id</a>;<br>select way_nodes.* from way_nodes inner join temp_way_ids on <a href="http://ways_node.id">ways_node.id</a>=<a href="http://temp_way_ids.id">temp_way_ids.id</a>;<br>
<br>