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

Revision 28337, 11.0 KB checked in by kdrangmeister, 2 years ago (diff)

started handling of deleted segments

  • 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 get_segments_of_joinway(self,joinway_id):
169        select="select segment_id from planet_line_joinmap where join_id={jid};".format(jid=joinway_id)
170        segments=self.select_list(select)
171        return segments
172
173
174       
175
176
177"""
178'Kittelstra\xc3\x9fe', '36717484,36717485,5627159'
179
180create table planet_line_join (join_id integer , name text, highway text);
181select AddGeometryColumn('planet_line_join', 'way', 4326, 'LINESTRING', 2 );
182
183"""
184
185
186def main(options):
187    bboxstr = options['bbox']
188    DSN = options['dsn']
189    if bboxstr!='':
190        bboxobj = bbox({'bbox':bboxstr,'srs':'4326'})
191    else:
192        bboxobj = None
193    print bboxobj
194    print bboxobj.get_bbox_sql()
195
196    osmdb = JoinDB(DSN)
197
198    if options['command']=='clear':
199        osmdb.clear_planet_line_join()
200
201    #
202    # handle deleted highway segments -> mark all joined highway's segments as unhandled in order to have them re-handled
203    #
204    i=0
205    j=0
206    while True:
207        segment_id=osmdb.get_next_deleted_highway()
208        if segment_id==None:
209            break
210        print "[] Handling deleted segment {seg}".format(seg=segment_id)
211        joinway_id=osmdb.find_joinway_by_segment(segment_id)
212        if joinway_id==None:  # deleted segment was not in joined highway -> ignore (FIXME: and warn)
213            print "   [] was not a joinway. Ignoring and flushing."
214            osmdb.flush_deleted_segment(segment_id)
215            continue
216        dirtylist=osmdb.get_segments_of_joinway(joinway_id)
217        print "   [] list of segments to mark: {l}".format(l=dirtylist)
218        break
219    osmdb.commit()
220    print "Found {i} deleted segments and marked {j} highways as dirty".format(i=i,j=j)
221
222    #
223    # handle unhandled (or dirty) highway segments
224    #
225    ### FIXME: was passiert, wenn zu einem Segment eins angehängt wird, vorher müssten die alten segmente rausgelöscht werden.
226    i=0
227    while True:
228        # osmdb.set_bbox(bbox)
229        highway=osmdb.get_next_pending_highway(bboxobj)
230        if highway==None:
231            break
232        i+=1
233        #print "Found {i}. pending highway '{name}'".format(i=i,name=highway['name'])
234        joinset,joinway=osmdb.collate_highways(highway)
235        # print "  Found connected highways '{hws}'".format(hws=joinset)
236        numjoins = osmdb.add_join_highway(highway,joinset,joinway)
237        if i%100==0:
238            osmdb.commit()
239        print "Joined {i}. Highway '{name}': {segs} segments -> {numjoins} joined segments".format(i=i,name=highway['name'],segs=len(joinset),numjoins=numjoins)
240    osmdb.commit()
241    print "Terminated adding {i} highways".format(i=i)
242
243if __name__ == '__main__':
244    parser = OptionParser()
245    parser.add_option("-c", "--command", dest="command", help="The command to execute. Default is update. Possible values are update, install, clear", default="update")
246    parser.add_option("-b", "--bbox", dest="bbox", help="bounding box to restrict to", default="")
247    parser.add_option("-d", "--dsn", dest="dsn", help="DSN, default is 'dbname=gis host=crite'", default="dbname=gis host=crite")
248    (options, args) = parser.parse_args()
249    print options
250    main(options.__dict__)
251    sys.exit(0)
Note: See TracBrowser for help on using the repository browser.