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

Revision 28348, 12.8 KB checked in by kdrangmeister, 2 years ago (diff)

Improved logging

  • 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        #print "select osm_id,name "+self.FlW+" \"way\" && "+self.googbox+""
15        result=self.select("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")
16        highways=[]
17        for hw,osmids in result:
18            ids=osmids.split(',')
19            highways.append([hw,ids])
20
21    def _escape_quote(self,name):
22        return name.replace("'","''")
23
24    def find_same_named_highways(self,highway,bbox):
25        """ Finds - within the small bbox - the highways with the same name. Returns dictionary with osm_id as key. """
26        # 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'])
27        rs=self.select("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'])))
28        highways = {}
29        for res in rs:
30            highway = {}
31            highway['osm_id']=res[0]
32            highway['highway']=res[1]
33            highway['name']=res[2]
34            highway['geom']=res[3]
35            highways[highway['osm_id']]=highway
36        return highways
37
38
39    def get_next_deleted_highway(self):
40        """ Gets the next deleted highway (osm_id) """
41        select = "select osm_id from planet_line_join_deleted_segments limit 1"
42        highway_osm_id=self.select_one(select)
43        return highway_osm_id
44
45    def get_next_pending_highway(self,bboxobj=None):
46        """ Gets the next unhandled highway (osm_id+dict) """
47        if bboxobj!=None:
48            bbox_condition_sql = '"way" && {b} and '.format(b=bboxobj.get_bbox_sql())
49        else:
50            bbox_condition_sql = ''
51        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)
52        #print "Get Next Pending Highway: sql={s}".format(s=select)
53        result=self.select(select)
54        if len(result)==0:
55            return None
56            # raise BaseException("No pending highway found (this should not be an assert)")
57        res=result[0]
58        highway = {}
59        highway['osm_id']=res[0]
60        highway['highway']=res[1]
61        highway['name']=res[2]
62        highway['geom']=res[3]
63        return highway
64
65    def collate_highways(self,highway):
66        """ check and collect iteratively if a same-named highway is in an expanded bbox around the current highway """
67        old_bbox=""
68        collated_highways={}
69        collated_highways[highway['osm_id']]=highway
70        current_bbox=self.get_expanded_bbox(highway['geom'],10.0)
71
72        i=0
73        while current_bbox != old_bbox:
74            old_bbox = current_bbox
75            collated_highways.update(self.find_same_named_highways(highway,current_bbox))
76
77            the_joined_way=self.get_joined_ways(collated_highways.keys())
78            current_bbox=self.get_expanded_bbox(the_joined_way,10.0)
79            i+=1
80
81        #print "-> Found {n} highway segments in {i} iterations. Joined way is {w}".format(n=len(collated_highways),i=i,w=the_joined_way)
82        return collated_highways,the_joined_way
83
84    def get_expanded_bbox(self,geom,meter):
85        """ returns a bbox expanded by meter """
86        return self.select_one("select ST_Expand(cast(ST_Extent('{geom}') as BOX2D),{meter})".format(geom=geom,meter=meter))
87
88    def get_joined_ways(self,segment_ids):
89        """ Get a joined way (likely a LINESTRING, but possibly a MULTILINESTRING) for the set of osm_ids (int) given """
90        segment_ids_as_strings=map(lambda osmid: str(osmid),segment_ids)
91        return self.select_one("select ST_Linemerge(ST_Collect(way)) {FlW} osm_id in ({seglist})".format(FlW=self.FlW,seglist=string.join(segment_ids_as_strings,',')))
92
93    def _insert_joined_highway(self,id,name,highway,way):
94        """ 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) """
95        if self._which_geometry_is_it(way)=="LINESTRING":
96            #print "inserting a simple way"
97            self.insert("insert into planet_line_join (join_id, name, highway, way) values ('"+id+"','"+self._escape_quote(name)+"','"+highway+"',SetSrid('"+way+"'::Text,4326))")
98            return 1
99        else:
100            #print "inserting a MULTILINE way"
101            ways = self._split_multiline_way(way)
102            for one_way in ways:
103                self.insert("insert into planet_line_join (join_id, name, highway, way) values ('"+id+"','"+self._escape_quote(name)+"','"+highway+"',SetSrid('"+one_way+"'::Text,4326))")
104            return len(ways)
105
106
107    def _insert_segment_into_joinmap(self,join_id,segment_id):
108        """ adds a segment to the jr tables """
109        self.insert("insert into planet_line_joinmap (join_id, segment_id) values ('{jid}','{sid}')".format(jid=join_id,sid=segment_id))
110
111    def _mark_segment_as_handled(self,segment_id):
112        """ Mark the given segment (by osm_id) as handled in the jr tables """
113        self.update("update planet_line set jrhandled=True where osm_id={oid}".format(oid=segment_id))
114
115    def _which_geometry_is_it(self,geom):
116        """ Returns the WKT type of the geom, e.g. LINESTRING or MULTILINESTRING """
117        itisa = self.select_one("select ST_AsText(ST_SetSRID('"+geom+"'::Text,4326))")
118        itisa = itisa.split('(')[0]
119        # print "whatisit-result = "+itisa
120        return itisa
121
122    def _split_multiline_way(self,multilineway):
123        """ split MULTILINESTRING multilineway into array of ways """
124        ways=[]
125        i=1
126        while True:
127            way = self.select_one("select ST_GeometryN(ST_SetSRID('{way}'::Text,4326),{i})".format(way=multilineway,i=i))
128            #print "way[{i}]={w}".format(i=i,w=way)
129            if way==None:
130                break
131            ways.append(way)
132            i += 1
133        return ways
134
135    def add_join_highway(self,highway,joinset,joinway):
136        """ Add the highway into the jr tables, handle all flagging """
137        join_id = highway['osm_id']
138        #print "*** Adding '{name}' ({id}) to planet_line_join".format(name=highway['name'],id=join_id)
139        numjoins = self._insert_joined_highway(str(join_id),highway['name'],highway['highway'],joinway)
140        #print "(joinset={j})".format(j=joinset)
141        for segment_id in joinset.keys():
142            #print "  * segment is {s}".format(s=joinset[segment_id])
143            self._insert_segment_into_joinmap(join_id,segment_id)
144            self._mark_segment_as_handled(segment_id)
145        return numjoins
146
147    def clear_planet_line_join(self,bboxobj=None):
148        print "*** clearing jr tables and flags"
149        self.delete("delete from planet_line_join")
150        self.delete("delete from planet_line_joinmap")
151        if bboxobj!=None:
152            bbox_condition_sql = '"way" && {b} and '.format(b=bboxobj.get_bbox_sql())
153        else:
154            bbox_condition_sql = ''
155        update = "update planet_line set jrhandled=False where {bbox} jrhandled is True".format(bbox=bbox_condition_sql)
156        self.update(update)
157
158    def find_joinway_by_segment(self,segment_id):
159        """ Find the join_id of a highway segment. None if none found """
160        select="select join_id from planet_line_joinmap where segment_id={seg}".format(seg=segment_id)
161        return self.select_one(select)
162
163    def flush_deleted_segment(self,segment_id):
164        """ Removes a "deleted highway" from the deleted_segments table. """
165        delete="delete from planet_line_join_deleted_segments where osm_id={seg}".format(seg=segment_id)
166        self.delete(delete)
167
168    def flush_deleted_segments(self,segment_list):
169        """ Removes a list of "deleted highways" from the deleted_segments table. """
170        segmentlistsql=self.sql_list_of_ids(segment_list)
171        delete="delete from planet_line_join_deleted_segments where osm_id in {seg}".format(seg=segmentlistsql)
172        self.delete(delete)
173
174    def get_segments_of_joinway(self,joinway_id):
175        select="select segment_id from planet_line_joinmap where join_id={jid}".format(jid=joinway_id)
176        segments=self.select_list(select)
177        return segments
178
179    def get_name_of_joinway(self,joinway_id):
180        select="select name from planet_line_join where join_id={jid}".format(jid=joinway_id)
181        return self.select_one(select)
182
183    def mark_segments_unhandled(self,dirtylist):
184        dirtylistsql=self.sql_list_of_ids(dirtylist)
185        update="update planet_line set jrhandled=False where osm_id in {l}".format(l=dirtylistsql)
186        self.update(update)
187
188    def remove_joinway(self,joinway_id):
189        delete="delete from planet_line_join where join_id={j}".format(j=joinway_id)
190        self.delete(delete)
191        delete="delete from planet_line_joinmap where join_id={j}".format(j=joinway_id)
192        self.delete(delete)
193
194
195"""
196'Kittelstra\xc3\x9fe', '36717484,36717485,5627159'
197
198create table planet_line_join (join_id integer , name text, highway text);
199select AddGeometryColumn('planet_line_join', 'way', 4326, 'LINESTRING', 2 );
200
201"""
202
203
204def main(options):
205    bboxstr = options['bbox']
206    DSN = options['dsn']
207    if bboxstr!='':
208        bboxobj = bbox({'bbox':bboxstr,'srs':'4326'})
209    else:
210        bboxobj = None
211    print bboxobj
212    print bboxobj.get_bbox_sql()
213
214    osmdb = JoinDB(DSN)
215
216    if options['command']=='clear':
217        osmdb.clear_planet_line_join()
218
219    #
220    # handle deleted highway segments -> mark all joined highway's segments as unhandled in order to have them re-handled
221    #
222    i=0
223    j=0
224    while True:
225        segment_id=osmdb.get_next_deleted_highway()
226        if segment_id==None:
227            break
228        #print "[] Handling deleted segment {seg}".format(seg=segment_id)
229        joinway_id=osmdb.find_joinway_by_segment(segment_id)
230        if joinway_id==None:  # deleted segment was not in joined highway -> ignore (FIXME: and warn)
231            #print "   [] was not a joinway. Ignoring and flushing."
232            osmdb.flush_deleted_segment(segment_id)
233            ### FIXME: zur sicherheit in planet_line als dirty markieren (falls vorhanden), oder besser: ASSERT ERROR falls in planet_line vorhanden und jrhandled=True
234            continue
235        dirtylist=osmdb.get_segments_of_joinway(joinway_id)
236        name_of_joinway=osmdb.get_name_of_joinway(joinway_id)
237        #print "   [] '{jwname}': list of segments to mark: {l}".format(jwname=name_of_joinway,l=dirtylist)
238        # dirty segments must be removed: * from the deleted_segments table, * from the joinmap, * from the join table
239        # all of those must fail gracefully if an entry is not there (anymore).
240        osmdb.mark_segments_unhandled(dirtylist)
241        osmdb.flush_deleted_segments(dirtylist)
242        osmdb.remove_joinway(joinway_id)
243        i+=1
244        j+=len(dirtylist)
245        print "Deleted {i}. ({id}) '{jwname}' ({l} segments).".format(i=i,id=segment_id,jwname=name_of_joinway,l=dirtylist)
246        if i%100==0:
247            osmdb.commit()
248    osmdb.commit()
249    print "Found {i} deleted segments and marked {j} highways as dirty".format(i=i,j=j)
250
251    #
252    # handle unhandled (or dirty) highway segments
253    #
254    ### FIXME: was passiert, wenn zu einem Segment eins angehängt wird, vorher müssten die alten segmente rausgelöscht werden.
255    i=0
256    while True:
257        # osmdb.set_bbox(bbox)
258        highway=osmdb.get_next_pending_highway(bboxobj)
259        if highway==None:
260            break
261        i+=1
262        #print "Found {i}. pending highway '{name}'".format(i=i,name=highway['name'])
263        joinset,joinway=osmdb.collate_highways(highway)
264        # print "  Found connected highways '{hws}'".format(hws=joinset)
265        numjoins = osmdb.add_join_highway(highway,joinset,joinway)
266        if i%100==0:
267            osmdb.commit()
268        print "Joined {i}. ({id}) '{name}': {segs} segments -> {numjoins} joined segments".format(i=i,id=highway['osm_id'],name=highway['name'],segs=len(joinset),numjoins=numjoins)
269    osmdb.commit()
270    print "Terminated adding {i} highways".format(i=i)
271
272if __name__ == '__main__':
273    parser = OptionParser()
274    parser.add_option("-c", "--command", dest="command", help="The command to execute. Default is update. Possible values are update, install, clear", default="update")
275    parser.add_option("-b", "--bbox", dest="bbox", help="bounding box to restrict to", default="")
276    parser.add_option("-d", "--dsn", dest="dsn", help="DSN, default is 'dbname=gis host=crite'", default="dbname=gis host=crite")
277    (options, args) = parser.parse_args()
278    print options
279    main(options.__dict__)
280    sys.exit(0)
Note: See TracBrowser for help on using the repository browser.