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

Revision 28236, 12.4 KB checked in by kdrangmeister, 2 years ago (diff)

joinways initial - joinways

  • Property svn:mime-type set to text/plain
Line 
1# -*- coding: utf-8 -*-
2# by kay
3
4import sys
5import psycopg2
6from optparse import OptionParser
7
8class OsrayDB:
9
10    bbox = None
11
12    def get_bounds(self):
13        polygonstring = self.bbox[0][0]
14        polygonstring = polygonstring[9:] # cut off the "POLYGON(("
15        polygonstring = polygonstring[:-2] # cut off the "))"
16        points = polygonstring.split(',')
17
18        numpoints = len(points)
19        for i,point in enumerate(points):
20            latlon = point.split(' ')
21            if (i==0):
22                self.left=float(latlon[0])
23                self.bottom=float(latlon[1])
24            if (i==2):
25                self.right=float(latlon[0])
26                self.top=float(latlon[1])
27                   
28        print "Bounds [b l t r] = ",self.bottom,self.left,self.top,self.right
29
30    def __init__(self,dsn):
31        DSN = dsn
32        #thebbox = options['bbox']
33        prefix = "planet_"
34
35        print "Opening connection using dsn:", DSN
36        self.conn = psycopg2.connect(DSN)
37        print "Encoding for this connection is", self.conn.encoding
38        self.curs = self.conn.cursor()
39
40        """
41        SELECT ST_AsText(transform("way",4326)) AS geom
42        FROM planet_osm_line
43        WHERE "way" && transform(SetSRID('BOX3D(9.92498 49.78816,9.93955 49.8002)'::box3d,4326),900913)
44        LIMIT 10;
45   
46        SELECT highway,ST_AsText(transform("way",4326)) AS geom
47        FROM planet_osm_line
48        WHERE "way" && transform(SetSRID('BOX3D(9.92498 49.78816,9.93955 49.8002)'::box3d,4326),900913)
49        and highway='secondary' LIMIT 50;
50        """
51
52        self.latlon= 'ST_Y(ST_Transform(ST_line_interpolate_point(way,0.5),4326)),ST_X(ST_Transform(ST_line_interpolate_point(way,0.5),4326))'
53        self.coords= "ST_Y(ST_line_interpolate_point(way,0.5)) as py,ST_X(ST_line_interpolate_point(way,0.5)) as px,ST_Y(ST_line_interpolate_point(way,0.49)) as qy,ST_X(ST_line_interpolate_point(way,0.49)) as qx,ST_Y(ST_line_interpolate_point(way,0.51)) as ry,ST_X(ST_line_interpolate_point(way,0.51)) as rx"
54        self.FlW = "FROM "+prefix+"_line WHERE"
55        self.FpW = "FROM "+prefix+"_polygon WHERE"
56        self.FnW = "FROM "+prefix+"_point WHERE"
57
58    def set_bbox(self,bbox):
59        srs = '4326' #options.get('srs','4326')
60        if srs=='4326':
61            self.init_bbox_4326(bbox)
62        elif srs=='3857':
63            self.init_bbox_3857(bbox)
64        elif srs=='900913':
65            self.init_bbox_3857(bbox)
66
67    def __del__(self):
68        print "Closing connection"
69        self.conn.rollback()
70        self.conn.close()
71
72    def init_bbox_srs(self,bbox,srs):
73        self.googbox = "transform(SetSRID('BOX3D("+bbox+")'::box3d,"+srs+"),900913)"
74        self.curs.execute("SELECT ST_AsText("+self.googbox+") AS geom")
75        self.bbox = self.curs.fetchall()
76        self.get_bounds()
77
78    def init_bbox_4326(self,bbox):
79        self.init_bbox_srs(bbox, '4326')
80
81    def init_bbox_3857(self,bbox): # 900913 Projection
82        self.init_bbox_srs(bbox, '3857')
83        #self.googbox = "'BOX3D("+bbox+")'::box3d"
84        #self.curs.execute("SELECT ST_AsText("+self.googbox+") AS geom")
85        #self.bbox = self.curs.fetchall()
86        #self.get_bounds()
87
88    def select_highways(self):
89        self.curs.execute("SELECT osm_id,highway,ST_AsText(\"way\") AS geom, tags->'lanes' as lanes, tags->'layer' as layer, tags->'oneway' as oneway, tags->'lanes:forward' as lanesfw, tags->'lanes:forward' as lanesbw "+self.FlW+" \"way\" && "+self.googbox+" and highway is not NULL "+LIMIT+";")
90        rs = self.curs.fetchall()
91        highways = []
92        for res in rs:
93            highway = {}
94            highway['osm_id']=res[0]
95            highway['highway']=res[1]
96            highway['coords']=WKT_to_line(res[2])
97            highway['lanes']=res[3]
98            highway['layer']=res[4]
99            highway['oneway']=res[5]
100            highway['lanesfw']=res[6]
101            highway['lanesbw']=res[7]
102            highways.append(highway)
103        return highways
104
105    def select_highway_areas(self):
106        self.curs.execute("SELECT osm_id,highway,ST_AsText(\"way\") AS geom, tags->'height' as height, amenity, ST_AsText(buffer(\"way\",1)) AS geombuffer  "+self.FpW+" \"way\" && "+self.googbox+" and highway is not NULL "+LIMIT+";")
107        rs = self.curs.fetchall()
108        areas = []
109        for res in rs:
110            area = {}
111            area['osm_id']=res[0]
112            area['highway']=res[1]
113            area['coords']=WKT_to_polygon(res[2])
114            area['height']=res[3]
115            area['amenity']=res[4]
116            area['buffercoords']=WKT_to_polygon(res[5])
117            areas.append(area)
118        return areas
119
120    def select_amenity_areas(self):
121        self.curs.execute("SELECT osm_id,amenity,ST_AsText(\"way\") AS geom, ST_AsText(buffer(\"way\",1)) AS geombuffer, tags->'height' as height  "+self.FpW+" \"way\" && "+self.googbox+" and amenity is not NULL and (building is NULL or building='no')"+LIMIT+";")
122        rs = self.curs.fetchall()
123        areas = []
124        for res in rs:
125            area = {}
126            area['osm_id']=res[0]
127            area['amenity']=res[1]
128            area['coords']=WKT_to_polygon(res[2])
129            area['buffercoords']=WKT_to_polygon(res[3])
130            area['height']=res[4]
131            areas.append(area)
132        return areas
133
134    def select_buildings(self,buildingtype):
135        self.curs.execute("SELECT osm_id,ST_AsText(\"way\") AS geom, building, tags->'height' as height,tags->'building:height' as bheight,amenity,shop "+self.FpW+" \"way\" && "+self.googbox+" and building='"+buildingtype+"' "+LIMIT+";")
136        rs = self.curs.fetchall()
137        buildings = []
138        for res in rs:
139            building = {}
140            building['osm_id']=res[0]
141            building['coords']=WKT_to_polygon(res[1])
142            building['building']=res[2]
143            building['height']=res[3]
144            building['bheight']=res[4]
145            building['amenity']=res[5]
146            building['shop']=res[6]
147            buildings.append(building)
148        return buildings
149
150    def select_landuse(self,landusetype):
151        #print "SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";"
152        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";")
153        rs = self.curs.fetchall()
154        landuses = []
155        for res in rs:
156            landuse = {}
157            landuse['osm_id']=res[0]
158            landuse['landuse']=res[1]
159            landuse['coords']=WKT_to_polygon(res[2])
160            landuses.append(landuse)
161        return landuses
162
163    def select_landuse_areas(self):
164        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse is not NULL "+LIMIT+";")
165        rs = self.curs.fetchall()
166        landuses = []
167        for res in rs:
168            landuse = {}
169            landuse['osm_id']=res[0]
170            landuse['landuse']=res[1]
171            landuse['coords']=WKT_to_polygon(res[2])
172            landuses.append(landuse)
173        return landuses
174
175    def select_leisure_areas(self):
176        self.curs.execute("SELECT osm_id,leisure,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and leisure is not NULL "+LIMIT+";")
177        rs = self.curs.fetchall()
178        leisures = []
179        for res in rs:
180            leisure = {}
181            leisure['osm_id']=res[0]
182            leisure['leisure']=res[1]
183            leisure['coords']=WKT_to_polygon(res[2])
184            leisures.append(leisure)
185        return leisures
186
187    def select_waterway(self,waterwaytype):
188        self.curs.execute("SELECT osm_id,waterway,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and waterway='"+waterwaytype+"' "+LIMIT+";")
189        rs = self.curs.fetchall()
190        waterways = []
191        for res in rs:
192            waterway = {}
193            waterway['osm_id']=res[0]
194            waterway['waterway']=res[1]
195            waterway['coords']=WKT_to_polygon(res[2])
196            waterways.append(waterway)
197        return waterways
198
199    def select_naturalwater(self):
200        naturaltype='water'
201        self.curs.execute("SELECT osm_id,tags->'natural' as natural,ST_AsText(\"way\") AS geom, tags->'type' as type, layer "+self.FpW+" \"way\" && "+self.googbox+" and tags->'natural'='"+naturaltype+"' "+LIMIT+";")
202        rs = self.curs.fetchall()
203        waters = []
204        for res in rs:
205            water = {}
206            water['osm_id']=res[0]
207            water['natural']=res[1]
208            water['coords']=WKT_to_polygon(res[2])
209            water['type']=res[3]
210            water['layer']=res[4]
211            waters.append(water)
212        return waters
213
214    def select_trees(self):
215        naturaltype='tree'
216        self.curs.execute("SELECT osm_id,tags->'natural' as natural,ST_AsText(\"way\") AS geom, tags->'type' as type, tags->'height' as height "+self.FnW+" \"way\" && "+self.googbox+" and tags->'natural'='"+naturaltype+"' "+LIMIT+";")
217        rs = self.curs.fetchall()
218        trees = []
219        for res in rs:
220            tree = {}
221            tree['osm_id']=res[0]
222            tree['natural']=res[1]
223            tree['coords']=WKT_to_point(res[2])
224            tree['type']=res[3]
225            tree['height']=res[4]
226            trees.append(tree)
227        return trees
228
229    def select_barriers(self):
230        #print "barriers: SELECT osm_id,tags->'barrier' as barrier,ST_AsText(\"way\") AS geom, tags->'height' as height "+self.FnW+" \"way\" && "+self.googbox+" and tags ? 'barrier' "+LIMIT+";"
231        self.curs.execute("SELECT osm_id,tags->'barrier' as barrier,ST_AsText(\"way\") AS geom, tags->'height' as height "+self.FnW+" \"way\" && "+self.googbox+" and tags ? 'barrier' "+LIMIT+";")
232        rs = self.curs.fetchall()
233        barriers = []
234        for res in rs:
235            barrier = {}
236            barrier['osm_id']=res[0]
237            barrier['barrier']=res[1]
238            barrier['coords']=WKT_to_point(res[2])
239            barrier['height']=res[3]
240            barriers.append(barrier)
241        return barriers
242
243    def select_barrier_lines(self):
244        self.curs.execute("SELECT osm_id,tags->'barrier' as barrier,ST_AsText(ST_Buffer(\"way\",0.35)) AS geom, tags->'height' as height "+self.FlW+" \"way\" && "+self.googbox+" and tags ? 'barrier' "+LIMIT+";")
245        # ,'join=mitre mitre_limit=5.0' (requires GEOS 3.2)
246        rs = self.curs.fetchall()
247        barriers = []
248        for res in rs:
249            barrier = {}
250            barrier['osm_id']=res[0]
251            barrier['barrier']=res[1]
252            barrier['coords']=WKT_to_polygon(res[2])
253            barrier['height']=res[3]
254            barriers.append(barrier)
255        return barriers
256
257    def shutdown(self):
258        self.conn.rollback()
259
260class OSMDB:
261    """ OSM Database """
262    DSN = None
263    conn = None
264    curs = None
265
266    def __init__( self, dsn = None ):
267        self.DSN = dsn
268        self.conn = psycopg2.connect(self.DSN)
269        print "Encoding for this connection is", self.conn.encoding
270        self.curs = self.conn.cursor()
271    def __del__(self):
272        print "Closing connection"
273        self.conn.rollback()
274        self.conn.close()
275    def dummy(self,bbox):
276        #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'")
277        result=[]
278        #self.curs.execute("select osm_id,name from planet_line where \"way\" && SetSRID('BOX3D(1101474.25471931 6406603.879863935,1114223.324055468 6415715.307134068)'::box3d, 900913)")
279        print "result for bbox("+bbox+")"
280        self.curs.execute("select osm_id,name from planet_line where \"way\" && SetSRID('BOX3D("+bbox+")'::box3d, 4326)")
281        result += self.curs.fetchall()
282        print "resultlen="+len(result)
283
284def main(options):
285    bbox = options['bbox']
286    DSN = options['dsn']
287    print bbox
288    osmdb = OsrayDB(DSN)
289    #highways = getHighwaysInBbox(DSN,bbox)
290    bxarray=bbox.split(",")
291    bbox="{b} {l},{t} {r}".format(b=bxarray[0],l=bxarray[1],t=bxarray[2],r=bxarray[3])
292    osmdb.set_bbox(bbox)
293    osmdb.dummy(bbox)
294
295if __name__ == '__main__':
296    parser = OptionParser()
297    parser.add_option("-b", "--bbox", dest="bbox", help="bounding box to restrict to", default="")
298    parser.add_option("-d", "--dsn", dest="dsn", help="DSN, default is 'dbname=gis host=crite'", default="dbname=gis host=crite")
299    (options, args) = parser.parse_args()
300    print options
301    main(options.__dict__)
302    sys.exit(0)
Note: See TracBrowser for help on using the repository browser.