Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import psycopg2, datetime
- #from config import config
- def create_tables(conn):
- commands = (
- """
- DROP TABLE IF EXISTS taxi_services_T, tempo, stand, stand_geom, taxi, local, local_geom
- """,
- """
- CREATE TABLE stand (
- stand_id SERIAL NOT NULL,
- name VARCHAR NOT NULL,
- lotations INTEGER,
- PRIMARY KEY (stand_id)
- )
- """,
- """
- CREATE TABLE stand_geom (
- stand_id SERIAL NOT NULL,
- name VARCHAR NOT NULL,
- geom GEOMETRY,
- freguesia VARCHAR,
- concelho VARCHAR,
- lotations INTEGER,
- PRIMARY KEY (stand_id)
- )
- """
- )
- delete_duplicates_flag = False
- delete_duplicates = """
- DELETE FROM cont_freg_v5 AS t1
- WHERE EXISTS (
- SELECT 1 FROM cont_freg_v5 AS t2
- WHERE t2.freguesia = t1.freguesia and
- t2.concelho = t1.concelho and
- t2.geom = t1.geom and
- t2.gid > t1.gid
- ); """
- cur = conn.cursor()
- # create table one by one
- ind = 1
- for command in commands:
- print('Execute command %d' % ind)
- ind += 1
- cur.execute(command)
- if delete_duplicates_flag:
- cur.execute(delete_duplicates)
- # commit the changes
- cur.close()
- print('Added new table')
- conn.commit()
- # close communication with the PostgreSQL database server
- def fill_stands(conn):
- select_locals = """ SELECT name, location FROM taxi_stands """
- select_freguesia = """ SELECT freguesia, concelho FROM cont_freg_v5
- WHERE ST_Contains(ST_Transform(geom, 4326), '%s') LIMIT 1 """
- insert_stand = """ INSERT INTO stand(name, lotations) VALUES('%s', %d) RETURNING stand_id """
- insert_stand_geom = """ INSERT INTO stand_geom(name, lotations, concelho, freguesia, geom) VALUES('%s', %d, '%s', '%s', '%s') RETURNING stand_id """
- cur = conn.cursor()
- cur.execute(select_locals)
- local_rows = cur.fetchall()
- for row in local_rows:
- nome = row[0]
- cur.execute(insert_stand % (nome, 0))
- stand_id = cur.fetchone()[0]
- print(stand_id)
- cur.execute(select_freguesia % row[1])
- tup = cur.fetchone()
- print(tup)
- freg, concelho = tup
- cur.execute(insert_stand_geom % (nome, 0, concelho, freg, row[1]))
- #cur.execute(insert_local % (stand_id, freg, concelho))
- cur.close()
- # commit the changes
- print('Filled stand and location table')
- conn.commit()
- def insert_local(cur, pt):
- local_geom = """ INSERT INTO local_geom(stand_id, freguesia, concelho, geom) VALUES(%d, '%s', '%s', '%s')"""
- local = """ INSERT INTO local(stand_id, freguesia, concelho) VALUES(%d, '%s', '%s')"""
- select_stand = """
- SELECT stand_id, freguesia, concelho FROM stand_geom
- WHERE ST_Distance(ST_Transform(geom, 4326), '%s') = (
- SELECT MIN(ST_Distance(ST_Transform(geom, 4326), '%s')) FROM stand_geom
- ) """
- cur.execute(select_stand % (pt, pt))
- stand, freg, concelho = cur.fetchone()
- cur.execute(local_geom % (stand, freg, concelho, pt))
- cur.execute(local % (stand, freg, concelho))
- def insert_taxi(cur, tx):
- insert_query = """ INSERT INTO taxi(license) VALUES (%d) """
- cur.execute(insert_query % tx)
- def insert_tempo(cur, ts):
- insert_query = """ INSERT INTO tempo(hora, dia, mes) VALUES (%d, %d, %d) """
- m = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%m'))
- d = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%d'))
- h = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%H'))
- cur.execute(insert_query % (h, d, m))
- def fill_services(conn):
- drop_table = """ DROP TABLE IF EXISTS taxi_services_T, local, local_geom, taxi, tempo """
- create_tables = ("""
- CREATE TABLE local_geom (
- local_id SERIAL,
- stand_id INTEGER REFERENCES stand(stand_id),
- freguesia VARCHAR,
- concelho VARCHAR,
- geom GEOMETRY,
- PRIMARY KEY (local_id)
- )
- """,
- """
- CREATE TABLE local (
- local_id SERIAL,
- stand_id integer REFERENCES stand(stand_id),
- freguesia VARCHAR,
- concelho VARCHAR,
- PRIMARY KEY (local_id)
- )
- """,
- """
- CREATE TABLE taxi (
- taxi_id SERIAL NOT NULL,
- license INTEGER,
- PRIMARY KEY (taxi_id)
- )
- """,
- """
- CREATE TABLE tempo (
- tempo_id SERIAL NOT NULL,
- hora INTEGER,
- dia INTEGER,
- mes INTEGER,
- PRIMARY KEY (tempo_id)
- )
- """,
- """
- CREATE TABLE taxi_services_T (
- taxi_id INTEGER references taxi(taxi_id),
- tempo_ini_id INTEGER references tempo(tempo_id),
- tempo_fin_id INTEGER references tempo(tempo_id),
- local_id_in INTEGER references local(local_id),
- local_id_fim INTEGER references local(local_id),
- num_viagens INTEGER,
- tempo_total REAL,
- CONSTRAINT taxi_key PRIMARY KEY(taxi_id,tempo_ini_id,tempo_fin_id,local_id_in,local_id_fim)
- )
- """
- )
- select_services = """ SELECT initial_ts, final_ts, taxi_id, initial_point, final_point FROM taxi_services """
- select_local = """ SELECT * FROM local_geom WHERE geom = '%s' """
- select_taxi = """ SELECT * FROM taxi WHERE license = %d """
- select_tempo = """ SELECT * FROM tempo WHERE hora = %d and dia = %d and mes = %d """
- 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) """
- cur = conn.cursor()
- cur.execute(drop_table)
- for table in create_tables:
- cur.execute(table)
- cur.execute(select_services)
- local_rows = cur.fetchall()
- print("Created table and selected services")
- for row in local_rows:
- i_ts, f_ts, t_id, i_pt, f_pt = row
- tm = f_ts - i_ts
- cur.execute(select_local % i_pt)
- ires = cur.fetchone()
- if ires == None:
- insert_local(cur, i_pt)
- cur.execute(select_local % i_pt)
- ires = cur.fetchone()
- cur.execute(select_local % f_pt)
- fres = cur.fetchone()
- if fres == None:
- insert_local(cur, f_pt)
- cur.execute(select_local % f_pt)
- fres = cur.fetchone()
- cur.execute(select_taxi % t_id)
- tres = cur.fetchone()
- if tres == None:
- insert_taxi(cur, t_id)
- cur.execute(select_taxi % t_id)
- tres = cur.fetchone()
- itm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(i_ts)).strftime('%H %d %m').split()])
- cur.execute(select_tempo % itm_tup)
- itmres = cur.fetchone()
- if itmres == None:
- insert_tempo(cur, i_ts)
- cur.execute(select_tempo % itm_tup)
- itmres = cur.fetchone()
- ftm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(f_ts)).strftime('%H %d %m').split()])
- cur.execute(select_tempo % ftm_tup)
- ftmres = cur.fetchone()
- if ftmres == None:
- insert_tempo(cur, f_ts)
- cur.execute(select_tempo % ftm_tup)
- ftmres = cur.fetchone()
- print("Inserting %d %d" % (ires[0], fres[0]))
- cur.execute(insert_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0], 1, tm))
- cur.close()
- conn.commit()
- if __name__ == '__main__':
- conn = None
- try:
- # connect to the PostgreSQL server
- conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="")
- #create_tables(conn)
- #fill_stands(conn)
- fill_services(conn)
- except (Exception, psycopg2.DatabaseError) as error:
- print(error)
- finally:
- if conn is not None:
- conn.close()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement