1 | # -*- coding: utf-8 -*- |
---|
2 | # by kay |
---|
3 | |
---|
4 | import sys |
---|
5 | import string |
---|
6 | #import psycopg2 |
---|
7 | from osmdb import OSMDB |
---|
8 | from geom import bbox |
---|
9 | from optparse import OptionParser |
---|
10 | |
---|
11 | class 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 | |
---|
198 | create table planet_line_join (join_id integer , name text, highway text); |
---|
199 | select AddGeometryColumn('planet_line_join', 'way', 4326, 'LINESTRING', 2 ); |
---|
200 | |
---|
201 | """ |
---|
202 | |
---|
203 | |
---|
204 | def 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 | |
---|
272 | if __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) |
---|