source: subversion/applications/rendering/parking/joinways/joinways.py @ 28322

Revision 28322, 10.8 KB checked in by kdrangmeister, 2 years ago (diff)

cleaded up output log

  • Property svn:mime-type set to text/plain
Line 
1# -*- coding: utf-8 -*-
2# by kay
3
4import sys
5import string
6#import psycopg2
7from osmdb import OSMDB
8from geom import bbox
9from optparse import OptionParser
10
11class JoinDB (OSMDB):
12
13    def get_highways_segments(self):
14        #self.curs.execute("SELECT osm_id,"+latlon+",\"parking:condition:"+side+":maxstay\","+coords+",'"+side+"' "+FW+" \"parking:condition:"+side+":maxstay\" is not NULL and \"parking:condition:"+side+"\"='disc'")
15        result=[]
16        #self.curs.execute("select osm_id,name from planet_line where \"way\" && SetSRID('BOX3D(1101474.25471931 6406603.879863935,1114223.324055468 6415715.307134068)'::box3d, 900913)")
17        #print "result for bbox("+self.googbox+")"
18        #print "select osm_id,name "+self.FlW+" \"way\" && "+self.googbox+""
19        #self.curs.execute("select osm_id,name from planet_line where \"way\" && "+self.googbox+"")
20        self.curs.execute("select name,string_agg(text(osm_id),',') "+self.FlW+" highway is not Null and \"way\" && "+self.googbox+" and name is not Null group by name")
21        result += self.curs.fetchall()
22        highways=[]
23        for hw,osmids in result:
24            ids=osmids.split(',')
25            highways.append([hw,ids])
26
27    def _escape_quote(self,name):
28        return name.replace("'","''")
29
30    def find_same_named_highways(self,highway,bbox):
31        """ finds - within the small bbox - the highways with the same name. Returns dictionary with osm_id as key. """
32#        print "select osm_id,highway,name,ST_AsText(\"way\") AS geom {FlW} highway is not Null and \"way\" && '{bbox}'::BOX2D and name='{name}'".format(FlW=self.FlW,bbox=bbox,name=highway['name'])
33        self.curs.execute("select osm_id,highway,name,ST_AsText(\"way\") AS geom {FlW} highway is not Null and \"way\" && '{bbox}'::BOX2D and name='{name}'".format(FlW=self.FlW,bbox=bbox,name=self._escape_quote(highway['name'])))
34        rs = self.curs.fetchall()
35        highways = {}
36        for res in rs:
37            highway = {}
38            highway['osm_id']=res[0]
39            highway['highway']=res[1]
40            highway['name']=res[2]
41            highway['geom']=res[3]
42            highways[highway['osm_id']]=highway
43        return highways
44
45
46    def get_next_pending_highway(self,bboxobj=None):
47        """ Gets the next unhandled highway (osm_id+dict) """
48        if bboxobj!=None:
49            bbox_condition_sql = '"way" && {b} and '.format(b=bboxobj.get_bbox_sql())
50        else:
51            bbox_condition_sql = ''
52        select = "select osm_id,highway,name,ST_AsText(\"way\") AS geom {FlW} jrhandled is False and highway is not Null and {bbox} name is not Null limit 1".format(FlW=self.FlW,bbox=bbox_condition_sql)
53        #print "Get Next Pending Highway: sql={s}".format(s=select)
54        self.curs.execute(select)
55        result = self.curs.fetchall()
56        if len(result)==0:
57            return None
58            # raise BaseException("No pending highway found (this should not be an assert)")
59        res=result[0]
60        highway = {}
61        highway['osm_id']=res[0]
62        highway['highway']=res[1]
63        highway['name']=res[2]
64        highway['geom']=res[3]
65        return highway
66
67    def collate_highways(self,highway):
68        old_bbox=""
69        collated_highways={}
70        collated_highways[highway['osm_id']]=highway
71#        print "  collated_highways_0={ch}".format(ch=collated_highways)
72
73#        all_osm_ids_of_collated_highways=map(lambda osmid: str(osmid),collated_highways.keys())
74#        current_geom=self.get_joined_ways(all_osm_ids_of_collated_highways)
75#        current_bbox=self.get_expanded_bbox(current_geom,10.0)
76        current_bbox=self.get_expanded_bbox(highway['geom'],10.0)
77        #print "    current_bbox={bb}".format(bb=current_bbox)
78
79        i=0
80        while current_bbox != old_bbox:
81            old_bbox = current_bbox
82#            print "current_bbox={bb}".format(bb=current_bbox)
83            collated_highways.update(self.find_same_named_highways(highway,current_bbox))
84#            print "  collated_highways_{i}={ch}".format(i=i,ch=collated_highways)
85 
86            all_osm_ids_of_collated_highways=map(lambda osmid: str(osmid),collated_highways.keys())
87            the_joined_way=self.get_joined_ways(all_osm_ids_of_collated_highways)
88#            print "    current_bbox={bb}".format(bb=current_bbox)
89            current_bbox=self.get_expanded_bbox(the_joined_way,10.0)
90            i+=1
91
92        #print "-> Found {n} highway segments in {i} iterations. Joined way is {w}".format(n=len(collated_highways),i=i,w=the_joined_way)
93        return collated_highways,the_joined_way
94
95    def get_expanded_bbox(self,geom,meter):
96        """ returns a bbox expanded by meter """
97        result=[]
98        self.curs.execute("select st_expand(cast(st_extent('{geom}') as box2d),{meter})".format(geom=geom,meter=meter))
99        result += self.curs.fetchall()
100        return result[0][0]
101
102    def get_joined_ways(self,segment_ids):
103        result=[]
104        self.curs.execute("select st_linemerge(st_collect(way)) "+self.FlW+" osm_id in ("+string.join(segment_ids,',')+")")
105        result += self.curs.fetchall()
106#        print "jw-result = "+str(result)
107        return result[0][0]
108
109    def _insert_joined_highway(self,id,name,highway,way):
110        """ adds the joined highway (it may be a MULTILINE feature) to the jr tables. returns (just for info) the number of written ways (>1 if a MULTILINESTRING) """
111        #self.curs.execute("SELECT osm_id,"+latlon+",\"parking:condition:"+side+":maxstay\","+coords+",'"+side+"' "+FW+" \"parking:condition:"+side+":maxstay\" is not NULL and \"parking:condition:"+side+"\"='disc'")
112        #result=[]
113        #self.curs.execute("select osm_id,name from planet_line where \"way\" && SetSRID('BOX3D(1101474.25471931 6406603.879863935,1114223.324055468 6415715.307134068)'::box3d, 900913)")
114        #print "insert into planet_line_join (join_id, name, highway, way) values ('"+id+"','"+name+"','"+highway+"',SetSrid('"+way+"'::Text,4326));"
115        if self._which_geometry_is_it(way)=="LINESTRING":
116            #print "inserting a simple way"
117            self.curs.execute("insert into planet_line_join (join_id, name, highway, way) values ('"+id+"','"+self._escape_quote(name)+"','"+highway+"',SetSrid('"+way+"'::Text,4326))")
118            return 1
119        else:
120            #print "inserting a MULTILINE way"
121            ways = self._split_multiline_way(way)
122            for one_way in ways:
123                self.curs.execute("insert into planet_line_join (join_id, name, highway, way) values ('"+id+"','"+self._escape_quote(name)+"','"+highway+"',SetSrid('"+one_way+"'::Text,4326))")
124            return len(ways)
125
126
127    def _insert_segment_into_joinmap(self,join_id,segment_id):
128        """ adds a segment to the jr tables """
129        self.curs.execute("insert into planet_line_joinmap (join_id, segment_id) values ('{jid}','{sid}')".format(jid=join_id,sid=segment_id))
130
131    def _mark_segment_as_handled(self,segment_id):
132        """ Mark the given segment (by osm_id) as handled in the jr tables """
133        self.curs.execute("update planet_line set jrhandled=True where osm_id={oid}".format(oid=segment_id))
134
135
136    def _which_geometry_is_it(self,geom):
137        """ Returns the WKT type of the geom, e.g. LINESTRING or MULTILINESTRING """
138        self.curs.execute("select astext(setsrid('"+geom+"'::Text,4326))")
139        itisa = self.curs.fetchall()[0][0] # get first (and only) result
140        itisa = itisa.split('(')[0]
141        # print "whatisit-result = "+itisa
142        return itisa
143
144    def _split_multiline_way(self,multilineway):
145        """ split MULTILINESTRING multilineway into array of ways """
146        ways=[]
147        i=1
148        while True:
149            self.curs.execute("select ST_GeometryN(ST_SetSRID('{way}'::Text,4326),{i})".format(way=multilineway,i=i))
150            way = self.curs.fetchall()[0][0] # get first (and only) result
151            #print "way[{i}]={w}".format(i=i,w=way)
152            if way==None:
153                break
154            ways.append(way)
155            i += 1
156        return ways
157
158
159
160    def add_join_highway(self,highway,joinset,joinway):
161        """ Add the highway into the jr tables, handle all flagging """
162        join_id = highway['osm_id']
163        #print "*** Adding '{name}' ({id}) to planet_line_join".format(name=highway['name'],id=join_id)
164        numjoins = self._insert_joined_highway(str(join_id),highway['name'],highway['highway'],joinway)
165        #print "(joinset={j})".format(j=joinset)
166        for segment_id in joinset.keys():
167            #print "  * segment is {s}".format(s=joinset[segment_id])
168            self._insert_segment_into_joinmap(join_id,segment_id)
169            self._mark_segment_as_handled(segment_id)
170        return numjoins
171
172    def clear_planet_line_join(self,bboxobj=None):
173        print "*** clearing jr tables and flags"
174        self.curs.execute("delete from planet_line_join")
175        self.curs.execute("delete from planet_line_joinmap")
176        if bboxobj!=None:
177            bbox_condition_sql = '"way" && {b} and '.format(b=bboxobj.get_bbox_sql())
178        else:
179            bbox_condition_sql = ''
180        update = "update planet_line set jrhandled=False where {bbox} jrhandled is True".format(bbox=bbox_condition_sql)
181        self.curs.execute(update)
182
183
184"""
185'Kittelstra\xc3\x9fe', '36717484,36717485,5627159'
186
187create table planet_line_join (join_id integer , name text, highway text);
188select AddGeometryColumn('planet_line_join', 'way', 4326, 'LINESTRING', 2 );
189
190"""
191
192
193def main(options):
194    bboxstr = options['bbox']
195    DSN = options['dsn']
196    if bboxstr!='':
197        bboxobj = bbox({'bbox':bboxstr,'srs':'4326'})
198    else:
199        bboxobj = None
200    print bboxobj
201    print bboxobj.get_bbox_sql()
202
203    osmdb = JoinDB(DSN)
204
205    if options['command']=='clear':
206        osmdb.clear_planet_line_join()
207
208    i=0
209    while True:
210        # osmdb.set_bbox(bbox)
211        highway=osmdb.get_next_pending_highway(bboxobj)
212        if highway==None:
213            break
214        i+=1
215        #print "Found {i}. pending highway '{name}'".format(i=i,name=highway['name'])
216        joinset,joinway=osmdb.collate_highways(highway)
217        # print "  Found connected highways '{hws}'".format(hws=joinset)
218        numjoins = osmdb.add_join_highway(highway,joinset,joinway)
219        if i%100==0:
220            osmdb.commit()
221        print "Joined {i}. Highway '{name}': {segs} segments -> {numjoins} joined segments".format(i=i,name=highway['name'],segs=len(joinset),numjoins=numjoins)
222    osmdb.commit()
223    print "Terminated adding {i} highways".format(i=i)
224
225if __name__ == '__main__':
226    parser = OptionParser()
227    parser.add_option("-c", "--command", dest="command", help="The command to execute. Default is update. Possible values are update, install, clear", default="update")
228    parser.add_option("-b", "--bbox", dest="bbox", help="bounding box to restrict to", default="")
229    parser.add_option("-d", "--dsn", dest="dsn", help="DSN, default is 'dbname=gis host=crite'", default="dbname=gis host=crite")
230    (options, args) = parser.parse_args()
231    print options
232    main(options.__dict__)
233    sys.exit(0)
Note: See TracBrowser for help on using the repository browser.