Last active
February 23, 2018 10:19
-
-
Save frafra/e8c74ab66cdb2ee361d6db9ad69448e5 to your computer and use it in GitHub Desktop.
OSM node history (just a test)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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