Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
pipe osmium-tools extracts into PostgreSQL
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 load_date date")
conn.commit()
cur.execute("ALTER TABLE public.multilinestrings ADD COLUMN load_date date")
conn.commit()
cur.execute("ALTER TABLE public.multipolygons ADD COLUMN load_date date")
conn.commit()
cur.execute("ALTER TABLE public.other_relations ADD COLUMN load_date date")
conn.commit()
cur.execute("ALTER TABLE public.points ADD COLUMN 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)
@locontem

This comment has been minimized.

Copy link

commented Jun 19, 2018

Forked your script and modified the Alter table statement, the script was failing because load_date already existed in the tables. Thanks for writing this!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.