[OSM-dev] Limitation on downloading ways via /map API Call

Christopher Schmidt crschmidt at crschmidt.net
Sun Jul 9 17:12:50 BST 2006


On Sun, Jul 09, 2006 at 04:46:48PM +0100, SteveC wrote:
> * @ 09/07/06 04:20:33 PM crschmidt at crschmidt.net wrote:
> >       ress = call_sql { "select
> > d.id,d.segs,d.tags,current_ways.timestamp,current_ways.visible from
> > (select c.id, segs, group_concat(k , concat('===', v) SEPARATOR '|||')
> > as tags from (select id, group_concat(segment_id order by sequence_id)
> > as segs from (select a.id, segment_id, sequence_id from (select id from
> > current_way_segments where segment_id in (#{id_list}) group by id) as a,
> > current_way_segments where a.id = current_way_segments.id) as b group by
> > id) as c, current_way_tags where c.id = current_way_tags.id group by id)
> > as d, current_ways where current_ways.id = d.id;" }

So, I was attempting to figure out:
 1. What this does
 2. How to make it better

In doing so, I simplified things out so I could understand them:

#d = select c.id, segs, group_concat(k, concat('==='),v) SEPERATOR '|||') as 
tags from c, current_way_tags where c.id = current_way_tags.id group by id;
#c = select id, group_concat(segment_id order by sequence_id) as segs
from b, current_way_segments where a.id = current_way_segments.id
#b = select a.id, segment_id, sequence_id from a, current_way_segments
where a.id = current_way_segments.id
#a = select id from current_way_segments where segment_id in (#{id_list}) group
by id

So, select the ways we want, then create all the ways in a single SQL
query.

This seems to me to be ignoring the fact that we have a get_multi
function which does exactly what this SQL does, for a single way: given
persistent database connections, there should be no significant slowdown
on getting data in multiple queries instead of one query.

However, the current code doesn't use persistent database connections,
which would explain why the code was done the way it was. 

This doesn't seem to me to be very hard to fix: all the SQL currently
uses the call_sql function, which uses get_connection, which currently is:
    
module OSM:
    def get_connection
      begin
        return Mysql.real_connect($DBSERVER, $USERNAME, $PASSWORD,
$DATABASE)
      rescue MysqlError => e
        mysql_error(e)
      end
    end

In Python, this would be:

class OSM():
  def get_connection(self):
    try:
      return MySQL.connect()
    except MySQLError, E:
      mysql_error(e)

When writing a get_connection function, to make it persist across the
instance, I would typically do:

class OSM():
  def get_connection(self):
    if self.dbh: return self.dbh
    try:
      self.dbh = MySQL.connect()
      return self.dbh
    except MySQLError, E:
      mysql_error(e)

However, I don't know enough ruby to translate this.

http://svn.openstreetmap.org/www.openstreetmap.org/ruby/api/osm/dao.rb
is the code as it stands. If persistent connections were to be enabled,
such that each new MySQL query didn't create a new database handle, then
the patch attached to this email would correct the current
segments-in-ways limitation. However, as it stands, creating a new
database handle for every query is slowing everything down to much to
the extent that this patch should not be applied.

-- 
Christopher Schmidt
Web Developer
-------------- next part --------------
Index: www.openstreetmap.org/ruby/api/osm/dao.rb
===================================================================
--- www.openstreetmap.org/ruby/api/osm/dao.rb	(revision 1152)
+++ www.openstreetmap.org/ruby/api/osm/dao.rb	(working copy)
@@ -1100,15 +1100,17 @@
       id_list = segment_ids.join(',')
 
 
-      ress = call_sql { "select d.id,d.segs,d.tags,current_ways.timestamp,current_ways.visible from (select c.id, segs, group_concat(k , concat('===', v) SEPARATOR '|||') as tags from (select id, group_concat(segment_id order by sequence_id) as segs from (select a.id, segment_id, sequence_id from (select id from current_way_segments where segment_id in (#{id_list}) group by id) as a, current_way_segments where a.id = current_way_segments.id) as b group by id) as c, current_way_tags where c.id = current_way_tags.id group by id) as d, current_ways where current_ways.id = d.id;" }
+    def get_multis_from_segments(segment_ids, type=:way)
 
+
+      id_list = segment_ids.join(',')
+
+      ress = call_sql { "select id from current_way_segments where segment_id in (#{id_list}) group by id" }
+
       multis = []
 
       ress.each_hash do |row|
-        tags = row['tags'].split('|||').collect {|x| x.split('===')}
-        segs = row['segs'].split(',').collect {|x| x}
-        visible = row['visible'] == '1'
-        multis << Street.new(row['id'].to_i, tags, segs, visible, row['timestamp'])
+        multis << get_multi(row['id'].to_i)
       end
 
       return multis


More information about the dev mailing list