1 | # -*- coding: utf-8 -*- |
---|
2 | # by kay - basic functions |
---|
3 | |
---|
4 | ### config for Toolserver |
---|
5 | #DSN = 'dbname=osm_mapnik host=sql-mapnik' |
---|
6 | #openlayertextfilename = '/home/kayd/parkingicons/parkingicons.txt' |
---|
7 | ### config for devserver |
---|
8 | #DSN = 'dbname=hstore' |
---|
9 | #openlayertextfilename = '/osm/parking/parkingicons/parkingicons.txt' |
---|
10 | |
---|
11 | import sys |
---|
12 | import psycopg2 |
---|
13 | import csv |
---|
14 | from numpy import math,angle |
---|
15 | |
---|
16 | def shift_by_meters(lat, lon, brng, d): |
---|
17 | R = 6371000.0 # earth's radius in m |
---|
18 | lat=math.radians(lat) |
---|
19 | lon=math.radians(lon) |
---|
20 | lat2 = math.asin( math.sin(lat)*math.cos(d/R) + |
---|
21 | math.cos(lat)*math.sin(d/R)*math.cos(brng)) |
---|
22 | lon2 = lon + math.atan2(math.sin(brng)*math.sin(d/R)*math.cos(lat), |
---|
23 | math.cos(d/R)-math.sin(lat)*math.sin(lat2)) |
---|
24 | lat2=math.degrees(lat2) |
---|
25 | lon2=math.degrees(lon2) |
---|
26 | return [lat2,lon2] |
---|
27 | |
---|
28 | def calc_bearing(x1,y1,x2,y2,side): |
---|
29 | Q = complex(x1,y1) |
---|
30 | R = complex(x2,y2) |
---|
31 | v = R-Q |
---|
32 | if side=='left': |
---|
33 | v=v*complex(0,1); |
---|
34 | elif side=='right': |
---|
35 | v=v*complex(0,-1); |
---|
36 | else: |
---|
37 | raise TypeError('side must be left or right') |
---|
38 | #v=v*(1/abs(v)) # normalize |
---|
39 | angl = angle(v) # angle (radians) (0°=right, and counterclockwise) |
---|
40 | bearing = math.pi/2.0-angl # (0°=up, and clockwise) |
---|
41 | return bearing |
---|
42 | |
---|
43 | def unboth(lst,sideindex): |
---|
44 | """ Replace in a list all rows with 'both' with two rows with 'left' and 'right' |
---|
45 | """ |
---|
46 | list_both = list(lst) |
---|
47 | list_both.reverse() |
---|
48 | lst = [] |
---|
49 | while len(list_both)>0: |
---|
50 | row = list_both.pop() |
---|
51 | print "row=", row |
---|
52 | print "row.type=", type(row) |
---|
53 | side = row[sideindex] |
---|
54 | if side=='both': |
---|
55 | row_l = list(row) |
---|
56 | row_l[sideindex] = 'left' |
---|
57 | #print 'bothl:', row_l |
---|
58 | lst += [row_l] |
---|
59 | row_r = list(row) |
---|
60 | row_r[sideindex] = 'right' |
---|
61 | #print 'bothr:', row_r |
---|
62 | lst += [row_r] |
---|
63 | else: |
---|
64 | #print side, ":", row |
---|
65 | lst += [row] |
---|
66 | return lst |
---|
67 | |
---|
68 | if len(sys.argv) == 3: |
---|
69 | DSN = sys.argv[1] |
---|
70 | openlayertextfilename = sys.argv[2] |
---|
71 | else: |
---|
72 | print "usage: osm-parking-icons.py 'dbname=osm_mapnik host=sql-mapnik' '/home/kayd/parkingicons/parkingicons.txt'" |
---|
73 | exit(0); |
---|
74 | |
---|
75 | print "Opening connection using dns:", DSN |
---|
76 | conn = psycopg2.connect(DSN) |
---|
77 | print "Encoding for this connection is", conn.encoding |
---|
78 | curs = conn.cursor() |
---|
79 | |
---|
80 | openlayertextfile = csv.writer(open(openlayertextfilename, 'w'), delimiter='\t',quotechar='"', quoting=csv.QUOTE_MINIMAL) |
---|
81 | openlayertextfile.writerow(['lat','lon','title','description','icon','iconSize','iconOffset']) |
---|
82 | |
---|
83 | 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))" |
---|
84 | 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" |
---|
85 | FW = "FROM planet_line WHERE" |
---|
86 | |
---|
87 | ### display disc - maxstay |
---|
88 | |
---|
89 | pc_disc_maxstay = [] |
---|
90 | for side in ['left','right','both']: |
---|
91 | curs.execute("SELECT osm_id,"+latlon+",(tags->'parking:condition:"+side+":maxstay') as \"parking:condition:"+side+":maxstay\","+coords+",'"+side+"' "+FW+" (tags ? 'parking:condition:"+side+":maxstay') and (tags ? 'parking:condition:"+side+"') and (tags->'parking:condition:"+side+"')='disc'") |
---|
92 | pc_disc_maxstay += curs.fetchall() |
---|
93 | |
---|
94 | pc_disc_maxstay = unboth(pc_disc_maxstay,10) |
---|
95 | |
---|
96 | for pc_dm in pc_disc_maxstay: |
---|
97 | side = pc_dm[10] |
---|
98 | bearing = calc_bearing(pc_dm[7],pc_dm[6], pc_dm[9],pc_dm[8], side) |
---|
99 | openlayertextfile.writerow(shift_by_meters(pc_dm[1],pc_dm[2],bearing,4.0)+['Disc parking','Maximum parking time:<br>'+pc_dm[3],'parkingicons/pi-disc.png','16,16','-8,-8']) |
---|
100 | |
---|
101 | ### display vehicles |
---|
102 | |
---|
103 | pc_vehicles = [] |
---|
104 | for side in ['left','right','both']: |
---|
105 | curs.execute("SELECT osm_id,"+latlon+",(tags->'parking:condition:"+side+":vehicles') as \"parking:condition:"+side+":vehicles\","+coords+",'"+side+"' "+FW+" (tags ? 'parking:condition:"+side+":vehicles')") |
---|
106 | pc_vehicles += curs.fetchall() |
---|
107 | |
---|
108 | vehicle_icons = {"car":"parkingicons/pi-car.png" , "bus":"parkingicons/pi-bus.png" , "motorcycle":"parkingicons/pi-motorcycle.png"} |
---|
109 | |
---|
110 | pc_vehicles = unboth(pc_vehicles,10) |
---|
111 | |
---|
112 | for pc_v in pc_vehicles: |
---|
113 | vehicle_icon = vehicle_icons.get(pc_v[3],"parkingicons/pi-unkn.png"); |
---|
114 | bearing = calc_bearing(pc_v[7],pc_v[6], pc_v[9],pc_v[8], pc_v[10]) |
---|
115 | openlayertextfile.writerow(shift_by_meters(pc_v[1],pc_v[2],bearing,4.0)+['Parking only for','Vehicle : '+pc_v[3],vehicle_icon,'16,16','-8,-8']) |
---|
116 | |
---|
117 | conn.rollback() |
---|
118 | |
---|
119 | sys.exit(0) |
---|
120 | |
---|
121 | """ |
---|
122 | SELECT |
---|
123 | ST_Y(way) AS lat_wgs84, |
---|
124 | ST_X(way) AS lon_wgs84, |
---|
125 | ST_X(transform(way, 31466)) AS KOORD_X, |
---|
126 | ST_Y(transform(way, 31466)) AS KOORD_Y, |
---|
127 | skeys(tags) AS key, |
---|
128 | svals(tags) AS value, |
---|
129 | osm_id |
---|
130 | FROM |
---|
131 | dortmund_point |
---|
132 | WHERE |
---|
133 | exist(tags, 'amenity') |
---|
134 | LIMIT 10; |
---|
135 | |
---|
136 | SELECT |
---|
137 | ST_Y(way) AS lat_wgs84, |
---|
138 | ST_X(way) AS lon_wgs84, |
---|
139 | ST_X(transform(way, 31466)) AS KOORD_X, |
---|
140 | ST_Y(transform(way, 31466)) AS KOORD_Y, |
---|
141 | key, |
---|
142 | value, |
---|
143 | tags->'name' as name, |
---|
144 | osm_id |
---|
145 | FROM |
---|
146 | (SELECT |
---|
147 | osm_id, way, tags, |
---|
148 | (each(tags)).key, |
---|
149 | (each(tags)).value |
---|
150 | FROM |
---|
151 | dortmund_point |
---|
152 | WHERE |
---|
153 | exist(tags, 'amenity') /*AND exist(tags, 'name')*/ |
---|
154 | ) AS sq |
---|
155 | WHERE |
---|
156 | key = 'amenity' |
---|
157 | LIMIT 10; |
---|
158 | |
---|
159 | SELECT |
---|
160 | ST_Y(way) AS lat_wgs84, |
---|
161 | ST_X(way) AS lon_wgs84, |
---|
162 | ST_X(transform(way, 31466)) AS KOORD_X, |
---|
163 | ST_Y(transform(way, 31466)) AS KOORD_Y, |
---|
164 | key, |
---|
165 | value, |
---|
166 | tags->'name' as name, |
---|
167 | tags->'sport' as sport, |
---|
168 | osm_id |
---|
169 | FROM |
---|
170 | (SELECT |
---|
171 | osm_id, tags, |
---|
172 | ST_Centroid(way) as way, |
---|
173 | (each(tags)).key, |
---|
174 | (each(tags)).value |
---|
175 | FROM |
---|
176 | dortmund_polygon |
---|
177 | WHERE |
---|
178 | tags->'natural' = 'water' |
---|
179 | AND |
---|
180 | tags->'sport' = 'swimming' |
---|
181 | ) AS sq |
---|
182 | WHERE |
---|
183 | key = 'natural' |
---|
184 | LIMIT 10; |
---|
185 | """ |
---|