2022-02-21 12:47:59 +01:00
|
|
|
* Notes
|
|
|
|
|
|
|
|
|
|
Vous pouvez trouver ici les notes plus ou moins en vrac que j'ai
|
|
|
|
|
prise lors de l'étude de ce projet. C'était la première fois que je
|
|
|
|
|
touchais a un GIS. Si c'est également votre cas, il y a
|
|
|
|
|
potentiellement des réponses a vos futures questions ici.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
** Load Spatialite
|
|
|
|
|
1. Have libspatialite in your LD_LIBRARY_PATH.
|
|
|
|
|
2. Run =select load_extension("mod_spatialite");= at SQLite startup.
|
|
|
|
|
** How to Create a point
|
|
|
|
|
Example in this tutorial: https://gist.github.com/sixman9/805823
|
|
|
|
|
|
|
|
|
|
** How to Test Points
|
|
|
|
|
Use the spatial index table.
|
|
|
|
|
|
|
|
|
|
/!\ DO NOT USE =CONTAINS= to filter out data. It's not as performant as the spatial index.
|
|
|
|
|
|
|
|
|
|
See https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/rtree.html for what's happening under the hood.
|
|
|
|
|
|
|
|
|
|
** Spatialite Getting Started
|
|
|
|
|
|
|
|
|
|
Petit test pour voir comment ça marche.
|
|
|
|
|
|
|
|
|
|
#+BEGIN_SRC
|
|
|
|
|
spatialite> CREATE TABLE points (id INTEGER PRIMARY KEY NOT NULL);
|
|
|
|
|
spatialite> SELECT AddGeometryColumn('points','dummypoints',3857,'POINT');
|
|
|
|
|
1
|
|
|
|
|
spatialite> INSERT INTO points (dummypoints) VALUES(Transform(MakePoint(384998.246399999,6366249.435399996366249.43539999,4964),3857));
|
|
|
|
|
spatialite> select * from points;
|
|
|
|
|
1|
|
|
|
|
|
spatialite> select AsText(dummypoints) from points;
|
|
|
|
|
|
|
|
|
|
#+END_SRC
|
|
|
|
|
|
|
|
|
|
** SRID, quezako?
|
|
|
|
|
- Bonne référence: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/srid.html
|
|
|
|
|
|
|
|
|
|
En pratique, on spécifie le SRID a spatialite en utiliant le code EPSG
|
|
|
|
|
|
|
|
|
|
- Projection règlementaire en France: RGF93, EPSG:2154
|
|
|
|
|
- Mercator (OSM/Google Maps): WGS84, EPSG:4326
|
|
|
|
|
|
|
|
|
|
D'après https://geodesie.ign.fr/index.php?page=rgf93, le RGF93 est compatible avec Mercator.
|
|
|
|
|
|
|
|
|
|
** DSPs to Ingest
|
|
|
|
|
ADTH AISN BEFO BTHD CTYF EURE FI44 NATH NIVE NPDC SART SHSN SIEL SPTH VAUC
|
2022-02-21 19:00:11 +01:00
|
|
|
|
|
|
|
|
** Requête sur l'Index Spatial
|
|
|
|
|
|
|
|
|
|
#+BEGIN_SRC sql
|
|
|
|
|
SELECT * FROM ipe
|
|
|
|
|
WHERE ROWID = (
|
|
|
|
|
SELECT ROWID FROM SpatialIndex
|
|
|
|
|
WHERE f_table_name = 'ipe'
|
|
|
|
|
AND search_frame = BuildMBR(
|
|
|
|
|
-1.405456066131592,
|
|
|
|
|
43.53722495535158,
|
|
|
|
|
-1.3643002510070803,
|
|
|
|
|
43.54475322429539,
|
|
|
|
|
4326)
|
|
|
|
|
);
|
|
|
|
|
#+END_SRC
|
