KnBeyPxz

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