p50wnRqa

1import psycopg2, datetime
2#from config import config
3  
4def create_tables(conn):
5    commands = (
6        """
7        DROP TABLE IF EXISTS taxi_services_T, tempo, stand, stand_geom, taxi, local, local_geom
8        """,
9        """
10    CREATE TABLE stand (
11        stand_id SERIAL NOT NULL,
12        name VARCHAR NOT NULL,
13        lotations INTEGER,
14        PRIMARY KEY (stand_id)
15        )
16        """,
17        """
18    CREATE TABLE stand_geom (
19        stand_id SERIAL NOT NULL,
20        name VARCHAR NOT NULL,
21        geom GEOMETRY,
22        freguesia VARCHAR,
23        concelho VARCHAR,
24        lotations INTEGER,
25        PRIMARY KEY (stand_id)
26        )
27        """
28    )
29  
30    delete_duplicates_flag = False
31    delete_duplicates = """
32        DELETE FROM cont_freg_v5 AS t1
33            WHERE EXISTS (
34                SELECT 1 FROM cont_freg_v5 AS t2
35                      WHERE t2.freguesia = t1.freguesia and
36                            t2.concelho = t1.concelho and
37                            t2.geom = t1.geom and
38                            t2.gid > t1.gid
39                 ); """
40    
41    cur = conn.cursor()
42    # create table one by one
43    ind = 1
44    for command in commands:
45        print('Execute command %d' % ind)
46        ind += 1
47        cur.execute(command)
48    if delete_duplicates_flag:
49        cur.execute(delete_duplicates)
50    # commit the changes
51    cur.close()
52    print('Added new table')
53    conn.commit()
54    # close communication with the PostgreSQL database server
55  
56def fill_stands(conn):
57    select_locals = """ SELECT name, location FROM taxi_stands """
58    select_freguesia = """ SELECT freguesia, concelho FROM cont_freg_v5
59    WHERE ST_Contains(ST_Transform(geom, 4326), '%s') LIMIT 1 """
60    insert_stand = """ INSERT INTO stand(name, lotations) VALUES('%s', %d) RETURNING stand_id """
61    insert_stand_geom = """ INSERT INTO stand_geom(name, lotations, concelho, freguesia, geom) VALUES('%s', %d, '%s', '%s', '%s') RETURNING stand_id """
62    
63    cur = conn.cursor()
64    cur.execute(select_locals)
65    local_rows = cur.fetchall()
66  
67    for row in local_rows:
68        nome = row[0]
69        cur.execute(insert_stand % (nome, 0))
70        stand_id = cur.fetchone()[0]
71        print(stand_id)
72        cur.execute(select_freguesia % row[1])
73        tup = cur.fetchone()
74        print(tup)
75        freg, concelho = tup
76        cur.execute(insert_stand_geom % (nome, 0, concelho, freg, row[1]))
77        #cur.execute(insert_local % (stand_id, freg, concelho))
78  
79    cur.close()
80    # commit the changes
81    print('Filled stand and location table')
82    conn.commit()
83  
84def insert_local(cur, pt):
85    local_geom = """ INSERT INTO local_geom(stand_id, freguesia, concelho, geom) VALUES(%d, '%s', '%s', '%s')"""
86    local = """ INSERT INTO local(stand_id, freguesia, concelho) VALUES(%d, '%s', '%s')"""
87    select_stand = """
88        SELECT stand_id, freguesia, concelho FROM stand_geom
89        WHERE ST_Distance(ST_Transform(geom, 4326), '%s') = (
90            SELECT MIN(ST_Distance(ST_Transform(geom, 4326), '%s')) FROM stand_geom
91        ) """
92    
93    cur.execute(select_stand % (pt,  pt))
94    stand, freg, concelho = cur.fetchone()
95    cur.execute(local_geom % (stand, freg, concelho, pt))
96    cur.execute(local % (stand, freg, concelho))
97  
98def insert_taxi(cur, tx):
99    insert_query = """ INSERT INTO taxi(license) VALUES (%d) """
100    
101    cur.execute(insert_query % tx)
102  
103def insert_tempo(cur, ts):
104    insert_query = """ INSERT INTO tempo(hora, dia, mes) VALUES (%d, %d, %d) """
105    
106    m = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%m'))
107    d = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%d'))
108    h = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%H'))
109    cur.execute(insert_query % (h, d, m))
110  
111def fill_services(conn):
112    drop_table = """ DROP TABLE IF EXISTS taxi_services_T, local, local_geom, taxi, tempo """
113    create_tables = ("""
114    CREATE TABLE local_geom (
115        local_id SERIAL,
116        stand_id INTEGER REFERENCES stand(stand_id),
117        freguesia VARCHAR,
118        concelho VARCHAR,
119        geom GEOMETRY,
120        PRIMARY KEY (local_id)
121        )
122        """,
123        """
124    CREATE TABLE local (
125        local_id SERIAL,
126        stand_id integer REFERENCES stand(stand_id),
127        freguesia VARCHAR,
128        concelho VARCHAR,
129        PRIMARY KEY (local_id)
130        )
131        """,
132        """
133    CREATE TABLE taxi (
134        taxi_id SERIAL NOT NULL,
135        license INTEGER,
136        PRIMARY KEY (taxi_id)
137        )
138        """,
139        """
140    CREATE TABLE tempo (
141        tempo_id SERIAL NOT NULL,
142        hora INTEGER,
143        dia INTEGER,
144        mes INTEGER,
145        PRIMARY KEY (tempo_id)
146        )
147        """,
148        """
149    CREATE TABLE taxi_services_T (
150        taxi_id INTEGER references taxi(taxi_id),
151        tempo_ini_id INTEGER references tempo(tempo_id),
152        tempo_fin_id INTEGER references tempo(tempo_id),
153        local_id_in INTEGER references local(local_id),
154        local_id_fim INTEGER references local(local_id),
155        num_viagens INTEGER,
156        tempo_total REAL,
157        CONSTRAINT taxi_key PRIMARY KEY(taxi_id,tempo_ini_id,tempo_fin_id,local_id_in,local_id_fim)
158        )
159        """
160    )
161    select_services = """ SELECT initial_ts, final_ts, taxi_id, initial_point, final_point FROM taxi_services  """
162    select_local = """ SELECT * FROM local_geom WHERE geom = '%s' """
163    select_taxi = """ SELECT * FROM taxi WHERE license = %d """
164    select_tempo = """ SELECT * FROM tempo WHERE hora = %d and dia = %d and mes = %d """
165    insert_service = """ INSERT INTO taxi_services_T(taxi_id, tempo_ini_id, tempo_fin_id, local_id_in, local_id_fim, num_viagens, tempo_total) VALUES(%d, %d, %d, %d, %d, %d, %d) """
166  
167    cur = conn.cursor()
168    cur.execute(drop_table)
169    for table in create_tables:
170        cur.execute(table)
171    cur.execute(select_services)
172    local_rows = cur.fetchall()
173    print("Created table and selected services")
174  
175    for row in local_rows:
176        i_ts, f_ts, t_id, i_pt, f_pt = row
177        tm = f_ts - i_ts
178  
179        cur.execute(select_local % i_pt)
180        ires = cur.fetchone()
181        if ires == None:
182            insert_local(cur, i_pt)
183            cur.execute(select_local % i_pt)
184            ires = cur.fetchone()
185  
186        cur.execute(select_local % f_pt)
187        fres = cur.fetchone()
188        if fres == None:
189            insert_local(cur, f_pt)
190            cur.execute(select_local % f_pt)
191            fres = cur.fetchone()
192  
193        cur.execute(select_taxi % t_id)
194        tres = cur.fetchone()
195        if tres == None:
196            insert_taxi(cur, t_id)
197            cur.execute(select_taxi % t_id)
198            tres = cur.fetchone()
199  
200        itm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(i_ts)).strftime('%H %d %m').split()])
201        cur.execute(select_tempo % itm_tup)
202        itmres = cur.fetchone()
203        if itmres == None:
204            insert_tempo(cur, i_ts)
205            cur.execute(select_tempo % itm_tup)
206            itmres = cur.fetchone()
207  
208        ftm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(f_ts)).strftime('%H %d %m').split()])
209        cur.execute(select_tempo % ftm_tup)
210        ftmres = cur.fetchone()
211        if ftmres == None:
212            insert_tempo(cur, f_ts)
213            cur.execute(select_tempo % ftm_tup)
214            ftmres = cur.fetchone()
215  
216        print("Inserting %d %d" % (ires[0], fres[0]))
217        cur.execute(insert_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0], 1, tm))
218  
219    cur.close()
220    conn.commit()
221  
222if __name__ == '__main__':
223    conn = None
224    try:
225        # connect to the PostgreSQL server
226        conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="")
227  
228        #create_tables(conn)
229        #fill_stands(conn)
230        fill_services(conn)
231    except (Exception, psycopg2.DatabaseError) as error:
232        print(error)
233    finally:
234        if conn is not None:
235            conn.close()