source: subversion/applications/rendering/parking/parkingicons/osm_parking_icons_hstore.py @ 28519

Last change on this file since 28519 was 26130, checked in by kdrangmeister, 8 years ago

renamed files to conform to python conventions

File size: 5.8 KB
Line 
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
11import sys
12import psycopg2
13import csv
14from numpy import math,angle
15
16def 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
28def 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
43def 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
68if len(sys.argv) == 3:
69    DSN = sys.argv[1]
70    openlayertextfilename = sys.argv[2]
71else:
72    print "usage: osm-parking-icons.py 'dbname=osm_mapnik host=sql-mapnik' '/home/kayd/parkingicons/parkingicons.txt'"
73    exit(0);
74
75print "Opening connection using dns:", DSN
76conn = psycopg2.connect(DSN)
77print "Encoding for this connection is", conn.encoding
78curs = conn.cursor()
79
80openlayertextfile = csv.writer(open(openlayertextfilename, 'w'), delimiter='\t',quotechar='"', quoting=csv.QUOTE_MINIMAL)
81openlayertextfile.writerow(['lat','lon','title','description','icon','iconSize','iconOffset'])
82
83latlon= "ST_Y(ST_Transform(ST_line_interpolate_point(way,0.5),4326)),ST_X(ST_Transform(ST_line_interpolate_point(way,0.5),4326))"
84coords= "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"
85FW = "FROM planet_line WHERE"
86
87### display disc - maxstay
88
89pc_disc_maxstay = []
90for 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
94pc_disc_maxstay = unboth(pc_disc_maxstay,10)
95       
96for 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
103pc_vehicles = []
104for 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
108vehicle_icons = {"car":"parkingicons/pi-car.png" , "bus":"parkingicons/pi-bus.png" , "motorcycle":"parkingicons/pi-motorcycle.png"}
109
110pc_vehicles = unboth(pc_vehicles,10)
111
112for 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
117conn.rollback()
118
119sys.exit(0)
120
121"""
122SELECT
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
130FROM
131   dortmund_point
132WHERE
133   exist(tags, 'amenity')
134LIMIT 10;
135
136SELECT
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
145FROM
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
155WHERE
156   key = 'amenity'
157LIMIT 10;
158
159SELECT
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
169FROM
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
182WHERE
183   key = 'natural'
184LIMIT 10;
185"""
Note: See TracBrowser for help on using the repository browser.