Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import os
- import psycopg2
- import subprocess
- def runogr2ogr(infile):
- # import the data
- try:
- command = ["ogr2ogr", "-f", "PostgreSQL",
- OGR_CONN_STRING,
- infile]
- print command
- subprocess.check_call(command)
- # record an error if there is one
- except subprocess.CalledProcessError as e:
- print(str(e.output))
- def runosmium(infile, outfile, month, year):
- month_run = month + 1
- if month_run == 13:
- month_run = 1
- year_run = year + 1
- else:
- year_run = year
- month_run = str(month_run).zfill(2)
- year_run = str(year_run).zfill(4)
- try:
- command = ["osmium", "time-filter", infile,
- "{}-{}-01T00:00:00Z".format(year_run, month_run), "-o", outfile]
- print command
- subprocess.check_call(command)
- except subprocess.CalledProcessError as e:
- print(str(e.output))
- def checkTableExits(schema, table, connection):
- m_cur = connection.cursor()
- m_cur.execute(
- "select exists (select * from information_schema.tables where "
- "table_schema='{}' AND table_name='{}')".format(
- schema, table
- ))
- if m_cur.fetchone()[0]:
- ret = True
- else:
- ret = False
- m_cur.close()
- return ret
- # Dates
- START_YEAR = 2006
- START_MONTH = 12
- END_YEAR = 2018
- END_MONTH = 4
- ROOT_DIR = os.path.dirname(os.path.abspath(__file__))
- INFILE = "ireland-and-northern-ireland.osh.pbf"
- CONN_STRING = "dbname=%s user=%s password=%s host=%s port=%s" % (
- "postgis", "postgres", "postgres", "localhost", "5432")
- OGR_CONN_STRING = "PG:host=localhost user=postgres password=postgres dbname=postgis port=5432"
- conn = psycopg2.connect(CONN_STRING)
- SCHEMA = "public"
- for y in range(START_YEAR, END_YEAR + 1):
- if y == START_YEAR and END_YEAR > START_YEAR:
- t_end_month = 12
- elif y > START_YEAR and y < END_YEAR:
- t_end_month = 12
- else:
- t_end_month = END_MONTH
- if y > START_YEAR:
- t_start_month = 01
- else:
- t_start_month = START_MONTH
- for m in range(t_start_month, t_end_month + 1):
- print(y, m)
- file_name_pbf = str(y) + str(m).zfill(2) + '.pbf'
- out_file_pbf = os.path.join(ROOT_DIR, file_name_pbf)
- runosmium(INFILE, out_file_pbf, m, y)
- if checkTableExits(SCHEMA, 'public.lines', conn) is True:
- runogr2ogr(out_file_pbf)
- else:
- runogr2ogr(out_file_pbf)
- cur = conn.cursor()
- cur.execute("ALTER TABLE public.lines ADD COLUMN IF NOT EXISTS load_date date")
- conn.commit()
- cur.execute("ALTER TABLE public.multilinestrings ADD COLUMN IF NOT EXISTS load_date date")
- conn.commit()
- cur.execute("ALTER TABLE public.multipolygons ADD COLUMN IF NOT EXISTS load_date date")
- conn.commit()
- cur.execute("ALTER TABLE public.other_relations ADD COLUMN IF NOT EXISTS load_date date")
- conn.commit()
- cur.execute("ALTER TABLE public.points ADD COLUMN IF NOT EXISTS load_date date")
- conn.commit()
- cur.close()
- cur = conn.cursor()
- cur.execute(
- "update public.lines set load_date = '{}-{}-01' where load_date is NULL;".format(
- str(y), str(m).zfill(2)))
- conn.commit()
- cur.execute(
- "update public.multilinestrings set load_date = '{}-{}-01' where load_date is NULL;".format(
- str(y), str(m).zfill(2)))
- conn.commit()
- cur.execute(
- "update public.multipolygons set load_date = '{}-{}-01' where load_date is NULL;".format(
- str(y), str(m).zfill(2)))
- conn.commit()
- cur.execute(
- "update public.other_relations set load_date = '{}-{}-01' where load_date is NULL;".format(
- str(y), str(m).zfill(2)))
- conn.commit()
- cur.execute(
- "update public.points set load_date = '{}-{}-01' where load_date is NULL;".format(
- str(y), str(m).zfill(2)))
- conn.commit()
- cur.close()
- os.remove(out_file_pbf)
Add Comment
Please, Sign In to add comment