source: subversion/applications/rendering/parking/shellscripts/osm-update-db.py @ 21693

Last change on this file since 21693 was 21522, checked in by kdrangmeister, 10 years ago

Parking Icons

File size: 7.0 KB
Line 
1# -*- coding: utf-8 -*-
2# by kay - basic functions
3
4DSN = 'dbname=gis'
5
6#class SimpleQuoter(object):
7#    def sqlquote(x=None):
8#        return "'bar'"
9
10import sys
11import psycopg2
12
13if len(sys.argv) > 1:
14    DSN = sys.argv[1]
15
16print "Opening connection using dns:", DSN
17conn = psycopg2.connect(DSN)
18print "Encoding for this connection is", conn.encoding
19curs = conn.cursor()
20
21
22curs.execute("SELECT column_name FROM information_schema.columns WHERE table_name ='planet_osm_line'")
23colnames = curs.fetchall()
24
25#print colnames
26
27#print "------------------------------"
28
29#print zip(*colnames)
30
31print "--------------------------------------------------"
32print "Set no_stopping on trunk roads"
33curs.execute("UPDATE planet_osm_line set \"parking:lane:both\"='no_stopping' where highway IN ('trunk','trunk_link') and \"parking:lane:both\" is NULL and \"parking:lane:left\" is NULL and \"parking:lane:right\" is NULL")
34print "-> " , curs.statusmessage
35
36print "Set no_stopping on roundabouts"
37curs.execute("UPDATE planet_osm_line set \"parking:lane:both\"='no_stopping' where junction='roundabout' and \"parking:lane:both\" is NULL and \"parking:lane:left\" is NULL and \"parking:lane:right\" is NULL")
38print "-> " , curs.statusmessage
39
40
41
42print "--------------------------------------------------"
43print "Finland conversion:"
44
45print "Converting :both to :left + :right"
46for lane in ['inline','orthogonal','diagonal','no']:
47  print "Writing 'parking:left/right=%s' where 'parking:both=%s'" % (lane,lane)
48  curs.execute("UPDATE planet_osm_line SET \"parking:left\"=%s,\"parking:right\"=%s,\"parking:both\"=NULL WHERE \"parking:both\"=%s",(lane,lane,lane))
49  print "-> " , curs.statusmessage
50
51print "Converting halting:both to :left + :right"
52for lane in ['no']:
53  print "Writing 'halting:left/right=%s' where 'halting:both=%s'" % (lane,lane)
54  curs.execute("UPDATE planet_osm_line SET \"halting:left\"=%s,\"halting:right\"=%s,\"halting:both\"=NULL WHERE \"halting:both\"=%s",(lane,lane,lane))
55  print "-> " , curs.statusmessage
56
57print "Converting no to no_parking"
58print "Writing 'parking:left=no_parking' where 'parking:left=no'"
59curs.execute("UPDATE planet_osm_line SET \"parking:left\"='no_parking' WHERE \"parking:left\"='no'",())
60print "-> " , curs.statusmessage
61print "Writing 'parking:right=no_parking' where 'parking:right=no'"
62curs.execute("UPDATE planet_osm_line SET \"parking:right\"='no_parking' WHERE \"parking:right\"='no'",())
63print "-> " , curs.statusmessage
64
65print "Converting halting:no to no_stopping"
66print "Writing 'parking:left=no_stopping' where 'halting:left=no'"
67curs.execute("UPDATE planet_osm_line SET \"parking:left\"='no_stopping' WHERE \"halting:left\"='no'",())
68print "-> " , curs.statusmessage
69print "Writing 'parking:right=no_stopping' where 'halting:right=no'"
70curs.execute("UPDATE planet_osm_line SET \"parking:right\"='no_stopping' WHERE \"halting:right\"='no'",())
71print "-> " , curs.statusmessage
72
73print "Converting parking:left/right to parking:lane:left/right"
74for lane in ['inline','orthogonal','diagonal','no_parking','no_stopping','fire_lane']:
75  print "Writing 'parking:lane:left=%s' where 'parking:left=%s'" % (lane,lane)
76  curs.execute("UPDATE planet_osm_line SET \"parking:lane:left\"=%s WHERE \"parking:left\"=%s",(lane,lane))
77  print "-> " , curs.statusmessage
78  print "Writing 'parking:lane:right=%s' where 'parking:right=%s'" % (lane,lane)
79  curs.execute("UPDATE planet_osm_line SET \"parking:lane:right\"=%s WHERE \"parking:right\"=%s",(lane,lane))
80  print "-> " , curs.statusmessage
81
82print "Converting parking:fee=yes to parking:condition:both=ticket"
83print "Writing 'parking:condition:both=ticket' where 'parking:fee=yes'"
84curs.execute("UPDATE planet_osm_line SET \"parking:condition:both\"='ticket' WHERE \"parking:fee\"='yes'",())
85
86print "End Finland conversion"
87print "--------------------------------------------------"
88
89print "--------------------------------------------------"
90print "Converting :both to :left + :right"
91
92for lane in ['inline','orthogonal','diagonal','no_parking','no_stopping','fire_lane']:
93  print "Writing 'parking:lane:left/right=%s' where 'parking:lane:both=%s'" % (lane,lane)
94  #print curs.mogrify("UPDATE planet_osm_line SET \"parking:lane:left\"=%s,\"parking:lane:right\"=%s,\"parking:lane:both\"=NULL WHERE \"parking:lane:both\"=%s",(lane,lane,lane))
95  curs.execute("UPDATE planet_osm_line SET \"parking:lane:left\"=%s,\"parking:lane:right\"=%s,\"parking:lane:both\"=NULL WHERE \"parking:lane:both\"=%s",(lane,lane,lane))
96  print "-> " , curs.statusmessage
97
98for condition in ['free','disc','ticket','customers','residents','private']:
99  print "Writing 'parking:condition:left/right=%s' where 'parking:lane:both=%s'" % (condition,condition)
100  #print curs.mogrify("UPDATE planet_osm_line SET \"parking:condition:left\"=%s,\"parking:condition:right\"=%s,\"parking:condition:both\"=NULL WHERE \"parking:condition:both\"=%s and osm_id=47907684",[condition,condition,condition])
101  curs.execute("UPDATE planet_osm_line SET \"parking:condition:left\"=%s,\"parking:condition:right\"=%s,\"parking:condition:both\"=NULL WHERE \"parking:condition:both\"=%s",[condition,condition,condition])
102  print "-> " , curs.statusmessage
103
104  print "Writing 'parking:condition:left/right:vehicles=x' where 'parking:lane:both:vehicles=x'"
105  curs.execute("UPDATE planet_osm_line SET \"parking:condition:left:vehicles\"=\"parking:condition:both:vehicles\",\"parking:condition:right:vehicles\"=\"parking:condition:both:vehicles\",\"parking:condition:both:vehicles\"=NULL WHERE \"parking:condition:both:vehicles\" is not NULL")
106  print "-> " , curs.statusmessage
107
108print "--------------------------------------------------"
109print "Customer parking"
110
111print "Writing 'parking:condition:area=customers' where 'amenity=parking' and 'access=customers'"
112curs.execute("UPDATE planet_osm_polygon set \"parking:condition:area\"='customers' where amenity='parking' and access='customers' and \"parking:condition:area\" is NULL")
113print "-> " , curs.statusmessage
114
115print "Disc parking"
116
117print "Writing 'parking:condition:area=disc' where 'amenity=parking' and 'disc_parking=yes'"
118curs.execute("UPDATE planet_osm_polygon set \"parking:condition:area\"='disc' where amenity='parking' and disc_parking='yes' and \"parking:condition:area\" is NULL")
119print "-> " , curs.statusmessage
120print "Writing 'parking:condition:area=disc' where 'amenity=parking' and 'maxstay not NULL'"
121curs.execute("UPDATE planet_osm_polygon set \"parking:condition:area\"='disc' where amenity='parking' and maxstay is not NULL and \"parking:condition:area\" is NULL")
122print "-> " , curs.statusmessage
123
124print "--------------------------------------------------"
125print "unknown parking (lane but no condition)"
126
127curs.execute("UPDATE planet_osm_line set \"parking:condition:left\"='unknown' where  \"parking:lane:left\" IN ('inline','orthogonal','diagonal') and \"parking:condition:left\" is NULL")
128print "-> " , curs.statusmessage
129
130curs.execute("UPDATE planet_osm_line set \"parking:condition:right\"='unknown' where  \"parking:lane:right\" IN ('inline','orthogonal','diagonal') and \"parking:condition:right\" is NULL")
131print "-> " , curs.statusmessage
132
133#conn.rollback()
134conn.commit()
135
136sys.exit(0)
137
Note: See TracBrowser for help on using the repository browser.