source: subversion/applications/rendering/parking/joinways/osmdb.py @ 28321

Revision 28321, 10.3 KB checked in by kdrangmeister, 2 years ago (diff)

added commit method

  • Property svn:mime-type set to text/plain
Line 
1# -*- coding: utf-8 -*-
2# by kay
3
4import sys
5import psycopg2
6
7class OSMDB:
8    """ Handles queries to the planet database """
9
10    def __init__(self,dsn,prefix="planet"):
11        self.DSN = dsn
12        self.prefix = prefix
13        self.LIMIT = ""
14
15        print "Opening connection using dsn:", self.DSN
16        self.conn = psycopg2.connect(self.DSN)
17        print "Encoding for this connection is", self.conn.encoding
18        self.curs = self.conn.cursor()
19
20        self.clear_bbox()
21
22        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))'
23        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"
24        self.FlW = "FROM "+prefix+"_line WHERE"
25        self.FpW = "FROM "+prefix+"_polygon WHERE"
26        self.FnW = "FROM "+prefix+"_point WHERE"
27
28    def __del__(self):
29        print "Closing connection"
30        self.conn.commit()
31        self.conn.close()
32
33    def commit(self):
34        self.conn.commit()
35
36    def select(self,select):
37        self.curs.execute(select)
38        rs = self.curs.fetchall()
39        return rs
40
41# ---------------------------------------------------------------------------
42
43    def clear_bbox(self):
44        self.bbox = None
45        self.googbox = None
46       
47    def set_bbox(self,bbox,srs = '4326'):
48        #options.get('srs','4326')
49        if srs=='4326':
50            self.init_bbox_srs(bbox, '4326')
51        elif srs=='3857':
52            self.init_bbox_srs(bbox, '3857')
53        elif srs=='900913':
54            self.init_bbox_srs(bbox, '3857')
55        else:
56            raise ValueError("Unknown srs "+str(srs))
57   
58    def init_bbox_srs(self,bbox,srs):
59        self.googbox = "transform(SetSRID('BOX3D("+bbox+")'::box3d,"+srs+"),900913)"
60        self.curs.execute("SELECT ST_AsText("+self.googbox+") AS geom")
61        self.bbox = self.curs.fetchall()
62        self.get_bounds()
63
64    def get_bounds(self):
65        polygonstring = self.bbox[0][0]
66        polygonstring = polygonstring[9:] # cut off the "POLYGON(("
67        polygonstring = polygonstring[:-2] # cut off the "))"
68        points = polygonstring.split(',')
69
70        numpoints = len(points)
71        for i,point in enumerate(points):
72            latlon = point.split(' ')
73            if (i==0):
74                self.left=float(latlon[0])
75                self.bottom=float(latlon[1])
76            if (i==2):
77                self.right=float(latlon[0])
78                self.top=float(latlon[1])
79        print "Bounds [b l t r] = ",self.bottom,self.left,self.top,self.right
80
81# ---------------------------------------------------------------------------
82
83    def select_highways(self):
84        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 "+self.LIMIT+";")
85        rs = self.curs.fetchall()
86        highways = []
87        for res in rs:
88            highway = {}
89            highway['osm_id']=res[0]
90            highway['highway']=res[1]
91            highway['coords']=WKT_to_line(res[2])
92            highway['lanes']=res[3]
93            highway['layer']=res[4]
94            highway['oneway']=res[5]
95            highway['lanesfw']=res[6]
96            highway['lanesbw']=res[7]
97            highways.append(highway)
98        return highways
99
100    def select_highway_areas(self):
101        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 "+self.LIMIT+";")
102        rs = self.curs.fetchall()
103        areas = []
104        for res in rs:
105            area = {}
106            area['osm_id']=res[0]
107            area['highway']=res[1]
108            area['coords']=WKT_to_polygon(res[2])
109            area['height']=res[3]
110            area['amenity']=res[4]
111            area['buffercoords']=WKT_to_polygon(res[5])
112            areas.append(area)
113        return areas
114
115    def select_amenity_areas(self):
116        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')"+self.LIMIT+";")
117        rs = self.curs.fetchall()
118        areas = []
119        for res in rs:
120            area = {}
121            area['osm_id']=res[0]
122            area['amenity']=res[1]
123            area['coords']=WKT_to_polygon(res[2])
124            area['buffercoords']=WKT_to_polygon(res[3])
125            area['height']=res[4]
126            areas.append(area)
127        return areas
128
129    def select_buildings(self,buildingtype):
130        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+"' "+self.LIMIT+";")
131        rs = self.curs.fetchall()
132        buildings = []
133        for res in rs:
134            building = {}
135            building['osm_id']=res[0]
136            building['coords']=WKT_to_polygon(res[1])
137            building['building']=res[2]
138            building['height']=res[3]
139            building['bheight']=res[4]
140            building['amenity']=res[5]
141            building['shop']=res[6]
142            buildings.append(building)
143        return buildings
144
145    def select_landuse(self,landusetype):
146        #print "SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";"
147        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+self.LIMIT+";")
148        rs = self.curs.fetchall()
149        landuses = []
150        for res in rs:
151            landuse = {}
152            landuse['osm_id']=res[0]
153            landuse['landuse']=res[1]
154            landuse['coords']=WKT_to_polygon(res[2])
155            landuses.append(landuse)
156        return landuses
157
158    def select_landuse_areas(self):
159        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse is not NULL "+self.LIMIT+";")
160        rs = self.curs.fetchall()
161        landuses = []
162        for res in rs:
163            landuse = {}
164            landuse['osm_id']=res[0]
165            landuse['landuse']=res[1]
166            landuse['coords']=WKT_to_polygon(res[2])
167            landuses.append(landuse)
168        return landuses
169
170    def select_leisure_areas(self):
171        self.curs.execute("SELECT osm_id,leisure,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and leisure is not NULL "+self.LIMIT+";")
172        rs = self.curs.fetchall()
173        leisures = []
174        for res in rs:
175            leisure = {}
176            leisure['osm_id']=res[0]
177            leisure['leisure']=res[1]
178            leisure['coords']=WKT_to_polygon(res[2])
179            leisures.append(leisure)
180        return leisures
181
182    def select_waterway(self,waterwaytype):
183        self.curs.execute("SELECT osm_id,waterway,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and waterway='"+waterwaytype+"' "+self.LIMIT+";")
184        rs = self.curs.fetchall()
185        waterways = []
186        for res in rs:
187            waterway = {}
188            waterway['osm_id']=res[0]
189            waterway['waterway']=res[1]
190            waterway['coords']=WKT_to_polygon(res[2])
191            waterways.append(waterway)
192        return waterways
193
194    def select_naturalwater(self):
195        naturaltype='water'
196        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+"' "+self.LIMIT+";")
197        rs = self.curs.fetchall()
198        waters = []
199        for res in rs:
200            water = {}
201            water['osm_id']=res[0]
202            water['natural']=res[1]
203            water['coords']=WKT_to_polygon(res[2])
204            water['type']=res[3]
205            water['layer']=res[4]
206            waters.append(water)
207        return waters
208
209    def select_trees(self):
210        naturaltype='tree'
211        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+"' "+self.LIMIT+";")
212        rs = self.curs.fetchall()
213        trees = []
214        for res in rs:
215            tree = {}
216            tree['osm_id']=res[0]
217            tree['natural']=res[1]
218            tree['coords']=WKT_to_point(res[2])
219            tree['type']=res[3]
220            tree['height']=res[4]
221            trees.append(tree)
222        return trees
223
224    def select_barriers(self):
225        #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+";"
226        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' "+self.LIMIT+";")
227        rs = self.curs.fetchall()
228        barriers = []
229        for res in rs:
230            barrier = {}
231            barrier['osm_id']=res[0]
232            barrier['barrier']=res[1]
233            barrier['coords']=WKT_to_point(res[2])
234            barrier['height']=res[3]
235            barriers.append(barrier)
236        return barriers
237
238    def select_barrier_lines(self):
239        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' "+self.LIMIT+";")
240        # ,'join=mitre mitre_limit=5.0' (requires GEOS 3.2)
241        rs = self.curs.fetchall()
242        barriers = []
243        for res in rs:
244            barrier = {}
245            barrier['osm_id']=res[0]
246            barrier['barrier']=res[1]
247            barrier['coords']=WKT_to_polygon(res[2])
248            barrier['height']=res[3]
249            barriers.append(barrier)
250        return barriers
251
Note: See TracBrowser for help on using the repository browser.