Skip to content

Instantly share code, notes, and snippets.

@frafra
Last active February 23, 2018 10:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save frafra/e8c74ab66cdb2ee361d6db9ad69448e5 to your computer and use it in GitHub Desktop.
Save frafra/e8c74ab66cdb2ee361d6db9ad69448e5 to your computer and use it in GitHub Desktop.
OSM node history (just a test)
#!/usr/bin/env python3
#
# OSM node history to SQLite
import osmium
import sqlite3
import sys
SCHEMA_NODES = """
CREATE TABLE nodes
(id INTEGER NOT NULL,
version INTEGER NOT NULL,
uid INTEGER,
date DATETIME,
visible INTEGER,
PRIMARY KEY (id, version))
"""
LOCATION = """
SELECT AddGeometryColumn('nodes', 'Geometry', 32632, 'POINT', 'XY')
"""
SCHEMA_TAGS = """
CREATE TABLE tags
(id INTEGER NOT NULL REFERENCES nodes(id),
version INTEGER NOT NULL REFERENCES nodes(version),
key TEXT,
value TEXT,
PRIMARY KEY (id, version, key))
"""
class Converter(osmium.SimpleHandler):
def __init__(self):
osmium.SimpleHandler.__init__(self)
self.prev = {}
def insert_tag(self, n, key, value):
conn.execute('INSERT INTO tags VALUES (?, ?, ?, ?)', (
n.id, n.version, key, value))
def node(self, n):
if len(n.tags) == 0: # or n.deleted:
return
coordinates = f'Transform(MakePoint({n.location.lon}, {n.location.lat}, 4326), 32632)'
conn.execute(f'INSERT INTO nodes VALUES (?, ?, ?, ?, ?, {coordinates})', (
n.id, n.version, n.uid, n.timestamp, n.visible))
if n.deleted:
return
tags = dict([[tag.k, tag.v] for tag in n.tags])
if n.version == 1:
self.prev = {}
for key in self.prev.keys():
if key not in tags:
tags[key] = None
for key, value in tags.items():
if key in self.prev:
if self.prev[key] != value:
conn.execute('INSERT INTO tags VALUES (?, ?, ?, ?)', (
n.id, n.version, key, value))
else:
conn.execute('INSERT INTO tags VALUES (?, ?, ?, ?)', (
n.id, n.version, key, value))
self.nodes = []
self.attrs = []
self.prev = tags
if __name__ == '__main__':
with sqlite3.connect(sys.argv[2]) as conn:
conn.enable_load_extension(True)
conn.load_extension('mod_spatialite')
conn.execute(SCHEMA_NODES)
conn.execute(SCHEMA_TAGS)
conn.execute('SELECT InitSpatialMetaData()')
conn.execute(LOCATION)
converter = Converter()
converter.apply_file(sys.argv[1])
conn.execute("SELECT CreateSpatialIndex('nodes', 'Geometry')")
conn.commit()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment