Skip to content

Instantly share code, notes, and snippets.

@HeikkiVesanto
Last active February 26, 2024 15:31
Show Gist options
  • Save HeikkiVesanto/f01ea54cca499a6a144d18cf8909c940 to your computer and use it in GitHub Desktop.
Save HeikkiVesanto/f01ea54cca499a6a144d18cf8909c940 to your computer and use it in GitHub Desktop.
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, '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
Copy link

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