[PYTHON] Essayez d'utiliser Spatia Lite, qui stocke des informations spatiales telles que des cartes dans SQLite

Aperçu

Spatia Lite est une extension de SQLite qui peut stocker des informations spatiales telles que des cartes. Les informations de géométrie écrites dans les fichiers Shp et GeoJson peuvent être stockées et utilisées dans la base de données.

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

Description du fichier de distribution

SpatiaLite Vous pouvez utiliser la base de données à partir de la ligne de commande.

** Distribution binaire ** Pour Windows, vous pouvez obtenir le binaire de n'importe quelle plate-forme à partir de ce qui suit. http://www.gaia-gis.it/gaia-sins/index.html

mod_spatialite.dll/mod_spatialite.so Une extension de SQLite. Les fonctions SpatiaLite peuvent être utilisées en chargeant un fichier DLL ou plus en utilisant load_extension sur SQLite.

** Distribution binaire ** Pour Windows, vous pouvez obtenir le binaire de n'importe quelle plate-forme à partir de ce qui suit. http://www.gaia-gis.it/gaia-sins/index.html

** Distribution du code source ** Vous pouvez le créer en téléchargeant et en construisant le code source de libspatialite à partir de ce qui suit. https://www.gaia-gis.it/fossil/libspatialite/index

spatialite_gui Vous pouvez utiliser la fonction spatialite dans l'interface graphique.

Pour les colonnes de type Geometry telles que POLYGON, vous pouvez vérifier le contenu sous forme d'image.

** Distribution binaire ** Pour Windows, vous pouvez obtenir le binaire de n'importe quelle plate-forme à partir de ce qui suit. http://www.gaia-gis.it/gaia-sins/index.html

Construire libspatialite

Si vous ne pouvez pas obtenir mod_spatialite en binaire, vous devez compiler libspatialite.

Les bibliothèques suivantes sont nécessaires pour construire libspatialite-4.2.0. proj-4.8.0 proj.4 est une bibliothèque de projection cartographique. http://trac.osgeo.org/proj/

geos-3.4.2 Geometry Engin est un portage de Java Topology Suite vers C ++, un prédicat spatial et une API de fonctionnalité pour le traitement de la géométrie. http://trac.osgeo.org/geos/

Après avoir installé ce qui précède, exécutez la commande suivante dans le dossier contenant la source de libsatialite.

./configure --disable-freexl
make
make install

--disable-free xl désactive la fonction de liaison avec Excel. Si vous activez cela, vous aurez besoin de freexl. https://www.gaia-gis.it/fossil/freexl/index

Si vous obtenez une erreur sur FreeBSD

Vous pouvez obtenir l'erreur suivante sur FreeBSD

/usr/bin/ld: cannot find -ldl

Dans FreeBSD, dlopen est une bibliothèque standard, donc -ldl n'est pas nécessaire. Recherchez -ldl dans src / Makefile, supprimez-le, puis réessayez de faire.

référence: http://sourceforge.net/p/idjc/discussion/458834/thread/246f0841/

Utilisez mod_spatialite de SQLite.

Comme mentionné précédemment, spatialite est disponible avec load_extension, mais il y a quelques mises en garde.

-La version SQLite doit être 3.7.17 ou ultérieure.

· Toutes les DLL dépendantes doivent être situées dans un emplacement visible par SQLite. Spécifiez le dossier contenant la DLL associée avec la variable d'environnement PATH.

-Les processus SQLite et mod_spatialite doivent être construits avec les mêmes bits. Si SQLite fonctionne sur 32 bits, mod_spatialite doit être construit sur 32 bits, et si SQLite fonctionne sur 64 bits, mod_spatialite doit être construit sur 64 bits. Si vous avez besoin de SQLite binaire 64 bits sous Windows, vous pouvez le créer en suivant l'article ci-dessous. http://qiita.com/akaneko3/items/0e99c3c1366dfbad006f

-En chargeant le fichier DLL ou plus à l'aide de load_extension, la fonction spatialite peut être utilisée par la suite.

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

-Lorsque la base de données est créée à partir de SQLite3, les métadonnées gérées par SpatiaLite ne sont pas créées. Exécutez le SQL suivant pour créer ces métadonnées.

SELECT InitSpatialMetaData();

Didacticiel

Ici, nous allons en fait stocker la zone administrative de l'information numérique foncière nationale dans spatialite et l'utiliser.

Acquérir des zones administratives dans tout le pays avec des informations numériques sur les terres nationales.

Tout d'abord, téléchargez les zones administratives du pays à partir de la page ci-dessous. http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03.html

Créez un fichier SQLite vide.

Démarrez spatialite_gui et créez un fichier de base de données SQLite vide.

Appuyez sur l'icône Création d'une nouvelle base de données SQLite (vide) et entrez un nom de fichier.

S'il est créé avec succès, le chemin d'accès à la base de données créée sera affiché.

Jusqu'à présent, ce n'est pas différent d'une base de données normale.

Importer un fichier shp

Importez le fichier shp des informations numériques sur les terres nationales dans la base de données.

Appuyez sur l'icône [Charger le fichier de forme] et sélectionnez "N03-14_140401.shp" dans les informations numériques des terres nationales.

A ce moment, il vous sera demandé l'encodage, alors sélectionnez "CP932".

Une fois l'opération terminée, une table appelée "N03-14_140401" sera créée.

Vérifiez la table créée à partir du fichier shp

Ici, nous allons vérifier la table créée à partir du fichier shp. Pour voir les colonnes du tableau créé, sélectionnez le tableau, cliquez avec le bouton droit de la souris et sélectionnez Afficher les colonnes.

N03_001 à N03_004 sont des préfectures, des quartiers, des villes et N03_007 est une colonne de type TEXT dans laquelle le code est stocké. Geometry stocke la forme de la zone administrative en tant que Blob et est du type spécifié par spatia Lite appelé POLYGON.

Vous pouvez voir l'instruction SQL réelle utilisée pour créer cette table en cliquant avec le bouton droit de la souris sur la table et en sélectionnant Afficher l'instruction CREATE.

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)

Regardons en fait le contenu de la table. Pour ce faire, exécutez une instruction SQL.

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

Une fois exécutés, les informations suivantes peuvent être obtenues.

Les données sont exprimées sous forme de texte de PK_UID à N03_007. Cependant, puisque la géométrie est des données BLOB, les utilisateurs ne peuvent pas voir le contenu.

Il existe deux façons de vérifier ce contenu. La première consiste à utiliser l'explorateur BLOB. Sélectionnez une cellule dans la colonne Géométrie, faites un clic droit et sélectionnez "Exploration BLOB".

BLOB explore peut représenter des données dans divers formats tels que binaire, image, SVG, GeoJSON, etc.

Une façon d'éviter d'utiliser l'exploration BLOB consiste à utiliser SQL pour afficher les colonnes Geometry dans n'importe quel format.

L'exemple suivant affiche la colonne Geometry au format texte.

--Étant donné que les données sont longues, affichez les 100 premiers caractères
SELECT substr(ASTEXT(Geometry), 1, 100) FROM "N03-14_140401" LIMIT 10;

J'ai pu afficher les informations POLYGON sous forme de caractères dans ASTEXT. Il peut être produit dans n'importe quel format. Par exemple, s'il s'agit de ASGEOJSON, il sera affiché en tant que GeoJSON.

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

Les fonctions fournies par Spatia Lite d'ASTEXT et ASGEOJSON sont décrites dans le "Guide de référence des fonctions Spatial SQL" ci-dessous.

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

Essayez de créer vous-même une table avec des informations de coordonnées

Dans l'exemple précédent, j'ai créé une table à partir du fichier shp, mais cette fois je vais créer moi-même une table avec des informations de coordonnées.

Dans le tableau ci-dessous, créez un tableau avec les coordonnées des informations sur les caractéristiques telles que la tour de Tokyo.

Tout d'abord, créez uniquement des colonnes qui ne contiennent pas d'informations sur les fonctionnalités.

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

Après avoir exécuté SQL, actualiser ajoutera la table.

Ensuite, créez une colonne représentant les informations sur les fonctionnalités à l'aide de AddGeometryColumn ().

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

Après avoir exécuté SQL, actualiser ajoutera une colonne et ajoutera une icône de globe à la table.

Après avoir créé le tableau, il est temps de saisir les données. Dans l'exemple suivant, les coordonnées du bureau métropolitain sont stockées dans la table.

INSERT INTO places (name, Geometry) VALUES(
  'Bureau du gouvernement métropolitain',
  GeomFromText('POINT(139.692101 35.689634 )')
)

GeomFromText est une fonction qui change une chaîne en Geometry. Cette fois, j'ai converti à partir du format texte, mais vous pouvez le créer à partir de GeoJSON ou Kml en utilisant GeomFromGeoJSON, GeomFromKml, etc.

Si vous créez tout cela en même temps comme indiqué ci-dessous, cela fonctionnera comme ça à première vue, mais cela ne fonctionnera pas correctement lors de la création d'un index RTree car les métadonnées ne lui sont pas associées.

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

Rechercher des informations de coordonnées

Ici, nous rechercherons des informations de coordonnées. Cherchons quelle division administrative contient les coordonnées spécifiées dans le tableau des lieux.

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

La fonction Contient vérifie si la plage du premier argument inclut la zone du deuxième argument. Le résultat est le suivant:

Le bureau métropolitain étant situé dans le quartier de Shinjuku, on peut dire que le résultat est comme prévu. Dans l'exemple précédent, la recherche a été effectuée à partir des données stockées dans la table, mais il est également possible de la spécifier directement dans le quartier WHERE.

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

MBR et recherche approximative

La recherche précédente était une recherche précise pour chaque coordonnée, mais il y a des moments où vous souhaitez obtenir rapidement une position approximative.

Dans ce cas, utilisez Rectangle englobant minimum --MBR. MBR représente la taille approximative de la GEOMETRIE.

Considérez les formes complexes suivantes comme des formes de bordure rouge.

Cela permet une recherche imprécise mais rapide. Utilisez Envelope () pour obtenir le MBR de GEOMETRY.

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

Le résultat est un simple chiffre comme celui-ci:

Maintenant, recherchons les coordonnées qui incluent le même bureau métropolitain qu'avant d'utiliser MBR.

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

Au lieu d'améliorer la vitesse de recherche, des données inutiles ont également été extraites comme indiqué ci-dessous.

Recherche à l'aide de l'index RTree

Normalement, lorsqu'il s'agit de DB, la vitesse de recherche est améliorée en utilisant l'index. Malheureusement, Geometry ne peut pas recevoir d'index pouvant être attribué aux lettres et aux nombres. Cependant, Spatia Lite exploite RTree de SQLite pour prendre en charge les index RTree. Veuillez vous référer à ce qui suit pour l'algorithme d'index RTree.

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

Pour donner un aperçu de RTree, la géométrie est transformée en rectangle à l'aide de MBR et un arbre est créé en fonction de l'intersection des rectangles pour faciliter la recherche. Il est appelé index RTree car il crée un arbre en utilisant un rectangle.

Création et utilisation des index RTree

Utilisons maintenant en fait l'index RTree. Utilisez la fonction CreateSpatialIndex pour créer un index RTree. Spécifiez le nom de la table dans le premier argument et le nom de la colonne dans le deuxième argument.

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

S'il a été créé avec succès, il renvoie 1 et vous pouvez voir que SpatialIndex affiche la nouvelle table après l'actualisation.

En créant l'index RTree, les quatre tables suivantes sont créées.

・ Idx_N03-14_140401_Geometry ・ Idx_N03-14_140401_Geometry_node ・ Idx_N03-14_140401_Geometry_parent ・ Idx_N03-14_140401_Geometry_rowid

Sauf pour idx_N03-14_140401_Geometry, c'est une table utilisée en interne et nous ne nous attendons pas à ce que l'utilisateur l'exploite directement.

idx_N03-14_140401_Geometry est une TABLE VIRTUELLE, qui utilise en fait une table interne pour renvoyer le résultat.

Maintenant, vérifions le contenu de idx_N03-14_140401_Geometry.

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

Le MRB de chaque enregistrement est stocké de cette manière.

En utilisant ce tableau, les utilisateurs peuvent filtrer les données à partir d'une grande quantité de données et obtenir le résultat.

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 )')
)

Je pense que j'ai pu obtenir des données précises plus rapidement qu'une recherche normale. Plus le nombre de données est grand, plus la différence est grande.

De cette façon, contrairement à un index normal, l'index RTree n'est pas appliqué simplement en exécutant SQL sur la table réelle, mais il joue un rôle de filtrage des données par sous-requête ou JOIN en utilisant la TABLE VIRTUELLE créée.

En gérant l'index dans une table distincte, vous vous demandez peut-être s'il peut y avoir une différence lorsque la table réelle est mise à jour. Cependant, comme le déclencheur a été créé lors de l'exécution de CreateSpatialIndex, l'utilisateur peut synchroniser l'index et la table sans aucune modification.

Pour voir ce déclencheur, exécutez le SQL suivant:

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

Cette fois, vous pouvez voir que les déclencheurs suivants sont automatiquement créés.

・ Ggi_N03-14_140401_Géométrie ・ Ggu_N03-14_140401_Geometry ・ Gii_N03-14_140401_Géométrie ・ Giu_N03-14_140401_Géométrie ・ Gid_N03-14_140401_Géométrie

Supprimer l'index RTree

Utilisez DisableSpatialIndex pour supprimer l'index RTree que vous avez créé.

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

Vous pouvez voir que l'index spatial a été supprimé en exécutant une actualisation.

Utiliser à partir de python

Si vous utilisez Spatia Lite à partir de python, il est facile à utiliser si SQLite est correctement disponible.

Vous pouvez savoir quelle version de Sqlite3 Python utilise en suivant ces étapes:

import sqlite3
print (sqlite3.sqlite_version_info)

S'il ne correspond pas à la version, veuillez mettre à jour SQLITE. Pour Windows, réécrivez la DLL dans le dossier suivant.

C:\Python27\DLLs\sqlite3.dll

Voici le code en Python.

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

# mod_Ajoutez le dossier avec spatialite à votre PATH
os.environ["PATH"] = os.environ["PATH"] + ';C:\\tool\\spatialite\\mod_spatialite-4.2.0-win-x86'
cnn = sqlite3.connect('database/gyouseikuiki.sqlite')

# mod_Chargement spatialite
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') )

Passez la variable d'environnement via le chemin avec mod_spatialite, autorisez l'utilisation de l'extension DLL avec enable_load_extension, puis chargez mod_spatialite.

Après cela, vous pouvez exécuter SQL comme d'habitude.

Si une erreur se produit avec enable_load_extension

L'erreur suivante peut se produire lors de l'utilisation de enable_load_extension.

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

La raison en est que Python, qui est préinstallé sur certains systèmes d'exploitation tels que MaxOS et Debian, désactive la fonction de chargement de la bibliothèque d'extensions au moment de la compilation.

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

La seule façon de résoudre ce problème est de réinstaller Python. Lors de l'installation, vous devrez modifier manuellement setup.py dans le dossier du code source Python.

Recherchez SQLITE_OMIT_LOAD_EXTENSION, commentez cette ligne et exécutez ./configure, make, make install.

Si vous obtenez une erreur avec make install, essayez "make -i alt install" si vous obtenez une erreur avec make install.

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

Comment utiliser Peewee

Veuillez vous référer à ce qui suit pour savoir comment utiliser spatialite avec la bibliothèque Peewee qui est un ORM.

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

Utiliser depuis php

Dans le cas de Windows + PHP, ce n'est pas facile à utiliser. Voir ci-dessous pour plus de détails.

J'ai du mal à utiliser Spatialite lorsque j'essaie de stocker des informations spatiales telles que des cartes dans DB avec PHP http://qiita.com/mima_ita/items/1a90de89f0a194ba843e

Autres conseils

Effectuer une conversion géographique à l'aide de Spatialite

Vous pouvez utiliser la fonction Transformer pour transformer le système géodésique. Dans l'exemple ci-dessous, le système de coordonnées orthogonales JGD2000 / plan 6 est converti en système de levé mondial.

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

référence

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

** Un tutoriel rapide sur SpatiaLite - une extension spatiale pour SQLite (ancienne mais utile) ** http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html