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

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

bugfix

File size: 7.1 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['way']=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        return self.curs.fetchall()
109
110    def select_buildings(self,buildingtype):
111        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+";")
112        return self.curs.fetchall()
113
114    def select_landuse(self,landusetype):
115        #print "SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";"
116        self.curs.execute("SELECT osm_id,landuse,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and landuse='"+landusetype+"' "+LIMIT+";")
117        return self.curs.fetchall()
118
119    def select_waterway(self,waterwaytype):
120        self.curs.execute("SELECT osm_id,waterway,ST_AsText(\"way\") AS geom "+self.FpW+" \"way\" && "+self.googbox+" and waterway='"+waterwaytype+"' "+LIMIT+";")
121        return self.curs.fetchall()
122
123    def select_naturalwater(self):
124        naturaltype='water'
125        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+";")
126        rs = self.curs.fetchall()
127        waters = []
128        for res in rs:
129            water = {}
130            water['osm_id']=res[0]
131            water['natural']=res[1]
132            water['coords']=WKT_to_point(res[2])
133            water['type']=res[3]
134            water['layer']=res[4]
135            waters.append(water)
136        return waters
137
138    def select_trees(self):
139        naturaltype='tree'
140        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+";")
141        rs = self.curs.fetchall()
142        trees = []
143        for res in rs:
144            tree = {}
145            tree['osm_id']=res[0]
146            tree['natural']=res[1]
147            tree['coords']=WKT_to_point(res[2])
148            tree['type']=res[3]
149            tree['height']=res[4]
150            trees.append(tree)
151        return trees
152
153    def select_barriers(self):
154        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+";")
155        rs = self.curs.fetchall()
156        barriers = []
157        for res in rs:
158            barrier = {}
159            barrier['osm_id']=res[0]
160            barrier['barrier']=res[1]
161            barrier['coords']=WKT_to_point(res[2])
162            barrier['height']=res[3]
163            barriers.append(barrier)
164        return barriers
165
166    def shutdown(self):
167        self.conn.rollback()
Note: See TracBrowser for help on using the repository browser.