[PYTHON] Versuchen Sie es mit Spatia Lite, das räumliche Informationen wie Karten in SQLite speichert

Überblick

Spatia Lite ist eine Erweiterung von SQLite, die räumliche Informationen wie Karten speichern kann. In Shp-Dateien und GeoJson geschriebene Geometrieinformationen können gespeichert und in der Datenbank verwendet werden.

http://www.gaia-gis.it/gaia-sins/index.html

Beschreibung der Verteilungsdatei

SpatiaLite Sie können die Datenbank über die Befehlszeile bedienen.

** Binäre Verteilung ** Unter Windows können Sie die Binärdatei jeder Plattform wie folgt abrufen. http://www.gaia-gis.it/gaia-sins/index.html

mod_spatialite.dll/mod_spatialite.so Eine Erweiterung von SQLite. Unter SQLite können Sie SpatiaLite-Funktionen verwenden, indem Sie eine DLL-Datei oder so mit load_extension laden.

** Binäre Verteilung ** Unter Windows können Sie die Binärdatei jeder Plattform wie folgt abrufen. http://www.gaia-gis.it/gaia-sins/index.html

** Verteilung des Quellcodes ** Sie können es erstellen, indem Sie den libspatialite-Quellcode wie folgt herunterladen und erstellen. https://www.gaia-gis.it/fossil/libspatialite/index

spatialite_gui Sie können die Spatialite-Funktion in der GUI verwenden.

Für Spalten vom Typ Geometrie wie POLYGON können Sie den Inhalt als Bild überprüfen.

** Binäre Verteilung ** Unter Windows können Sie die Binärdatei jeder Plattform wie folgt abrufen. http://www.gaia-gis.it/gaia-sins/index.html

Erstellen Sie libspatialite

Wenn Sie mod_spatialite nicht binär erhalten können, müssen Sie libspatialite erstellen.

Die folgenden Bibliotheken sind erforderlich, um libspatialite-4.2.0 zu erstellen. proj-4.8.0 proj.4 ist eine Mapping-Projektionsbibliothek. http://trac.osgeo.org/proj/

geos-3.4.2 Geometry Engin ist eine Portierung der Java Topology Suite auf C ++, ein räumliches Prädikat und eine Feature-API für die Verarbeitung von Geometrie. http://trac.osgeo.org/geos/

Führen Sie nach der Installation des obigen Befehls den folgenden Befehl in dem Ordner aus, der die libsatialite-Quelle enthält.

./configure --disable-freexl
make
make install

--disable-free xl deaktiviert die Funktion zum Verknüpfen mit Excel. Wenn Sie dies aktivieren, benötigen Sie freexl. https://www.gaia-gis.it/fossil/freexl/index

Wenn Sie einen Fehler bei FreeBSD erhalten

Bei FreeBSD wird möglicherweise der folgende Fehler angezeigt

/usr/bin/ld: cannot find -ldl

In FreeBSD ist dlopen eine Standardbibliothek, daher wird -ldl nicht benötigt. Suchen Sie in src / Makefile nach -ldl, löschen Sie es und versuchen Sie es erneut.

Referenz: http://sourceforge.net/p/idjc/discussion/458834/thread/246f0841/

Verwenden Sie mod_spatialite von SQLite.

Wie bereits erwähnt, ist Spatialite mit load_extension verfügbar, es gibt jedoch einige Einschränkungen.

-Die SQLite-Version muss 3.7.17 oder höher sein.

· Alle abhängigen DLLs müssen sich an einem für SQLite sichtbaren Speicherort befinden. Geben Sie den Ordner mit der zugehörigen DLL mit der Umgebungsvariablen PATH an.

-Die SQLite- und mod_spatialite-Prozesse müssen mit denselben Bits erstellt werden. Wenn SQLite auf 32 Bit ausgeführt wird, muss mod_spatialite auf 32 Bit erstellt werden, und wenn SQLite auf 64 Bit ausgeführt wird, muss mod_spatialite auf 64 Bit erstellt werden. Wenn Sie unter Windows 64-Bit-Binär-SQLite benötigen, können Sie es anhand des folgenden Artikels erstellen. http://qiita.com/akaneko3/items/0e99c3c1366dfbad006f

• Durch Laden der DLL-Datei oder so mit load_extension kann die Spatialite-Funktion danach verwendet werden.

SELECT load_extension('/usr/local/lib/mod_spatialite.so');

-Wenn die Datenbank aus SQLite3 erstellt wird, werden die von SpatiaLite verwalteten Metadaten nicht erstellt. Führen Sie die folgende SQL aus, um diese Metadaten zu erstellen.

SELECT InitSpatialMetaData();

Lernprogramm

Hier werden wir den Verwaltungsbereich der nationalen Landnummerninformationen tatsächlich in Spatialite speichern und verwenden.

Erwerben Sie landesweit Verwaltungsgebiete mit nationalen Landnummerninformationen.

Laden Sie zunächst die Verwaltungsbereiche landesweit von der folgenden Seite herunter. http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03.html

Erstellen Sie eine leere SQLite-Datei.

Starten Sie Spatialite_gui und erstellen Sie eine leere SQLite-Datenbankdatei.

Klicken Sie auf das Symbol Neue (leere) SQLite-Datenbank erstellen und geben Sie einen Dateinamen ein.

Bei erfolgreicher Erstellung wird der Pfad zur erstellten Datenbank angezeigt.

Bis zu diesem Punkt unterscheidet es sich nicht von einer normalen Datenbank.

SHP-Datei importieren

Importieren Sie die shp-Datei mit den nationalen Landnummerninformationen in die Datenbank.

Drücken Sie auf das Symbol [Formdatei laden] und wählen Sie "N03-14_140401.shp" in den nationalen Landnummerninformationen aus.

Zu diesem Zeitpunkt werden Sie nach der Codierung gefragt. Wählen Sie daher "CP932".

Nach erfolgreichem Abschluss wird eine Tabelle mit dem Namen "N03-14_140401" erstellt.

Überprüfen Sie die aus der shp-Datei erstellte Tabelle

Hier überprüfen wir die aus der shp-Datei erstellte Tabelle. Um die Spalten in der erstellten Tabelle anzuzeigen, wählen Sie die Tabelle aus, klicken Sie mit der rechten Maustaste und wählen Sie Spalten anzeigen.

N03_001 bis N03_004 sind Präfekturen, Bezirke, Städte, und N03_007 ist eine Spalte vom Typ TEXT, in der der Code gespeichert ist. Geometrie speichert die Form des Verwaltungsbereichs als Blob und ist der von Spatia Lite angegebene Typ namens POLYGON.

Sie können die tatsächliche SQL-Anweisung anzeigen, die zum Erstellen dieser Tabelle verwendet wurde, indem Sie mit der rechten Maustaste auf die Tabelle klicken und Show CREATE-Anweisung auswählen.

CREATE TABLE "N03-14_140401" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"N03_001" TEXT,
"N03_002" TEXT,
"N03_003" TEXT,
"N03_004" TEXT,
"N03_007" TEXT, 
"Geometry" POLYGON)

Schauen wir uns den Inhalt der Tabelle an. Führen Sie dazu eine SQL-Anweisung aus.

SELECT * FROM "N03-14_140401" LIMIT 10;

Bei der Ausführung können die folgenden Informationen erhalten werden.

Die Daten werden im Text von PK_UID bis N03_007 ausgedrückt. Da es sich bei Geometrie jedoch um BLOB-Daten handelt, können Benutzer den Inhalt nicht sehen.

Es gibt zwei Möglichkeiten, diesen Inhalt zu überprüfen. Eine besteht darin, den BLOB-Explorer zu verwenden. Wählen Sie eine beliebige Zelle in der Spalte Geometrie aus, klicken Sie mit der rechten Maustaste und wählen Sie "BLOB-Erkundung".

BLOB Explore kann Daten in verschiedenen Formaten wie Binär, Bild, SVG, GeoJSON usw. darstellen.

Eine Möglichkeit, die Verwendung von BLOB Explore zu vermeiden, besteht darin, mithilfe von SQL Geometriespalten in einem beliebigen Format anzuzeigen.

Im folgenden Beispiel wird die Spalte Geometrie im Textformat angezeigt.

--Da die Daten lang sind, werden bis zu den ersten 100 Zeichen angezeigt
SELECT substr(ASTEXT(Geometry), 1, 100) FROM "N03-14_140401" LIMIT 10;

Ich konnte POLYGON-Informationen als Zeichen in ASTEXT anzeigen. Es kann in jedem Format ausgegeben werden. Wenn es sich beispielsweise um ASGEOJSON handelt, wird es als GeoJSON ausgegeben.

SELECT substr(ASGEOJSON(Geometry), 1, 100) FROM "N03-14_140401" LIMIT 10;

Die von Spatia Lite von ASTEXT und ASGEOJSON bereitgestellten Funktionen werden im folgenden "Referenzhandbuch für räumliche SQL-Funktionen" beschrieben.

http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

Versuchen Sie, selbst eine Tabelle mit Koordinateninformationen zu erstellen

Im vorherigen Beispiel habe ich eine Tabelle aus der shp-Datei erstellt, aber dieses Mal werde ich selbst eine Tabelle mit Koordinateninformationen erstellen.

Erstellen Sie in der folgenden Tabelle eine Tabelle mit Koordinaten von Feature-Informationen, z. B. Tokyo Tower.

Erstellen Sie zunächst nur Spalten, die keine Feature-Informationen enthalten.

CREATE TABLE "places" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT)

Nach dem Ausführen von SQL wird durch Aktualisieren die Tabelle hinzugefügt.

Erstellen Sie als Nächstes mit AddGeometryColumn () eine Spalte mit Feature-Informationen.

Select AddGeometryColumn ('places', 'Geometry', 0, 'POINT', 'XY')

Nach dem Ausführen von SQL wird durch Aktualisieren eine Spalte hinzugefügt und der Tabelle ein Globussymbol hinzugefügt.

Nach dem Erstellen der Tabelle ist es Zeit, die Daten einzugeben. Im folgenden Beispiel werden die Koordinaten des Metropolitan Office in der Tabelle gespeichert.

INSERT INTO places (name, Geometry) VALUES(
  'Regierungsbüro der Metropole',
  GeomFromText('POINT(139.692101 35.689634 )')
)

GeomFromText ist eine Funktion, die eine Zeichenfolge in Geometrie ändert. Dieses Mal habe ich aus dem Textformat konvertiert, aber Sie können es aus GeoJSON oder Kml mit GeomFromGeoJSON, GeomFromKml usw. erstellen.

Wenn Sie alles auf einmal erstellen, wie unten gezeigt, funktioniert es auf den ersten Blick so, aber beim Erstellen eines RTree-Index funktioniert es nicht richtig, da die Metadaten nicht damit verknüpft sind.

CREATE TABLE "places" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT,
"Geometry" POINT)

Suchen Sie nach Koordinateninformationen

Hier suchen wir nach Koordinateninformationen. Versuchen Sie herauszufinden, welche Verwaltungsabteilung die in der Ortsübersicht angegebenen Koordinaten enthält.

SELECT
  name, "N03-14_140401".*
FROM
 "N03-14_140401"
INNER JOIN places ON
 Contains("N03-14_140401".Geometry, places.Geometry)

Die Funktion Enthält prüft, ob der Bereich des ersten Arguments den Bereich des zweiten Arguments enthält. Das Ergebnis ist wie folgt:

Da sich das Büro der Metropole in der Gemeinde Shinjuku befindet, kann gesagt werden, dass das Ergebnis wie erwartet ist. Im vorherigen Beispiel haben wir nach den in der Tabelle gespeicherten Daten gesucht, aber Sie können sie auch direkt in der WHERE-Station angeben.

SELECT
  *
FROM
 "N03-14_140401"
WHERE
  Contains("N03-14_140401".Geometry,GeomFromText('POINT(139.692101 35.689634 )'))

MBR und grobe Suche

Die vorherige Suche war eine genaue Suche für jede Koordinate, aber es gibt Zeiten, in denen Sie schnell eine grobe Position erhalten möchten.

Verwenden Sie in diesem Fall Minimum Bounding Rectangle --MBR. MBR repräsentiert die ungefähre Größe der GEOMETRIE.

Betrachten Sie die folgenden komplexen Formen als rote Randformen.

Dies ermöglicht eine ungenaue, aber schnelle Suche. Verwenden Sie Envelope (), um den MBR von GEOMETRY abzurufen.

SELECT
   ASTEXT(Envelope(Geometry))
FROM
 "N03-14_140401"
LIMIT 10

Das Ergebnis ist eine einfache Zahl wie folgt:

Suchen wir nun nach Koordinaten, die dasselbe Büro in der Metropole wie vor der Verwendung von MBR enthalten.

SELECT
  *
FROM
 "N03-14_140401"
WHERE
  MBRContains("N03-14_140401".Geometry,GeomFromText('POINT(139.692101 35.689634 )'))

Anstatt die Suchgeschwindigkeit zu verbessern, wurden auch unnötige Daten extrahiert, wie unten gezeigt.

Suche mit dem RTree-Index

Normalerweise wird beim Umgang mit DB die Suchgeschwindigkeit durch Verwendung des Index verbessert. Leider kann der Geometrie kein Index zugewiesen werden, der Buchstaben und Zahlen zugewiesen werden kann. Spatia Lite nutzt jedoch SQLites RTree, um RTree-Indizes zu unterstützen. Informationen zum RTree-Indexalgorithmus finden Sie im Folgenden.

Wonderful R*Tree Spatial Index http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/rtree.html

Als Übersicht über RTree wird Geometrie mithilfe von MBR in ein Rechteck umgewandelt und ein Baum entsprechend dem Schnittpunkt der Rechtecke erstellt, um die Suche zu vereinfachen. Es wird als RTree-Index bezeichnet, da ein Baum mithilfe eines Rechtecks erstellt wird.

Erstellen und Verwenden von RTree-Indizes

Verwenden wir nun den RTree-Index. Verwenden Sie die Funktion CreateSpatialIndex, um einen RTree-Index zu erstellen. Geben Sie den Tabellennamen im ersten Argument und den Spaltennamen im zweiten Argument an.

SELECT CreateSpatialIndex("N03-14_140401", "Geometry") ;

Wenn es erfolgreich erstellt wurde, gibt es 1 zurück und Sie können sehen, dass der SpatialIndex die neue Tabelle nach dem Aktualisieren anzeigt.

Durch Erstellen des RTree-Index werden die folgenden vier Tabellen erstellt.

・ Idx_N03-14_140401_Geometrie ・ Idx_N03-14_140401_Geometry_node ・ Idx_N03-14_140401_Geometry_parent ・ Idx_N03-14_140401_Geometry_rowid

Mit Ausnahme von idx_N03-14_140401_Geometry handelt es sich um eine intern verwendete Tabelle, und wir erwarten nicht, dass der Benutzer sie direkt bedient.

idx_N03-14_140401_Geometry ist eine VIRTUELLE TABELLE, die tatsächlich eine interne Tabelle verwendet, um das Ergebnis zurückzugeben.

Lassen Sie uns nun den Inhalt von idx_N03-14_140401_Geometry überprüfen.

SELECT * FROM "idx_N03-14_140401_Geometry" LIMIT 10;

Die MRB jedes Datensatzes wird auf diese Weise gespeichert.

Mithilfe dieser Tabelle können Benutzer Daten aus einer großen Datenmenge filtern und das Ergebnis erhalten.

SELECT 
  * 
FROM "N03-14_140401"
WHERE ROWID IN(
  SELECT
    pkid
  FROM
   "idx_N03-14_140401_Geometry"
  WHERE
    MBRContains(
      BuildMBR(xmin,ymin,xmax,ymax),
      GeomFromText('POINT(139.692101 35.689634 )')
  )
) AND Contains(Geometry, 
  GeomFromText('POINT(139.692101 35.689634 )')
)

Ich glaube, ich konnte schneller als bei einer normalen Suche genaue Daten abrufen. Je größer die Anzahl der Daten ist, desto größer ist der Unterschied.

Auf diese Weise wird der RTree-Index im Gegensatz zu einem normalen Index nicht nur durch Ausführen von SQL für die reale Tabelle angewendet, sondern hat die Aufgabe, Daten mithilfe der erstellten VIRTUAL TABLE nach Unterabfrage oder JOIN zu filtern.

Wenn Sie den Index in einer separaten Tabelle verwalten, fragen Sie sich möglicherweise, ob bei der Aktualisierung der realen Tabelle möglicherweise eine Diskrepanz besteht. Da der Trigger jedoch bei der Ausführung von CreateSpatialIndex erstellt wurde, kann der Benutzer den Index und die Tabelle ohne Änderungen synchronisieren.

Führen Sie die folgende SQL aus, um diesen Trigger anzuzeigen:

select name  from sqlite_master where type = 'trigger' and tbl_name='N03-14_140401';

Dieses Mal können Sie sehen, dass die folgenden Trigger automatisch erstellt werden.

・ Ggi_N03-14_140401_Geometrie ・ Ggu_N03-14_140401_Geometrie ・ Gii_N03-14_140401_Geometrie ・ Giu_N03-14_140401_Geometrie ・ Gid_N03-14_140401_Geometry

RTree-Index löschen

Verwenden Sie DisableSpatialIndex, um den von Ihnen erstellten RTree-Index zu löschen.

SELECT DisableSpatialIndex("N03-14_140401", "Geometry") ;

Sie können sehen, dass der räumliche Index durch Ausführen der Aktualisierung gelöscht wurde.

Verwenden Sie aus Python

Wenn Sie SpatiaLite aus Python verwenden, ist es einfach zu verwenden, wenn SQLite ordnungsgemäß verwendet wird.

Gehen Sie folgendermaßen vor, um herauszufinden, welche Version von Sqlite3 Python verwendet wird:

import sqlite3
print (sqlite3.sqlite_version_info)

Wenn es nicht der Version entspricht, aktualisieren Sie bitte SQLITE. Schreiben Sie unter Windows die DLL in den folgenden Ordner.

C:\Python27\DLLs\sqlite3.dll

Unten ist der Code in Python.

#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3
import os

# mod_Fügen Sie den Ordner mit Spatialite zu Ihrem PFAD hinzu
os.environ["PATH"] = os.environ["PATH"] + ';C:\\tool\\spatialite\\mod_spatialite-4.2.0-win-x86'
cnn = sqlite3.connect('database/gyouseikuiki.sqlite')

# mod_Laden von Spatialit
cnn.enable_load_extension(True)
cnn.execute("SELECT load_extension('./mod_spatialite-4.2.0-win-x86/mod_spatialite.dll');")
sql = """
SELECT
  N03_001,
  N03_002,
  N03_003,
  N03_004,
  N03_007, 
  AsGeoJson(Geometry)
FROM
 "N03-14_140401"
WHERE
  MBRContains("N03-14_140401".Geometry,GeomFromText('POINT(139.692101 35.689634 )'))
"""

ret = cnn.execute(sql)
for r in ret:
  print('----------------------------')
  print(r[0].encode('utf_8') )
  print(r[1].encode('utf_8') )
  print(r[2].encode('utf_8') )
  print(r[3].encode('utf_8') )
  print(r[4].encode('utf_8') )
  print(r[5].encode('utf_8') )

Übergeben Sie die Umgebungsvariable über den Pfad von mod_spatialite, erlauben Sie die Verwendung der Erweiterungs-DLL mit enable_load_extension und laden Sie dann mod_spatialite.

Danach können Sie SQL wie gewohnt ausführen.

Wenn bei enable_load_extension ein Fehler auftritt

Der folgende Fehler kann bei Verwendung von enable_load_extension auftreten.

"'sqlite3.Connection' object has no attribute 'enable_load_extension'"

Der Grund dafür ist, dass Python, das auf einigen Betriebssystemen wie MaxOS und Debian vorinstalliert ist, die Ladefunktion der Erweiterungsbibliothek beim Kompilieren deaktiviert.

https://docs.python.org/2/library/sqlite3.html#f1

Die einzige Möglichkeit, damit umzugehen, besteht darin, Python neu zu installieren. Während der Installation müssen Sie setup.py im Python-Quellcodeordner manuell ändern.

Suchen Sie nach SQLITE_OMIT_LOAD_EXTENSION, kommentieren Sie diese Zeile aus und führen Sie ./configure, make, make install aus.

Wenn bei make install ein Fehler auftritt, versuchen Sie "make -i alt install", wenn bei make install ein Fehler auftritt.

http://python.g.hatena.ne.jp/nelnal_programing/20101026/1288084718

Wie benutzt man Peewee?

Im Folgenden erfahren Sie, wie Sie Spatialite mit der Peewee-Bibliothek verwenden, bei der es sich um ein ORM handelt.

http://qiita.com/mima_ita/items/9d4e1d0afac1865acdbb#spatialitesql%E3%81%B8%E3%81%AE%E6%8E%A5%E7%B6%9A%E6%96%B9%E6%B3%95

Verwendung von PHP

Im Fall von Windows + PHP ist es nicht einfach zu bedienen. Siehe unten für Details.

Es fällt mir schwer, Spatialite zu verwenden, wenn ich versuche, räumliche Informationen wie Karten in DB mit PHP zu speichern http://qiita.com/mima_ita/items/1a90de89f0a194ba843e

Andere Tipps

Führen Sie eine geografische Konvertierung mit Spatialite durch

Mit der Funktion Transformieren können Sie das geodätische System transformieren. Im folgenden Beispiel wird das orthogonale Koordinatensystem 6 JGD2000 / Ebene in das Weltvermessungssystem konvertiert.

select AsText(Transform(GeomFromText('POINT(-4408.916645 -108767.765479)', 2448), 4326))

Referenz

SpatiaLite 4.2.0 SQL functions reference list http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

The SpatiaLite Cookbook http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html

** Ein kurzes Tutorial zu SpatiaLite - eine räumliche Erweiterung für SQLite (alt, aber nützlich) ** http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html