g2qQdY0B

1import psycopg2
2
3# Numero de viagens que saem do stand <stand>
4def numFromStand(conn, stand):
5    select_query = """
6        SELECT SUM(num_viagens) FROM taxi_services_T as t, local as l, stand as s
7        WHERE t.local_ini_id = l.local_id AND
8              l.stand_id = s.stand_id AND
9              s.name = '%s'
10    """
11
12    cur = conn.cursor()
13    cur.execute(select_query % stand)
14    res = cur.fetchone()
15
16    if res == None:
17        return 0
18    return int(res[0])
19
20# Histograma das horas
21def histHours(conn):
22    select_query = #"""
23#        SELECT t.hora, SUM(num_viagens) as nviagens FROM taxi_services_T, tempo
24#        WHERE t.tempo_id = tempo_id
25#        GROUP BY t.hora
26 #   """
27    """SELECT tmp, place, MAX(num_viagens) FROM(
28           SELECT tempo(dia, mes) as tmp, local(local_id) as place, num_viagens FROM taxi_services_T, tempo
29            WHERE local_id = local_id_in) AS aux
30GROUP BY tmp, place;"""
31
32    cur = conn.cursor()
33    cur.execute(select_query)
34    return cur.fetchall()
35
36# Cross-Tabulation: soma de viagens/tempo total por taxi a comecar em cada localizacao
37def maxMovement(conn):
38    select_query = """
39        SELECT taxi_id, 'ALL', 'ALL', local_id_in, 'ALL', SUM(num_viagens), SUM(tempo_total) FROM taxi_services_T, local as l
40        WHERE local_ini_id = l.local_id AND
41              l.freguesia = '%s'
42        GROUP BY taxi_id, local_id_in;
43    """
44
45if __name__ == '__main__':
46    conn = None
47    try:
48        # connect to the PostgreSQL server
49        conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="") 
50
51        
52    except (Exception, psycopg2.DatabaseError) as error:
53        print(error)
54    finally:
55        if conn is not None:
56            conn.close()
57 
58[up201207833@ssh trab1]$ ls
59tabdcon.py  tabdcon.py~  tabd_fill.py  tabd_queries.py  '#tabdt1.py#'  tabdt1.py~
60[up201207833@ssh trab1]$ cat tabd_fill.py 
61 
62import psycopg2, datetime
63#from config import config
64 
65def create_tables(conn):
66    commands = (
67        """
68        DROP TABLE IF EXISTS taxi_services_T, tempo, stand, stand_geom, taxi, local, local_geom 
69        """,
70        """
71    CREATE TABLE stand (
72        stand_id SERIAL NOT NULL,
73        name VARCHAR NOT NULL,
74        lotations INTEGER,
75        PRIMARY KEY (stand_id)
76        )
77        """,
78        """
79    CREATE TABLE stand_geom (
80        stand_id SERIAL NOT NULL,
81        name VARCHAR NOT NULL,
82        geom GEOMETRY,
83        freguesia VARCHAR,
84        concelho VARCHAR,
85        lotations INTEGER,
86        PRIMARY KEY (stand_id)
87        )
88        """
89    )
90
91    delete_duplicates_flag = False
92    delete_duplicates = """
93        DELETE FROM cont_freg_v5 AS t1
94            WHERE EXISTS (
95                SELECT 1 FROM cont_freg_v5 AS t2
96                      WHERE t2.freguesia = t1.freguesia and
97                            t2.concelho = t1.concelho and
98                            t2.geom = t1.geom and
99                            t2.gid > t1.gid
100                 ); """
101   
102    cur = conn.cursor()
103    # create table one by one
104    ind = 1
105    for command in commands:
106        print('Execute command %d' % ind)
107        ind += 1
108        cur.execute(command)
109    if delete_duplicates_flag:
110        cur.execute(delete_duplicates)
111    # commit the changes
112    cur.close()
113    print('Added new table')
114    conn.commit()
115    # close communication with the PostgreSQL database server
116
117def fill_stands(conn):
118    select_locals = """ SELECT name, location FROM taxi_stands """
119    select_freguesia = """ SELECT freguesia, concelho FROM cont_freg_v5
120    WHERE ST_Contains(ST_Transform(geom, 4326), '%s') LIMIT 1 """
121    insert_stand = """ INSERT INTO stand(name, lotations) VALUES('%s', %d) RETURNING stand_id """
122    insert_stand_geom = """ INSERT INTO stand_geom(name, lotations, concelho, freguesia, geom) VALUES('%s', %d, '%s', '%s', '%s') RETURNING stand_id """
123    
124    cur = conn.cursor()
125    cur.execute(select_locals)
126    local_rows = cur.fetchall()
127 
128    for row in local_rows:
129        nome = row[0]
130        cur.execute(insert_stand % (nome, 0))
131        stand_id = cur.fetchone()[0]
132        print(stand_id)
133        cur.execute(select_freguesia % row[1])
134        tup = cur.fetchone()
135        print(tup)
136        freg, concelho = tup
137        cur.execute(insert_stand_geom % (nome, 0, concelho, freg, row[1]))
138        #cur.execute(insert_local % (stand_id, freg, concelho))
139
140    cur.close()
141    # commit the changes
142    print('Filled stand and location table')
143    conn.commit()
144
145def insert_local(cur, pt):
146    local_geom = """ INSERT INTO local_geom(stand_id, freguesia, concelho, geom) VALUES(%d, '%s', '%s', '%s')"""
147    local = """ INSERT INTO local(stand_id, freguesia, concelho) VALUES(%d, '%s', '%s')"""
148    select_stand = """
149        SELECT stand_id, freguesia, concelho FROM stand_geom
150        WHERE ST_Distance(ST_Transform(geom, 4326), '%s') = (
151            SELECT MIN(ST_Distance(ST_Transform(geom, 4326), '%s')) FROM stand_geom
152        ) """
153    
154    cur.execute(select_stand % (pt,  pt))
155    stand, freg, concelho = cur.fetchone()
156    cur.execute(local_geom % (stand, freg, concelho, pt))
157    cur.execute(local % (stand, freg, concelho))
158
159def insert_taxi(cur, tx):
160    insert_query = """ INSERT INTO taxi(license) VALUES (%d) """
161    
162    cur.execute(insert_query % tx)
163
164def insert_tempo(cur, ts):
165    insert_query = """ INSERT INTO tempo(hora, dia, mes) VALUES (%d, %d, %d) """
166    
167    m = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%m'))
168    d = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%d'))
169    h = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%H'))
170    cur.execute(insert_query % (h, d, m))
171
172def fill_services(conn):
173    drop_table = """ DROP TABLE IF EXISTS taxi_services_T, local, local_geom, taxi, tempo """
174    create_tables = ("""
175    CREATE TABLE local_geom (
176        local_id SERIAL,
177        stand_id INTEGER REFERENCES stand(stand_id),
178        freguesia VARCHAR,
179        concelho VARCHAR,
180        geom GEOMETRY,
181        PRIMARY KEY (local_id)
182        )
183        """,
184        """
185    CREATE TABLE local (
186        local_id SERIAL, 
187        stand_id integer REFERENCES stand(stand_id), 
188        freguesia VARCHAR, 
189        concelho VARCHAR,
190        PRIMARY KEY (local_id)
191        )
192        """, 
193        """
194    CREATE TABLE taxi (
195        taxi_id SERIAL NOT NULL, 
196        license INTEGER,
197        PRIMARY KEY (taxi_id)
198        )
199        """,
200        """
201    CREATE TABLE tempo (
202        tempo_id SERIAL NOT NULL,
203        hora INTEGER,
204        dia INTEGER,
205        mes INTEGER,
206        PRIMARY KEY (tempo_id)
207        )
208        """,
209        """
210    CREATE TABLE taxi_services_T (
211        taxi_id INTEGER references taxi(taxi_id),
212        tempo_ini_id INTEGER references tempo(tempo_id),
213        tempo_fin_id INTEGER references tempo(tempo_id),
214        local_id_in INTEGER references local(local_id), 
215        local_id_fim INTEGER references local(local_id), 
216        num_viagens INTEGER, 
217        tempo_total REAL,
218        CONSTRAINT taxi_key PRIMARY KEY(taxi_id,tempo_ini_id,tempo_fin_id,local_id_in,local_id_fim)
219        )
220        """
221    )
222    select_services = """ SELECT initial_ts, final_ts, taxi_id, initial_point, final_point FROM taxi_services  """
223    select_local = """ SELECT * FROM local_geom WHERE geom = '%s' """
224    select_taxi = """ SELECT * FROM taxi WHERE license = %d """
225    select_tempo = """ SELECT * FROM tempo WHERE hora = %d and dia = %d and mes = %d """
226    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) """
227    select_service = """ SELECT num_viagens, tempo_total FROM taxi_services_T
228        WHERE taxi_id = %d and
229              tempo_ini_id = %d and
230              tempo_fin_id = %d and
231              local_id_in = %d and
232              local_id_fim = %d """
233    delete_service = """ DELETE FROM taxi_services_T
234        WHERE taxi_id = %d and
235              tempo_ini_id = %d and
236              tempo_fin_id = %d and
237              local_id_in = %d and
238              local_id_fim = %d """
239
240    cur = conn.cursor()
241    cur.execute(drop_table)
242    for table in create_tables:
243        cur.execute(table)
244    cur.execute(select_services)
245    local_rows = cur.fetchall()
246    print("Created table and selected services")
247
248    for row in local_rows:
249        i_ts, f_ts, t_id, i_pt, f_pt = row
250        tm = f_ts - i_ts
251        ntrips = 1
252
253        cur.execute(select_local % i_pt)
254        ires = cur.fetchone()
255        if ires == None:
256            insert_local(cur, i_pt)
257            cur.execute(select_local % i_pt)
258            ires = cur.fetchone()
259
260        cur.execute(select_local % f_pt)
261        fres = cur.fetchone()
262        if fres == None:
263            insert_local(cur, f_pt)
264            cur.execute(select_local % f_pt)
265            fres = cur.fetchone()
266
267        cur.execute(select_taxi % t_id)
268        tres = cur.fetchone()
269        if tres == None:
270            insert_taxi(cur, t_id)
271            cur.execute(select_taxi % t_id)
272            tres = cur.fetchone()
273
274        itm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(i_ts)).strftime('%H %d %m').split()])
275        cur.execute(select_tempo % itm_tup)
276        itmres = cur.fetchone()
277        if itmres == None:
278            insert_tempo(cur, i_ts)
279            cur.execute(select_tempo % itm_tup)
280            itmres = cur.fetchone()
281
282        ftm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(f_ts)).strftime('%H %d %m').split()])
283        cur.execute(select_tempo % ftm_tup)
284        ftmres = cur.fetchone()
285        if ftmres == None:
286            insert_tempo(cur, f_ts)
287            cur.execute(select_tempo % ftm_tup)
288            ftmres = cur.fetchone()
289
290        cur.execute(select_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0]))
291        serres = cur.fetchone()
292        if serres != None:
293            cur.execute(delete_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0]))
294            ntrips += int(serres[0])
295            tm += int(serres[1])
296
297        print("Inserting %d %d" % (ires[0], fres[0]))
298        cur.execute(insert_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0], ntrips, tm))
299
300    cur.close()
301    conn.commit()
302 
303if __name__ == '__main__':
304    conn = None
305    try:
306        # connect to the PostgreSQL server
307        conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="") 
308
309        #create_tables(conn)
310        #fill_stands(conn)
311        fill_services(conn)
312    except (Exception, psycopg2.DatabaseError) as error:
313        print(error)
314    finally:
315        if conn is not None:
316            conn.close()