source: subversion/applications/rendering/parking/osray/osray_db.py @ 22386

Last change on this file since 22386 was 22386, checked in by kdrangmeister, 9 years ago

landuse-types

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