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

Revision 28339, 11.2 KB checked in by kdrangmeister, 2 years ago (diff)

added sql_list_of_ids

  • Property svn:mime-type set to text/plain
Line 
1# -*- coding: utf-8 -*-
2# by kay
3
4import sys,string
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    def select_one(self,select):
42        """ Return exactly one result from select. None if no result rows. """
43        self.curs.execute(select)
44        rs = self.curs.fetchall()
45        if len(rs)==0:
46            return None
47        return rs[0][0]
48
49    def select_list(self,select):
50        """ Return a list of results (one column only!) from select. Empty list if no result rows. """
51        self.curs.execute(select)
52        rs = self.curs.fetchall()
53        l = []
54        for res in rs:
55            l.append(res[0])
56        return l
57
58    def insert(self,insert):
59        self.curs.execute(insert)
60
61    def update(self,update):
62        self.curs.execute(update)
63
64    def delete(self,delete):
65        self.curs.execute(delete)
66
67    def sql_list_of_ids(self,list):
68        list_ids_as_strings=map(lambda osmid: str(osmid),list)
69        return "("+string.join(list_ids_as_strings,',')+")"
70
71# ---------------------------------------------------------------------------
72
73    def clear_bbox(self):
74        self.bbox = None
75        self.googbox = None
76       
77    def set_bbox(self,bbox,srs = '4326'):
78        #options.get('srs','4326')
79        if srs=='4326':
80            self.init_bbox_srs(bbox, '4326')
81        elif srs=='3857':
82            self.init_bbox_srs(bbox, '3857')
83        elif srs=='900913':
84            self.init_bbox_srs(bbox, '3857')
85        else:
86            raise ValueError("Unknown srs "+str(srs))
87   
88    def init_bbox_srs(self,bbox,srs):
89        self.googbox = "transform(SetSRID('BOX3D("+bbox+")'::box3d,"+srs+"),900913)"
90        self.curs.execute("SELECT ST_AsText("+self.googbox+") AS geom")
91        self.bbox = self.curs.fetchall()
92        self.get_bounds()
93
94    def get_bounds(self):
95        polygonstring = self.bbox[0][0]
96        polygonstring = polygonstring[9:] # cut off the "POLYGON(("
97        polygonstring = polygonstring[:-2] # cut off the "))"
98        points = polygonstring.split(',')
99
100        numpoints = len(points)
101        for i,point in enumerate(points):
102            latlon = point.split(' ')
103            if (i==0):
104                self.left=float(latlon[0])
105                self.bottom=float(latlon[1])
106            if (i==2):
107                self.right=float(latlon[0])
108                self.top=float(latlon[1])
109        print "Bounds [b l t r] = ",self.bottom,self.left,self.top,self.right
110
111# ---------------------------------------------------------------------------
112
113    def select_highways(self):
114        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+";")
115        rs = self.curs.fetchall()
116        highways = []
117        for res in rs:
118            highway = {}
119            highway['osm_id']=res[0]
120            highway['highway']=res[1]
121            highway['coords']=WKT_to_line(res[2])
122            highway['lanes']=res[3]
123            highway['layer']=res[4]
124            highway['oneway']=res[5]
125            highway['lanesfw']=res[6]
126            highway['lanesbw']=res[7]
127            highways.append(highway)
128        return highways
129
130    def select_highway_areas(self):
131        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+";")
132        rs = self.curs.fetchall()
133        areas = []
134        for res in rs:
135            area = {}
136            area['osm_id']=res[0]
137            area['highway']=res[1]
138            area['coords']=WKT_to_polygon(res[2])
139            area['height']=res[3]
140            area['amenity']=res[4]
141            area['buffercoords']=WKT_to_polygon(res[5])
142            areas.append(area)
143        return areas
144
145    def select_amenity_areas(self):
146        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+";")
147        rs = self.curs.fetchall()
148        areas = []
149        for res in rs:
150            area = {}
151            area['osm_id']=res[0]
152            area['amenity']=res[1]
153            area['coords']=WKT_to_polygon(res[2])
154            area['buffercoords']=WKT_to_polygon(res[3])
155            area['height']=res[4]
156            areas.append(area)
157        return areas
158
159    def select_buildings(self,buildingtype):
160        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+";")
161        rs = self.curs.fetchall()
162        buildings = []
163        for res in rs:
164            building = {}
165            building['osm_id']=res[0]
166            building['coords']=WKT_to_polygon(res[1])
167            building['building']=res[2]
168            building['height']=res[3]
169            building['bheight']=res[4]
170            building['amenity']=res[5]
171            building['shop']=res[6]
172            buildings.append(building)
173        return buildings
174
175    def select_landuse(self,landusetype):
176        #print "SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";"
177        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+self.LIMIT+";")
178        rs = self.curs.fetchall()
179        landuses = []
180        for res in rs:
181            landuse = {}
182            landuse['osm_id']=res[0]
183            landuse['landuse']=res[1]
184            landuse['coords']=WKT_to_polygon(res[2])
185            landuses.append(landuse)
186        return landuses
187
188    def select_landuse_areas(self):
189        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse is not NULL "+self.LIMIT+";")
190        rs = self.curs.fetchall()
191        landuses = []
192        for res in rs:
193            landuse = {}
194            landuse['osm_id']=res[0]
195            landuse['landuse']=res[1]
196            landuse['coords']=WKT_to_polygon(res[2])
197            landuses.append(landuse)
198        return landuses
199
200    def select_leisure_areas(self):
201        self.curs.execute("SELECT osm_id,leisure,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and leisure is not NULL "+self.LIMIT+";")
202        rs = self.curs.fetchall()
203        leisures = []
204        for res in rs:
205            leisure = {}
206            leisure['osm_id']=res[0]
207            leisure['leisure']=res[1]
208            leisure['coords']=WKT_to_polygon(res[2])
209            leisures.append(leisure)
210        return leisures
211
212    def select_waterway(self,waterwaytype):
213        self.curs.execute("SELECT osm_id,waterway,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and waterway='"+waterwaytype+"' "+self.LIMIT+";")
214        rs = self.curs.fetchall()
215        waterways = []
216        for res in rs:
217            waterway = {}
218            waterway['osm_id']=res[0]
219            waterway['waterway']=res[1]
220            waterway['coords']=WKT_to_polygon(res[2])
221            waterways.append(waterway)
222        return waterways
223
224    def select_naturalwater(self):
225        naturaltype='water'
226        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+";")
227        rs = self.curs.fetchall()
228        waters = []
229        for res in rs:
230            water = {}
231            water['osm_id']=res[0]
232            water['natural']=res[1]
233            water['coords']=WKT_to_polygon(res[2])
234            water['type']=res[3]
235            water['layer']=res[4]
236            waters.append(water)
237        return waters
238
239    def select_trees(self):
240        naturaltype='tree'
241        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+";")
242        rs = self.curs.fetchall()
243        trees = []
244        for res in rs:
245            tree = {}
246            tree['osm_id']=res[0]
247            tree['natural']=res[1]
248            tree['coords']=WKT_to_point(res[2])
249            tree['type']=res[3]
250            tree['height']=res[4]
251            trees.append(tree)
252        return trees
253
254    def select_barriers(self):
255        #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+";"
256        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+";")
257        rs = self.curs.fetchall()
258        barriers = []
259        for res in rs:
260            barrier = {}
261            barrier['osm_id']=res[0]
262            barrier['barrier']=res[1]
263            barrier['coords']=WKT_to_point(res[2])
264            barrier['height']=res[3]
265            barriers.append(barrier)
266        return barriers
267
268    def select_barrier_lines(self):
269        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+";")
270        # ,'join=mitre mitre_limit=5.0' (requires GEOS 3.2)
271        rs = self.curs.fetchall()
272        barriers = []
273        for res in rs:
274            barrier = {}
275            barrier['osm_id']=res[0]
276            barrier['barrier']=res[1]
277            barrier['coords']=WKT_to_polygon(res[2])
278            barrier['height']=res[3]
279            barriers.append(barrier)
280        return barriers
281
Note: See TracBrowser for help on using the repository browser.