Generalities about OGC Geospatial extensions for SQL
Contents provided by F. Lovergine
Structured Query Language (SQL) is a family of languages implemented since 1974 by IBM and many others to implement the so called relational model for structured data. In brief, a relational data model is a representation of the abstract application domain data model in terms of multiple tables (rows) of attributes (columns) and relationships (i.e. linking attributes) amont them. Such a kind of tables can be familiar for most of you as a dataframe in the pandas package for Python or the R language.
SQL implemnted multiple sub-languages to create, register, update and query a system of tables based on the so-called relational algebra as directly inspired to the mathematical theory of sets where any type of operation can be expressed in terms of unions, intersections, differences, includes, negations and a few other simple operators.
Originally defined in pure declarative terms, today the current SQL standard also includes procedural, types, and control flow. The current ANSI/ISO standard is generally extended by implementations.
An example of an SQL query on a table (courtesy of Wikipedia):
[1]:
from IPython.display import Image
Image("../images/sql.png", width=800, height=600)
[1]:
The Open Geospatial Consortium (OGC) has promoted 1994 a standard for geospatial queries, that is currently implemented by most of the relational databases.
See OGC Standards for complete information.
A non exhaustive list of real-world databases (DBMS) with geospatial extensions includes:
Postgres/PostGIS
MySQL/Mariadb
Oracle
Ingres
MS/SQL
Sybase
Sqlite/Spatialite/Geopackage
The bold ones are those typically used in the FOSS world. Note that ``geopackage`` is roughly an exchange format, not a complete Spatial SQL implementation.
Those DBMS could include or not even a support for datacubes and rasters, but the OGC Simple Feature Access standard only deals with vector data. Often in the real world, DBMS are only used for rasters metadata, while the images/maps are maintained in an specialized object storage.
Spatialite, a portable relational geodatabase
A single file and single library solution
Multi-threaded
OGC compliant
Fast and compact, no server required
Very scalable even for tons of GBs of data
Based on SQLite engine
The Spatialte documentation include a complete reference of its SQL geospatial dialect, while SQLite documentation gives all details about the generalites of the core relational database.
OSGeoLive already includes Spatialite, for other Debian derived distributions you can install as follows:
sudo apt install libsqlite3-mod-spatialite spatialite-bin spatialite-gui
You can verify to have the required software installed as follows:
[2]:
!ogrinfo --formats | grep -i spatialite
SQLite -vector- (rw+v): SQLite / Spatialite
[3]:
!gdalinfo --formats | grep -i rasterlite
Rasterlite -raster- (rwvs): Rasterlite
A spatialite package for Python can also be installed (use an environment):
[4]:
!pip3 install spatialite
Requirement already satisfied: spatialite in /home/user/venv/lib/python3.10/site-packages (0.0.3)
You can verify that it is working with:
[5]:
import spatialite as sp
[6]:
with sp.connect('new.db') as db:
print(db.execute('SELECT spatialite_version()').fetchone()[0])
5.0.1
[7]:
!spatialite new.db '.tables'
SpatiaLite version ..: 5.0.1 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualGeoJSON' [direct GeoJSON access]
- 'VirtualXL' [direct XLS access]
- 'VirtualNetwork' [Dijkstra shortest path - obsolete]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'VirtualBBox' [BoundingBox tables]
- 'VirtualSpatialIndex' [R*Tree metahandler]
- 'VirtualElementary' [ElemGeoms metahandler]
- 'VirtualRouting' [Dijkstra shortest path - advanced]
- 'VirtualKNN' [K-Nearest Neighbors metahandler]
- 'VirtualGPKG' [OGC GeoPackage interoperability]
- 'VirtualXPath' [XML Path Language - XPath]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ version ........: Rel. 9.1.1, December 1st, 2022
GEOS version ........: 3.11.1-CAPI-1.17.1
RTTOPO version ......: 1.1.0
TARGET CPU ..........: x86_64-linux-gnu
ElementaryGeometries raster_coverages_ref_sys
ISO_metadata raster_coverages_srid
ISO_metadata_reference rl2map_configurations
ISO_metadata_view rl2map_configurations_view
KNN spatial_ref_sys
SE_external_graphics spatial_ref_sys_all
SE_external_graphics_view spatial_ref_sys_aux
SE_fonts spatialite_history
SE_fonts_view sql_statements_log
SE_raster_styled_layers stored_procedures
SE_raster_styled_layers_view stored_variables
SE_raster_styles topologies
SE_raster_styles_view vector_coverages
SE_vector_styled_layers vector_coverages_keyword
SE_vector_styled_layers_view vector_coverages_ref_sys
SE_vector_styles vector_coverages_srid
SE_vector_styles_view vector_layers
SpatialIndex vector_layers_auth
data_licenses vector_layers_field_infos
geom_cols_ref_sys vector_layers_statistics
geometry_columns views_geometry_columns
geometry_columns_auth views_geometry_columns_auth
geometry_columns_field_infos views_geometry_columns_field_infos
geometry_columns_statistics views_geometry_columns_statistics
geometry_columns_time virts_geometry_columns
idx_ISO_metadata_geometry virts_geometry_columns_auth
idx_ISO_metadata_geometry_node virts_geometry_columns_field_infos
idx_ISO_metadata_geometry_parent virts_geometry_columns_statistics
idx_ISO_metadata_geometry_rowid wms_getcapabilities
networks wms_getmap
raster_coverages wms_ref_sys
raster_coverages_keyword wms_settings
Fun fuct
Don’t make typos in connect(), else you will create a new empty Spatialite database instead of getting your data :-) Note that spatialite package is a simple wrapper for the main sqlite3 package that simply load an extension after opening the SQLite database. So, find the full documentation for the package functions reference there.
Hands on
Take your time to become confident with spatialite and spatialite-gui commands which are the main administrative tools for your data. Have a look to their man pages, help and command line options.
Hint
Some key aspects of Sqlite/Spatialite are concerning concurrent accesses, transactions and cache, all aspects governed by so called pragma directives which are essential for advanced uses (e.g. big data, multi-threading, performance tuning).
Spatialite and Geopackage files are flavors of the same basic engine (but SL is the most complete as a geo database)
[9]:
! file tree_height/geodata_vector/eu_x_y_height_select.gpkg
tree_height/geodata_vector/eu_x_y_height_select.gpkg: SQLite 3.x database (OGC GeoPackage file), user version 10200, last written using SQLite version 3021000, file counter 2635192, database pages 34553, cookie 0xf2, schema 4, UTF-8, version-valid-for 2635192
[10]:
! file new.db
new.db: SQLite 3.x database, last written using SQLite version 3037002, file counter 1, database pages 1698, cookie 0x118, schema 4, UTF-8, version-valid-for 1
Let’s see a simple importing of data into Spatialite
[11]:
! ogrinfo -so -al tree_height/geodata_vector/eu_x_y_height_select.gpkg
INFO: Open of `tree_height/geodata_vector/eu_x_y_height_select.gpkg'
using driver `GPKG' successful.
Layer name: New Layer
Geometry: Point
Feature Count: 1267239
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
GEOGCRS["WGS 84",
ENSEMBLE["World Geodetic System 1984 ensemble",
MEMBER["World Geodetic System 1984 (Transit)"],
MEMBER["World Geodetic System 1984 (G730)"],
MEMBER["World Geodetic System 1984 (G873)"],
MEMBER["World Geodetic System 1984 (G1150)"],
MEMBER["World Geodetic System 1984 (G1674)"],
MEMBER["World Geodetic System 1984 (G1762)"],
MEMBER["World Geodetic System 1984 (G2139)"],
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]],
ENSEMBLEACCURACY[2.0]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
USAGE[
SCOPE["Horizontal component of 3D system."],
AREA["World."],
BBOX[-90,-180,90,180]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
FID Column = fid
Geometry Column = geom
ID: Integer (0.0)
height: Real (0.0)
[13]:
! ogrinfo -al tree_height/geodata_vector/eu_x_y_height_select.gpkg -where 'fid<=5'
INFO: Open of `tree_height/geodata_vector/eu_x_y_height_select.gpkg'
using driver `GPKG' successful.
Layer name: New Layer
Geometry: Point
Feature Count: 5
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
GEOGCRS["WGS 84",
ENSEMBLE["World Geodetic System 1984 ensemble",
MEMBER["World Geodetic System 1984 (Transit)"],
MEMBER["World Geodetic System 1984 (G730)"],
MEMBER["World Geodetic System 1984 (G873)"],
MEMBER["World Geodetic System 1984 (G1150)"],
MEMBER["World Geodetic System 1984 (G1674)"],
MEMBER["World Geodetic System 1984 (G1762)"],
MEMBER["World Geodetic System 1984 (G2139)"],
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]],
ENSEMBLEACCURACY[2.0]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
USAGE[
SCOPE["Horizontal component of 3D system."],
AREA["World."],
BBOX[-90,-180,90,180]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
FID Column = fid
Geometry Column = geom
ID: Integer (0.0)
height: Real (0.0)
OGRFeature(New Layer):1
ID (Integer) = 1
height (Real) = 49.727499
POINT (6.050001 49.727499)
OGRFeature(New Layer):2
ID (Integer) = 2
height (Real) = 49.922155
POINT (6.0500017 49.922155)
OGRFeature(New Layer):3
ID (Integer) = 3
height (Real) = 48.602377
POINT (6.0500021 48.602377)
OGRFeature(New Layer):4
ID (Integer) = 4
height (Real) = 48.151979
POINT (6.0500089 48.151979)
OGRFeature(New Layer):5
ID (Integer) = 5
height (Real) = 49.58841
POINT (6.0500102 49.58841)
[14]:
! time ogr2ogr -f 'ESRI Shapefile' /tmp/eu_x_y_height_select tree_height/geodata_vector/eu_x_y_height_select.gpkg
real 0m48.982s
user 0m23.634s
sys 0m11.009s
[15]:
! spatialite -silent /tmp/eu_x_y_height_select.splite '.loadshp "/tmp/eu_x_y_height_select/New\ Layer" heights utf8'
the SPATIAL_REF_SYS table already contains some row(s)
========
Loading shapefile at '/tmp/eu_x_y_height_select/New Layer' into SQLite table 'heights'
BEGIN;
CREATE TABLE "heights" (
"pk_uid" INTEGER PRIMARY KEY AUTOINCREMENT,
"id" INTEGER,
"height" DOUBLE);
SELECT AddGeometryColumn('heights', 'geometry', -1, 'POINT', 'XY');
COMMIT;
Inserted 1267239 rows into 'heights' from SHAPEFILE
========
[17]:
! spatialite -silent /tmp/eu_x_y_height_select.splite "select avg(height),min(height),max(height),sqrt(avg(height*height)-avg(height)*avg(height)) from heights"
49.3651054490831|47.976346|49.95|0.468988390097542
Note that function set for Spatialite/SQLite is somehow simplified, sometimes other useful functions could be provided as an add-on extension
Hint
In order to perform write (i.e. create/delete/drop/insert/update ops) operations it is generally required to establish a transaction in order to maximize performances. This is specifically useful when performing operations programmatically (outside of the CLI tool).
That allows both:
locking the database for access
doing all-in-one bufferized operations
Note that default transactions are DEFERRED, so they start at first database access. At logical level, a transaction ensures that the whole set of operations performed can be considered as atomic (i.e. performed/discarded all together).
[18]:
from IPython.display import Image
Image("../images/transaction.png" , width = 800, height = 800)
[18]:
Let’s see some easy operations with SQLite/Spatialite drivers for OGR and the tree_height dataset.
First of all, create a copy with an explicit suffix for CSV file which can be managed by OGR tools:
[19]:
!cp tree_height/txt/eu_y_x_select_6algorithms_fullTable.txt tree_height/txt/eu_y_x_select_6algorithms_fullTable.csv
[20]:
!ogrinfo -al -so tree_height/txt/eu_y_x_select_6algorithms_fullTable.csv
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.csv'
using driver `CSV' successful.
Layer name: eu_y_x_select_6algorithms_fullTable
Geometry: None
Feature Count: 1267239
Layer SRS WKT:
(unknown)
ID: String (0.0)
X: String (0.0)
Y: String (0.0)
a1_95: String (0.0)
a2_95: String (0.0)
a3_95: String (0.0)
a4_95: String (0.0)
a5_95: String (0.0)
a6_95: String (0.0)
min_rh_95: String (0.0)
max_rh_95: String (0.0)
BEAM: String (0.0)
digital_elev: String (0.0)
elev_low: String (0.0)
qc_a1: String (0.0)
qc_a2: String (0.0)
qc_a3: String (0.0)
qc_a4: String (0.0)
qc_a5: String (0.0)
qc_a6: String (0.0)
se_a1: String (0.0)
se_a2: String (0.0)
se_a3: String (0.0)
se_a4: String (0.0)
se_a5: String (0.0)
se_a6: String (0.0)
deg_fg: String (0.0)
solar_ele: String (0.0)
[21]:
! time ogr2ogr -f SQLite -dsco SPATIALITE=YES -lco LAUNDER=YES -oo X_POSSIBLE_NAMES=X -oo Y_POSSIBLE_NAMES=Y "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" "tree_height/txt/eu_y_x_select_6algorithms_fullTable.csv"
real 1m31.803s
user 1m6.724s
sys 0m7.813s
Now, the original CSV file has been ingested as a Spatialite database and many operations can be performed efficiently on both attributes and geometries.
[22]:
!ogrinfo -al -so "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite"
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: eu_y_x_select_6algorithms_fulltable
Geometry: Point
Feature Count: 1267239
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = GEOMETRY
id: String (0.0)
x: Real (0.0)
y: Real (0.0)
a1_95: String (0.0)
a2_95: String (0.0)
a3_95: String (0.0)
a4_95: String (0.0)
a5_95: String (0.0)
a6_95: String (0.0)
min_rh_95: String (0.0)
max_rh_95: String (0.0)
beam: String (0.0)
digital_elev: String (0.0)
elev_low: String (0.0)
qc_a1: String (0.0)
qc_a2: String (0.0)
qc_a3: String (0.0)
qc_a4: String (0.0)
qc_a5: String (0.0)
qc_a6: String (0.0)
se_a1: String (0.0)
se_a2: String (0.0)
se_a3: String (0.0)
se_a4: String (0.0)
se_a5: String (0.0)
se_a6: String (0.0)
deg_fg: String (0.0)
solar_ele: String (0.0)
[23]:
!ogrinfo -al "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" -where 'CastToInteger(id) <= 5'
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: eu_y_x_select_6algorithms_fulltable
Geometry: Point
Feature Count: 5
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = GEOMETRY
id: String (0.0)
x: Real (0.0)
y: Real (0.0)
a1_95: String (0.0)
a2_95: String (0.0)
a3_95: String (0.0)
a4_95: String (0.0)
a5_95: String (0.0)
a6_95: String (0.0)
min_rh_95: String (0.0)
max_rh_95: String (0.0)
beam: String (0.0)
digital_elev: String (0.0)
elev_low: String (0.0)
qc_a1: String (0.0)
qc_a2: String (0.0)
qc_a3: String (0.0)
qc_a4: String (0.0)
qc_a5: String (0.0)
qc_a6: String (0.0)
se_a1: String (0.0)
se_a2: String (0.0)
se_a3: String (0.0)
se_a4: String (0.0)
se_a5: String (0.0)
se_a6: String (0.0)
deg_fg: String (0.0)
solar_ele: String (0.0)
OGRFeature(eu_y_x_select_6algorithms_fulltable):1
id (String) = 1
x (Real) = 6.050001
y (Real) = 49.727499
a1_95 (String) = 3139
a2_95 (String) = 3139
a3_95 (String) = 3139
a4_95 (String) = 3120
a5_95 (String) = 3139
a6_95 (String) = 3139
min_rh_95 (String) = 3120
max_rh_95 (String) = 3139
beam (String) = 5
digital_elev (String) = 4.1e+02
elev_low (String) = 383.72153
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.962
se_a2 (String) = 0.984
se_a3 (String) = 0.968
se_a4 (String) = 0.962
se_a5 (String) = 0.989
se_a6 (String) = 0.979
deg_fg (String) = 0
solar_ele (String) = 17.7
POINT (6.050001 49.727499)
OGRFeature(eu_y_x_select_6algorithms_fulltable):2
id (String) = 2
x (Real) = 6.0500017
y (Real) = 49.922155
a1_95 (String) = 1022
a2_95 (String) = 2303
a3_95 (String) = 970
a4_95 (String) = 872
a5_95 (String) = 5596
a6_95 (String) = 1524
min_rh_95 (String) = 872
max_rh_95 (String) = 5596
beam (String) = 5
digital_elev (String) = 2.9e+02
elev_low (String) = 2374.1411
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.948
se_a2 (String) = 0.990
se_a3 (String) = 0.960
se_a4 (String) = 0.948
se_a5 (String) = 0.994
se_a6 (String) = 0.980
deg_fg (String) = 0
solar_ele (String) = 43.7
POINT (6.0500017 49.922155)
OGRFeature(eu_y_x_select_6algorithms_fulltable):3
id (String) = 3
x (Real) = 6.0500021
y (Real) = 48.602377
a1_95 (String) = 380
a2_95 (String) = 1336
a3_95 (String) = 332
a4_95 (String) = 362
a5_95 (String) = 1336
a6_95 (String) = 1340
min_rh_95 (String) = 332
max_rh_95 (String) = 1340
beam (String) = 4
digital_elev (String) = 4.4e+02
elev_low (String) = 435.97781
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.947
se_a2 (String) = 0.975
se_a3 (String) = 0.956
se_a4 (String) = 0.947
se_a5 (String) = 0.981
se_a6 (String) = 0.968
deg_fg (String) = 0
solar_ele (String) = 0.2
POINT (6.0500021 48.602377)
OGRFeature(eu_y_x_select_6algorithms_fulltable):4
id (String) = 4
x (Real) = 6.0500089
y (Real) = 48.151979
a1_95 (String) = 3153
a2_95 (String) = 3142
a3_95 (String) = 3142
a4_95 (String) = 3127
a5_95 (String) = 3138
a6_95 (String) = 3142
min_rh_95 (String) = 3127
max_rh_95 (String) = 3153
beam (String) = 2
digital_elev (String) = 4.5e+02
elev_low (String) = 422.00537
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.930
se_a2 (String) = 0.970
se_a3 (String) = 0.943
se_a4 (String) = 0.930
se_a5 (String) = 0.978
se_a6 (String) = 0.962
deg_fg (String) = 0
solar_ele (String) = -14.2
POINT (6.0500089 48.151979)
OGRFeature(eu_y_x_select_6algorithms_fulltable):5
id (String) = 5
x (Real) = 6.0500102
y (Real) = 49.58841
a1_95 (String) = 666
a2_95 (String) = 4221
a3_95 (String) = 651
a4_95 (String) = 33
a5_95 (String) = 5611
a6_95 (String) = 2723
min_rh_95 (String) = 33
max_rh_95 (String) = 5611
beam (String) = 8
digital_elev (String) = 3.7e+02
elev_low (String) = 2413.7483
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.941
se_a2 (String) = 0.983
se_a3 (String) = 0.946
se_a4 (String) = 0.941
se_a5 (String) = 0.992
se_a6 (String) = 0.969
deg_fg (String) = 0
solar_ele (String) = 22.1
POINT (6.0500102 49.58841)
[25]:
!ogrinfo -al "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" -where 'ogc_fid <= 5'
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: eu_y_x_select_6algorithms_fulltable
Geometry: Point
Feature Count: 5
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = GEOMETRY
id: String (0.0)
x: Real (0.0)
y: Real (0.0)
a1_95: String (0.0)
a2_95: String (0.0)
a3_95: String (0.0)
a4_95: String (0.0)
a5_95: String (0.0)
a6_95: String (0.0)
min_rh_95: String (0.0)
max_rh_95: String (0.0)
beam: String (0.0)
digital_elev: String (0.0)
elev_low: String (0.0)
qc_a1: String (0.0)
qc_a2: String (0.0)
qc_a3: String (0.0)
qc_a4: String (0.0)
qc_a5: String (0.0)
qc_a6: String (0.0)
se_a1: String (0.0)
se_a2: String (0.0)
se_a3: String (0.0)
se_a4: String (0.0)
se_a5: String (0.0)
se_a6: String (0.0)
deg_fg: String (0.0)
solar_ele: String (0.0)
OGRFeature(eu_y_x_select_6algorithms_fulltable):1
id (String) = 1
x (Real) = 6.050001
y (Real) = 49.727499
a1_95 (String) = 3139
a2_95 (String) = 3139
a3_95 (String) = 3139
a4_95 (String) = 3120
a5_95 (String) = 3139
a6_95 (String) = 3139
min_rh_95 (String) = 3120
max_rh_95 (String) = 3139
beam (String) = 5
digital_elev (String) = 4.1e+02
elev_low (String) = 383.72153
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.962
se_a2 (String) = 0.984
se_a3 (String) = 0.968
se_a4 (String) = 0.962
se_a5 (String) = 0.989
se_a6 (String) = 0.979
deg_fg (String) = 0
solar_ele (String) = 17.7
POINT (6.050001 49.727499)
OGRFeature(eu_y_x_select_6algorithms_fulltable):2
id (String) = 2
x (Real) = 6.0500017
y (Real) = 49.922155
a1_95 (String) = 1022
a2_95 (String) = 2303
a3_95 (String) = 970
a4_95 (String) = 872
a5_95 (String) = 5596
a6_95 (String) = 1524
min_rh_95 (String) = 872
max_rh_95 (String) = 5596
beam (String) = 5
digital_elev (String) = 2.9e+02
elev_low (String) = 2374.1411
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.948
se_a2 (String) = 0.990
se_a3 (String) = 0.960
se_a4 (String) = 0.948
se_a5 (String) = 0.994
se_a6 (String) = 0.980
deg_fg (String) = 0
solar_ele (String) = 43.7
POINT (6.0500017 49.922155)
OGRFeature(eu_y_x_select_6algorithms_fulltable):3
id (String) = 3
x (Real) = 6.0500021
y (Real) = 48.602377
a1_95 (String) = 380
a2_95 (String) = 1336
a3_95 (String) = 332
a4_95 (String) = 362
a5_95 (String) = 1336
a6_95 (String) = 1340
min_rh_95 (String) = 332
max_rh_95 (String) = 1340
beam (String) = 4
digital_elev (String) = 4.4e+02
elev_low (String) = 435.97781
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.947
se_a2 (String) = 0.975
se_a3 (String) = 0.956
se_a4 (String) = 0.947
se_a5 (String) = 0.981
se_a6 (String) = 0.968
deg_fg (String) = 0
solar_ele (String) = 0.2
POINT (6.0500021 48.602377)
OGRFeature(eu_y_x_select_6algorithms_fulltable):4
id (String) = 4
x (Real) = 6.0500089
y (Real) = 48.151979
a1_95 (String) = 3153
a2_95 (String) = 3142
a3_95 (String) = 3142
a4_95 (String) = 3127
a5_95 (String) = 3138
a6_95 (String) = 3142
min_rh_95 (String) = 3127
max_rh_95 (String) = 3153
beam (String) = 2
digital_elev (String) = 4.5e+02
elev_low (String) = 422.00537
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.930
se_a2 (String) = 0.970
se_a3 (String) = 0.943
se_a4 (String) = 0.930
se_a5 (String) = 0.978
se_a6 (String) = 0.962
deg_fg (String) = 0
solar_ele (String) = -14.2
POINT (6.0500089 48.151979)
OGRFeature(eu_y_x_select_6algorithms_fulltable):5
id (String) = 5
x (Real) = 6.0500102
y (Real) = 49.58841
a1_95 (String) = 666
a2_95 (String) = 4221
a3_95 (String) = 651
a4_95 (String) = 33
a5_95 (String) = 5611
a6_95 (String) = 2723
min_rh_95 (String) = 33
max_rh_95 (String) = 5611
beam (String) = 8
digital_elev (String) = 3.7e+02
elev_low (String) = 2413.7483
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.941
se_a2 (String) = 0.983
se_a3 (String) = 0.946
se_a4 (String) = 0.941
se_a5 (String) = 0.992
se_a6 (String) = 0.969
deg_fg (String) = 0
solar_ele (String) = 22.1
POINT (6.0500102 49.58841)
Using a geospatial database it is possibile to perform easily geoprocessing operations on both attributes and geometries, for instance:
[26]:
# A simple average on one of the fields for all rows, note that in most of the case, casting is automagically done...
!ogrinfo "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" -dialect SQLite -sql 'select avg(qc_a1) from eu_y_x_select_6algorithms_fullTable'
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
avg(qc_a1): Real (0.0)
OGRFeature(SELECT):0
avg(qc_a1) (Real) = 0.802981915802781
[27]:
# Extract information about the first 5 features
!ogrinfo -al "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" -where 'CastToInteger(id) <= 5'
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: eu_y_x_select_6algorithms_fulltable
Geometry: Point
Feature Count: 5
Extent: (6.050001, 47.976346) - (9.950000, 49.950000)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = GEOMETRY
id: String (0.0)
x: Real (0.0)
y: Real (0.0)
a1_95: String (0.0)
a2_95: String (0.0)
a3_95: String (0.0)
a4_95: String (0.0)
a5_95: String (0.0)
a6_95: String (0.0)
min_rh_95: String (0.0)
max_rh_95: String (0.0)
beam: String (0.0)
digital_elev: String (0.0)
elev_low: String (0.0)
qc_a1: String (0.0)
qc_a2: String (0.0)
qc_a3: String (0.0)
qc_a4: String (0.0)
qc_a5: String (0.0)
qc_a6: String (0.0)
se_a1: String (0.0)
se_a2: String (0.0)
se_a3: String (0.0)
se_a4: String (0.0)
se_a5: String (0.0)
se_a6: String (0.0)
deg_fg: String (0.0)
solar_ele: String (0.0)
OGRFeature(eu_y_x_select_6algorithms_fulltable):1
id (String) = 1
x (Real) = 6.050001
y (Real) = 49.727499
a1_95 (String) = 3139
a2_95 (String) = 3139
a3_95 (String) = 3139
a4_95 (String) = 3120
a5_95 (String) = 3139
a6_95 (String) = 3139
min_rh_95 (String) = 3120
max_rh_95 (String) = 3139
beam (String) = 5
digital_elev (String) = 4.1e+02
elev_low (String) = 383.72153
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.962
se_a2 (String) = 0.984
se_a3 (String) = 0.968
se_a4 (String) = 0.962
se_a5 (String) = 0.989
se_a6 (String) = 0.979
deg_fg (String) = 0
solar_ele (String) = 17.7
POINT (6.050001 49.727499)
OGRFeature(eu_y_x_select_6algorithms_fulltable):2
id (String) = 2
x (Real) = 6.0500017
y (Real) = 49.922155
a1_95 (String) = 1022
a2_95 (String) = 2303
a3_95 (String) = 970
a4_95 (String) = 872
a5_95 (String) = 5596
a6_95 (String) = 1524
min_rh_95 (String) = 872
max_rh_95 (String) = 5596
beam (String) = 5
digital_elev (String) = 2.9e+02
elev_low (String) = 2374.1411
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.948
se_a2 (String) = 0.990
se_a3 (String) = 0.960
se_a4 (String) = 0.948
se_a5 (String) = 0.994
se_a6 (String) = 0.980
deg_fg (String) = 0
solar_ele (String) = 43.7
POINT (6.0500017 49.922155)
OGRFeature(eu_y_x_select_6algorithms_fulltable):3
id (String) = 3
x (Real) = 6.0500021
y (Real) = 48.602377
a1_95 (String) = 380
a2_95 (String) = 1336
a3_95 (String) = 332
a4_95 (String) = 362
a5_95 (String) = 1336
a6_95 (String) = 1340
min_rh_95 (String) = 332
max_rh_95 (String) = 1340
beam (String) = 4
digital_elev (String) = 4.4e+02
elev_low (String) = 435.97781
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.947
se_a2 (String) = 0.975
se_a3 (String) = 0.956
se_a4 (String) = 0.947
se_a5 (String) = 0.981
se_a6 (String) = 0.968
deg_fg (String) = 0
solar_ele (String) = 0.2
POINT (6.0500021 48.602377)
OGRFeature(eu_y_x_select_6algorithms_fulltable):4
id (String) = 4
x (Real) = 6.0500089
y (Real) = 48.151979
a1_95 (String) = 3153
a2_95 (String) = 3142
a3_95 (String) = 3142
a4_95 (String) = 3127
a5_95 (String) = 3138
a6_95 (String) = 3142
min_rh_95 (String) = 3127
max_rh_95 (String) = 3153
beam (String) = 2
digital_elev (String) = 4.5e+02
elev_low (String) = 422.00537
qc_a1 (String) = 1
qc_a2 (String) = 1
qc_a3 (String) = 1
qc_a4 (String) = 1
qc_a5 (String) = 1
qc_a6 (String) = 1
se_a1 (String) = 0.930
se_a2 (String) = 0.970
se_a3 (String) = 0.943
se_a4 (String) = 0.930
se_a5 (String) = 0.978
se_a6 (String) = 0.962
deg_fg (String) = 0
solar_ele (String) = -14.2
POINT (6.0500089 48.151979)
OGRFeature(eu_y_x_select_6algorithms_fulltable):5
id (String) = 5
x (Real) = 6.0500102
y (Real) = 49.58841
a1_95 (String) = 666
a2_95 (String) = 4221
a3_95 (String) = 651
a4_95 (String) = 33
a5_95 (String) = 5611
a6_95 (String) = 2723
min_rh_95 (String) = 33
max_rh_95 (String) = 5611
beam (String) = 8
digital_elev (String) = 3.7e+02
elev_low (String) = 2413.7483
qc_a1 (String) = 0
qc_a2 (String) = 0
qc_a3 (String) = 0
qc_a4 (String) = 0
qc_a5 (String) = 0
qc_a6 (String) = 0
se_a1 (String) = 0.941
se_a2 (String) = 0.983
se_a3 (String) = 0.946
se_a4 (String) = 0.941
se_a5 (String) = 0.992
se_a6 (String) = 0.969
deg_fg (String) = 0
solar_ele (String) = 22.1
POINT (6.0500102 49.58841)
[28]:
# Extract average by selecting only points in a 1 degree buffer around one (X,Y), using WKT. Note that
# buffers are approximated by 30 nodes per default.
!ogrinfo "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" -dialect SQLite -sql \
"select count(*),avg(qc_a1) from eu_y_x_select_6algorithms_fullTable where ST_Intersects(geometry,ST_Buffer(ST_GeomFromText('POINT(6.0500102 49.58841)',4326),1,100))"
INFO: Open of `tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite'
using driver `SQLite' successful.
Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
count(*): Integer (0.0)
avg(qc_a1): Real (0.0)
OGRFeature(SELECT):0
count(*) (Integer) = 221745
avg(qc_a1) (Real) = 0.808672123384969
Of course, the spatialite tool can also be used instead of ogrinfo to extract information in tabular form.
[29]:
!spatialite -silent "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" \
"select count(*),avg(qc_a1) from eu_y_x_select_6algorithms_fullTable where ST_Intersects(geometry,ST_Buffer(ST_GeomFromText('POINT(6.0500102 49.58841)',4326),1,100))"
221745|0.808672123384969
Hands on
Try to compute the average, min and max of every field in the table and for all the points in a buffer of 1.0 degree buffer around the center of the data extension.
Hint: Try to find Spatialite geo functions to get the bounding box (layer extent) and its centroid, than use them to select the right points. Take your time to take confidence with Spatialite documentationhere. Once found, the query can be derived easily ;-)
[45]:
!spatialite -silent "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" "select ST_AsText(ST_Centroid(GetLayerExtent('eu_y_x_select_6algorithms_fullTable')));"
POINT(8 48.963173)
[49]:
!spatialite -silent "tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite" \
"select count(*),avg(qc_a1),min(qc_a1),max(qc_a1) from eu_y_x_select_6algorithms_fullTable where ST_Intersects(geometry,ST_Buffer((ST_Centroid(GetLayerExtent('eu_y_x_select_6algorithms_fullTable'))),1,100));"
436343|0.793962547812157|0|1
Using Python to work onto the database
The same operations can be conducted using purely Python scripts. So for instance, it is possible to dump database contents.
[34]:
import spatialite
db = spatialite.connect('tree_height/txt/eu_y_x_select_6algorithms_fullTable.splite')
cur = db.cursor()
res = cur.execute('SELECT * FROM eu_y_x_select_6algorithms_fullTable')
res
[34]:
<sqlite3.Cursor at 0x76cdc0f6bc40>
[35]:
res.fetchone()
[35]:
(1,
'1',
6.050001,
49.727499,
'3139',
'3139',
'3139',
'3120',
'3139',
'3139',
'3120',
'3139',
'5',
'4.1e+02',
'383.72153',
'1',
'1',
'1',
'1',
'1',
'1',
'0.962',
'0.984',
'0.968',
'0.962',
'0.989',
'0.979',
'0',
'17.7',
b'\x00\x01\x00\x00\x00\x00\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@|\x01\x00\x00\x00\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@\xfe')
[36]:
res = cur.execute('SELECT a1_95,a2_95,a3_95,ST_AsText(geometry),ST_X(geometry),ST_Y(geometry) FROM eu_y_x_select_6algorithms_fullTable')
res.fetchone()
[36]:
('3139', '3139', '3139', 'POINT(6.050001 49.727499)', 6.050001, 49.727499)
[37]:
ls = res.fetchall()
ls
[37]:
[('1022', '2303', '970', 'POINT(6.050002 49.922155)', 6.0500017, 49.922155),
('380', '1336', '332', 'POINT(6.050002 48.602377)', 6.0500021, 48.602377),
('3153', '3142', '3142', 'POINT(6.050009 48.151979)', 6.0500089, 48.151979),
('666', '4221', '651', 'POINT(6.05001 49.58841)', 6.0500102, 49.58841),
('787', '1179', '1187', 'POINT(6.050014 48.608456)', 6.0500143, 48.608456),
('2952', '2933', '2937', 'POINT(6.050017 48.571401)', 6.0500165, 48.571401),
('3303', '3288', '3296', 'POINT(6.050019 49.921613)', 6.0500189, 49.921613),
('1637', '1614', '1618', 'POINT(6.05002 48.822645)', 6.0500201, 48.822645),
('1415', '1393', '1396', 'POINT(6.050024 49.847522)', 6.0500238, 49.847522),
('1194', '1183', '1186', 'POINT(6.050027 49.661237)', 6.0500266, 49.661237),
('2762', '2736', '2740', 'POINT(6.050039 47.995344)', 6.0500389, 47.995344),
('437', '3082', '1490', 'POINT(6.050041 49.573607)', 6.050041, 49.573607),
('2772', '2776', '2780', 'POINT(6.050043 48.144718)', 6.0500429, 48.144718),
('1398', '2505', '2509', 'POINT(6.050046 49.865317)', 6.0500458, 49.865317),
('984', '943', '947', 'POINT(6.050048 49.05002)', 6.0500477, 49.05002),
('3362', '3332', '3336', 'POINT(6.050049 48.391359)', 6.0500487, 48.391359),
('1602', '1591', '1594', 'POINT(6.050049 49.243593)', 6.0500492, 49.243593),
('533', '529', '529', 'POINT(6.050053 49.877876)', 6.0500529, 49.877876),
('2330', '2330', '2334', 'POINT(6.050053 49.457731)', 6.0500533, 49.457731),
('224', '168', '168', 'POINT(6.050054 49.450656)', 6.0500544, 49.450656),
('1103', '1081', '1089', 'POINT(6.050061 48.126619)', 6.0500607, 48.126619),
('1796', '1736', '1740', 'POINT(6.050063 48.014352)', 6.0500632, 48.014352),
('479', '1197', '943', 'POINT(6.050073 49.346603)', 6.0500727, 49.346603),
('1208', '1205', '1208', 'POINT(6.05007 49.871995)', 6.05007, 49.871995),
('408', '374', '378', 'POINT(6.050079 48.276936)', 6.0500789, 48.276936),
('3906', '3925', '3929', 'POINT(6.050083 49.281439)', 6.0500833, 49.281439),
('2393', '2371', '2374', 'POINT(6.050084 49.694701)', 6.0500838, 49.694701),
('378', '348', '351', 'POINT(6.050089 49.941493)', 6.0500886, 49.941493),
('2701', '2704', '2708', 'POINT(6.0501 49.286244)', 6.0501004, 49.286244),
('262', '250', '250', 'POINT(6.050104 48.977114)', 6.0501038, 48.977114),
('213', '172', '176', 'POINT(6.050104 49.23368)', 6.0501038, 49.23368),
('1941', '2008', '2020', 'POINT(6.050105 48.13987)', 6.0501048, 48.13987),
('343', '328', '328', 'POINT(6.050119 49.737194)', 6.0501193, 49.737194),
('1763', '1763', '1767', 'POINT(6.050119 49.92861)', 6.050119, 49.92861),
('172', '161', '161', 'POINT(6.05012 49.22903)', 6.0501204, 49.22903),
('3603', '3603', '3606', 'POINT(6.050122 49.72948)', 6.0501219, 49.72948),
('351', '343', '343', 'POINT(6.050123 49.860762)', 6.0501225, 49.860762),
('1507', '1544', '1548', 'POINT(6.050124 48.266277)', 6.050124, 48.266277),
('2923', '2905', '2905', 'POINT(6.05012 48.058094)', 6.05012, 48.058094),
('262', '1217', '1217', 'POINT(6.050127 48.660395)', 6.0501265, 48.660395),
('377', '1513', '1136', 'POINT(6.050128 49.721407)', 6.0501281, 49.721407),
('449', '1067', '419', 'POINT(6.05013 48.678628)', 6.0501302, 48.678628),
('1366', '1363', '1366', 'POINT(6.050132 49.765778)', 6.0501317, 49.765778),
('3273', '2460', '3311', 'POINT(6.050133 49.043002)', 6.0501325, 49.043002),
('2884', '2873', '2873', 'POINT(6.050139 49.574314)', 6.0501387, 49.574314),
('355', '1730', '336', 'POINT(6.050143 49.641136)', 6.0501434, 49.641136),
('2683', '2687', '2687', 'POINT(6.050146 49.724384)', 6.0501464, 49.724384),
('2982', '2964', '2975', 'POINT(6.050147 48.143707)', 6.0501468, 48.143707),
('2057', '2398', '2409', 'POINT(6.050147 48.739394)', 6.0501468, 48.739394),
('414', '440', '444', 'POINT(6.05015 49.946353)', 6.0501504, 49.946353),
('385', '378', '378', 'POINT(6.050151 49.771607)', 6.0501509, 49.771607),
('1811', '1785', '1789', 'POINT(6.050155 48.030541)', 6.0501546, 48.030541),
('179', '134', '142', 'POINT(6.05016 48.77701)', 6.0501601, 48.77701),
('347', '313', '321', 'POINT(6.050163 49.940555)', 6.0501626, 49.940555),
('359', '1011', '1015', 'POINT(6.050164 49.832442)', 6.0501643, 49.832442),
('3266', '3232', '3232', 'POINT(6.050167 49.815488)', 6.0501674, 49.815488),
('2564', '2564', '2568', 'POINT(6.050168 49.748087)', 6.0501675, 49.748087),
('291', '770', '774', 'POINT(6.050175 48.049679)', 6.0501746, 48.049679),
('2760', '2749', '2753', 'POINT(6.050188 49.821238)', 6.0501877, 49.821238),
('2963', '2967', '2970', 'POINT(6.050194 49.803259)', 6.0501936, 49.803259),
('1397', '5889', '1416', 'POINT(6.050195 48.514206)', 6.0501953, 48.514206),
('1838', '1865', '1876', 'POINT(6.050196 49.538832)', 6.0501957, 49.538832),
('1258', '1273', '1273', 'POINT(6.050196 49.074041)', 6.0501958, 49.074041),
('1006', '980', '991', 'POINT(6.050201 49.834817)', 6.0502009, 49.834817),
('1853', '1812', '1812', 'POINT(6.050206 48.762202)', 6.0502058, 48.762202),
('1924', '1924', '1924', 'POINT(6.050208 49.767374)', 6.0502075, 49.767374),
('2486', '2475', '2475', 'POINT(6.050214 49.293145)', 6.0502142, 49.293145),
('632', '1089', '1093', 'POINT(6.050215 49.947326)', 6.0502154, 49.947326),
('1830', '1826', '1830', 'POINT(6.050226 47.989116)', 6.0502255, 47.989116),
('744', '718', '722', 'POINT(6.050234 48.091576)', 6.050234, 48.091576),
('2693', '2648', '2648', 'POINT(6.050236 48.156663)', 6.0502359, 48.156663),
('1408', '1869', '1872', 'POINT(6.050236 49.83438)', 6.0502364, 49.83438),
('321', '284', '287', 'POINT(6.050247 48.756247)', 6.0502466, 48.756247),
('876', '891', '910', 'POINT(6.05025 49.790673)', 6.0502498, 49.790673),
('228', '191', '194', 'POINT(6.050253 49.235474)', 6.0502527, 49.235474),
('1444', '1422', '1426', 'POINT(6.050253 49.017677)', 6.0502531, 49.017677),
('2659', '2648', '2648', 'POINT(6.050255 48.245403)', 6.0502546, 48.245403),
('2146', '2153', '2153', 'POINT(6.050256 48.136725)', 6.0502559, 48.136725),
('501', '939', '943', 'POINT(6.050257 49.457015)', 6.0502569, 49.457015),
('2976', '2953', '2957', 'POINT(6.050263 48.269253)', 6.0502628, 48.269253),
('2752', '2789', '2793', 'POINT(6.050266 49.586915)', 6.0502663, 49.586915),
('471', '479', '482', 'POINT(6.050268 49.43227)', 6.0502682, 49.43227),
('784', '1012', '765', 'POINT(6.05027 49.745969)', 6.0502702, 49.745969),
('474', '1935', '463', 'POINT(6.050271 49.07236)', 6.050271, 49.07236),
('3141', '3118', '3118', 'POINT(6.050274 49.6346)', 6.0502743, 49.6346),
('2453', '2460', '2468', 'POINT(6.050275 49.739494)', 6.0502749, 49.739494),
('1681', '1666', '1674', 'POINT(6.050278 48.155905)', 6.050278, 48.155905),
('495', '1609', '1620', 'POINT(6.050284 48.6599)', 6.0502838, 48.6599),
('355', '3344', '355', 'POINT(6.050284 48.151159)', 6.050284, 48.151159),
('582', '1142', '1146', 'POINT(6.050286 48.613629)', 6.0502856, 48.613629),
('194', '161', '164', 'POINT(6.050291 49.554001)', 6.0502911, 49.554001),
('1621', '3385', '1613', 'POINT(6.050295 49.848342)', 6.0502949, 49.848342),
('2689', '2682', '2682', 'POINT(6.050304 49.568564)', 6.0503044, 49.568564),
('1819', '1834', '1842', 'POINT(6.050306 48.265646)', 6.0503059, 48.265646),
('2516', '2792', '2501', 'POINT(6.050308 49.638514)', 6.0503082, 49.638514),
('1760', '1764', '1768', 'POINT(6.050313 49.474377)', 6.0503131, 49.474377),
('265', '973', '239', 'POINT(6.05032 48.657586)', 6.0503196, 48.657586),
('2184', '2195', '2202', 'POINT(6.050321 49.359121)', 6.0503211, 49.359121),
('984', '1650', '1197', 'POINT(6.050324 48.008733)', 6.0503242, 48.008733),
('1400', '1382', '1400', 'POINT(6.050326 48.66191)', 6.0503259, 48.66191),
('2188', '2196', '2199', 'POINT(6.050327 48.158439)', 6.0503269, 48.158439),
('448', '437', '437', 'POINT(6.050327 49.708249)', 6.050327, 49.708249),
('637', '693', '704', 'POINT(6.050329 48.602245)', 6.0503285, 48.602245),
('227', '198', '201', 'POINT(6.050329 49.23037)', 6.0503291, 49.23037),
('2613', '2602', '2602', 'POINT(6.050335 49.287225)', 6.0503345, 49.287225),
('467', '1098', '1106', 'POINT(6.050335 49.55192)', 6.0503351, 49.55192),
('901', '2030', '934', 'POINT(6.050336 49.568882)', 6.0503355, 49.568882),
('1209', '1179', '1187', 'POINT(6.050348 48.997161)', 6.0503479, 48.997161),
('2816', '2801', '2805', 'POINT(6.050352 49.796402)', 6.0503515, 49.796402),
('277', '247', '247', 'POINT(6.050353 48.644042)', 6.0503526, 48.644042),
('3329', '3284', '3284', 'POINT(6.050354 49.280153)', 6.0503535, 49.280153),
('1406', '1402', '1402', 'POINT(6.05035 49.871558)', 6.05035, 49.871558),
('1520', '1520', '1524', 'POINT(6.050356 49.473944)', 6.0503555, 49.473944),
('791', '1651', '791', 'POINT(6.05036 48.13253)', 6.0503604, 48.13253),
('1972', '1961', '1965', 'POINT(6.050361 48.113711)', 6.0503606, 48.113711),
('653', '668', '672', 'POINT(6.050362 49.738684)', 6.0503621, 49.738684),
('1246', '2646', '1208', 'POINT(6.050367 48.020758)', 6.0503674, 48.020758),
('575', '1011', '1038', 'POINT(6.050369 49.727861)', 6.0503687, 49.727861),
('1347', '1321', '1325', 'POINT(6.050369 49.349842)', 6.0503694, 49.349842),
('2815', '2822', '2826', 'POINT(6.050372 48.024239)', 6.0503718, 48.024239),
('2006', '1998', '2002', 'POINT(6.050375 48.775438)', 6.0503753, 48.775438),
('1375', '1416', '1423', 'POINT(6.050375 49.654063)', 6.050375, 49.654063),
('917', '1479', '1490', 'POINT(6.05038 49.244936)', 6.0503797, 49.244936),
('1231', '1202', '1209', 'POINT(6.050381 49.063186)', 6.0503805, 49.063186),
('489', '780', '791', 'POINT(6.050384 49.884913)', 6.0503838, 49.884913),
('1307', '2532', '1150', 'POINT(6.05039 49.903842)', 6.0503903, 49.903842),
('415', '2454', '841', 'POINT(6.050392 48.146307)', 6.050392, 48.146307),
('2634', '2656', '2660', 'POINT(6.050394 48.037447)', 6.0503938, 48.037447),
('2390', '2352', '2352', 'POINT(6.050394 48.816719)', 6.0503944, 48.816719),
('2637', '2629', '2633', 'POINT(6.050397 49.468548)', 6.0503972, 49.468548),
('243', '202', '202', 'POINT(6.050398 48.096616)', 6.0503976, 48.096616),
('1445', '2033', '2033', 'POINT(6.050398 49.015717)', 6.0503976, 49.015717),
('650', '1842', '1850', 'POINT(6.050403 49.560612)', 6.0504034, 49.560612),
('217', '179', '179', 'POINT(6.050404 48.064458)', 6.0504043, 48.064458),
('2048', '2041', '2041', 'POINT(6.050405 49.76727)', 6.0504051, 49.76727),
('2264', '2268', '2268', 'POINT(6.050406 49.651123)', 6.0504055, 49.651123),
('1297', '1308', '1312', 'POINT(6.050406 49.804685)', 6.0504064, 49.804685),
('887', '925', '928', 'POINT(6.050409 49.323616)', 6.0504093, 49.323616),
('1703', '1703', '1703', 'POINT(6.050411 49.230699)', 6.0504109, 49.230699),
('2408', '2393', '2397', 'POINT(6.050412 49.287399)', 6.0504115, 49.287399),
('1394', '2883', '1503', 'POINT(6.050412 49.865704)', 6.0504122, 49.865704),
('1945', '1930', '1934', 'POINT(6.050417 48.110302)', 6.0504165, 48.110302),
('2350', '2328', '2332', 'POINT(6.050418 48.247739)', 6.0504177, 48.247739),
('2272', '2258', '2258', 'POINT(6.050418 49.464828)', 6.0504177, 49.464828),
('2449', '2430', '2434', 'POINT(6.050423 49.752892)', 6.0504232, 49.752892),
('2679', '2657', '2660', 'POINT(6.050426 48.05615)', 6.0504263, 48.05615),
('2588', '3678', '2595', 'POINT(6.050431 49.701037)', 6.050431, 49.701037),
('1505', '1475', '1479', 'POINT(6.050432 49.934463)', 6.0504316, 49.934463),
('3251', '3229', '3229', 'POINT(6.050434 48.149558)', 6.0504339, 48.149558),
('1936', '1932', '1936', 'POINT(6.050434 49.229568)', 6.0504343, 49.229568),
('302', '1366', '1366', 'POINT(6.050436 48.589054)', 6.0504357, 48.589054),
('2264', '2238', '2242', 'POINT(6.050439 49.742842)', 6.0504388, 49.742842),
('318', '310', '314', 'POINT(6.05044 49.543152)', 6.0504396, 49.543152),
('2532', '2532', '2535', 'POINT(6.050442 49.740129)', 6.0504416, 49.740129),
('1239', '1243', '1247', 'POINT(6.050443 49.330718)', 6.0504431, 49.330718),
('2352', '2337', '2341', 'POINT(6.050444 49.745857)', 6.0504442, 49.745857),
('1893', '1923', '1927', 'POINT(6.050449 49.097484)', 6.0504487, 49.097484),
('718', '707', '711', 'POINT(6.050451 49.449742)', 6.0504506, 49.449742),
('2966', '2963', '2966', 'POINT(6.050452 49.724607)', 6.0504518, 49.724607),
('201', '168', '168', 'POINT(6.050453 49.230591)', 6.0504532, 49.230591),
('748', '1407', '977', 'POINT(6.050455 48.113831)', 6.0504551, 48.113831),
('3364', '3356', '3360', 'POINT(6.050459 48.150059)', 6.0504592, 48.150059),
('2198', '2194', '2198', 'POINT(6.050465 49.288935)', 6.050465, 49.288935),
('1072', '1154', '1158', 'POINT(6.050467 49.663969)', 6.0504665, 49.663969),
('350', '331', '331', 'POINT(6.050468 49.73942)', 6.0504675, 49.73942),
('2112', '2120', '2124', 'POINT(6.05047 48.814112)', 6.05047, 48.814112),
('2996', '2974', '2978', 'POINT(6.050475 49.731163)', 6.0504753, 49.731163),
('370', '1021', '407', 'POINT(6.050476 49.661026)', 6.0504759, 49.661026),
('3513', '3506', '3524', 'POINT(6.050482 49.879106)', 6.0504824, 49.879106),
('2543', '3015', '3019', 'POINT(6.050488 49.432164)', 6.0504882, 49.432164),
('413', '387', '391', 'POINT(6.050495 49.852568)', 6.0504949, 49.852568),
('2050', '2046', '2054', 'POINT(6.050496 48.685926)', 6.0504957, 48.685926),
('436', '399', '407', 'POINT(6.050498 49.94672)', 6.0504982, 49.94672),
('426', '1111', '1118', 'POINT(6.050501 48.098055)', 6.0505013, 48.098055),
('2531', '3173', '2539', 'POINT(6.050508 49.835373)', 6.0505077, 49.835373),
('258', '232', '235', 'POINT(6.05051 49.22779)', 6.0505098, 49.22779),
('1232', '4557', '1198', 'POINT(6.050511 48.641439)', 6.0505112, 48.641439),
('1814', '2494', '2501', 'POINT(6.050512 49.742057)', 6.0505117, 49.742057),
('198', '149', '149', 'POINT(6.050516 48.071224)', 6.0505163, 48.071224),
('2953', '3558', '3566', 'POINT(6.05052 49.723497)', 6.0505197, 49.723497),
('2532', '2524', '2524', 'POINT(6.050527 49.809097)', 6.0505268, 49.809097),
('520', '1962', '1966', 'POINT(6.050527 48.638358)', 6.0505273, 48.638358),
('943', '1104', '1108', 'POINT(6.050529 49.037252)', 6.0505289, 49.037252),
('370', '396', '396', 'POINT(6.05053 49.704356)', 6.0505295, 49.704356),
('644', '644', '647', 'POINT(6.050533 49.443821)', 6.0505328, 49.443821),
('2442', '2464', '2468', 'POINT(6.050545 49.371889)', 6.0505445, 49.371889),
('4599', '4554', '4562', 'POINT(6.050552 49.728155)', 6.0505518, 49.728155),
('834', '838', '845', 'POINT(6.050557 48.084503)', 6.0505566, 48.084503),
('2175', '4765', '2190', 'POINT(6.050557 48.513279)', 6.0505567, 48.513279),
('2232', '2229', '2247', 'POINT(6.050561 49.816044)', 6.0505611, 49.816044),
('1987', '404', '1991', 'POINT(6.050576 49.322446)', 6.0505758, 49.322446),
('1923', '1908', '1912', 'POINT(6.050577 49.76693)', 6.0505772, 49.76693),
('606', '595', '602', 'POINT(6.050578 49.267928)', 6.0505776, 49.267928),
('4306', '4283', '4298', 'POINT(6.050579 48.24596)', 6.0505788, 48.24596),
('2467', '2456', '2459', 'POINT(6.050581 48.568742)', 6.0505807, 48.568742),
('4737', '5208', '5250', 'POINT(6.050581 48.252107)', 6.0505811, 48.252107),
('3046', '3038', '3042', 'POINT(6.050582 48.133117)', 6.0505817, 48.133117),
('217', '224', '232', 'POINT(6.050595 49.559782)', 6.0505945, 49.559782),
('1734', '2811', '1794', 'POINT(6.050597 49.56296)', 6.0505968, 49.56296),
('2175', '2156', '2160', 'POINT(6.050602 48.017664)', 6.0506023, 48.017664),
('988', '2986', '842', 'POINT(6.050605 48.057634)', 6.0506054, 48.057634),
('3009', '2983', '2983', 'POINT(6.050609 48.078618)', 6.0506092, 48.078618),
('1299', '1262', '1265', 'POINT(6.050619 49.05607)', 6.0506187, 49.05607),
('2555', '2551', '2555', 'POINT(6.050629 49.742247)', 6.0506289, 49.742247),
('359', '348', '348', 'POINT(6.050631 49.42633)', 6.0506309, 49.42633),
('2610', '2924', '2924', 'POINT(6.050632 48.564232)', 6.0506321, 48.564232),
('2907', '2884', '2888', 'POINT(6.050634 49.463705)', 6.050634, 49.463705),
('3529', '3495', '3495', 'POINT(6.050642 49.644647)', 6.0506421, 49.644647),
('2097', '2093', '2097', 'POINT(6.050645 49.731665)', 6.0506447, 49.731665),
('3970', '3985', '3992', 'POINT(6.050645 49.916016)', 6.0506447, 49.916016),
('306', '492', '496', 'POINT(6.050645 49.745735)', 6.0506448, 49.745735),
('2564', '2568', '2576', 'POINT(6.050645 49.468028)', 6.0506451, 49.468028),
('2473', '2473', '2473', 'POINT(6.050654 48.04395)', 6.0506544, 48.04395),
('2582', '2567', '2579', 'POINT(6.050655 49.725767)', 6.0506549, 49.725767),
('2508', '2504', '2508', 'POINT(6.050662 49.262929)', 6.0506618, 49.262929),
('2755', '2740', '2740', 'POINT(6.050666 48.120077)', 6.0506664, 48.120077),
('1042', '1024', '1027', 'POINT(6.050668 48.125864)', 6.0506684, 48.125864),
('411', '374', '374', 'POINT(6.050669 49.430866)', 6.0506693, 49.430866),
('1917', '1924', '1932', 'POINT(6.050671 49.69843)', 6.0506706, 49.69843),
('2705', '2705', '2705', 'POINT(6.050673 49.740436)', 6.0506727, 49.740436),
('1487', '1509', '1517', 'POINT(6.050676 49.5548)', 6.0506763, 49.5548),
('1868', '4872', '1902', 'POINT(6.050677 48.64731)', 6.0506765, 48.64731),
('1999', '2011', '2011', 'POINT(6.050677 49.227925)', 6.0506767, 49.227925),
('2594', '2605', '2613', 'POINT(6.050679 49.463065)', 6.0506794, 49.463065),
('302', '1073', '291', 'POINT(6.05068 48.464655)', 6.0506795, 48.464655),
('2070', '2066', '2077', 'POINT(6.050681 49.431922)', 6.0506805, 49.431922),
('2378', '2389', '2389', 'POINT(6.050687 49.628641)', 6.0506874, 49.628641),
('1242', '1197', '1197', 'POINT(6.050692 48.002562)', 6.0506915, 48.002562),
('2983', '2990', '2990', 'POINT(6.050696 49.647402)', 6.050696, 49.647402),
('359', '333', '337', 'POINT(6.050698 49.444967)', 6.0506976, 49.444967),
('2606', '2602', '2602', 'POINT(6.050701 49.459032)', 6.0507008, 49.459032),
('2746', '2746', '2753', 'POINT(6.050703 49.660287)', 6.0507026, 49.660287),
('2476', '3034', '3038', 'POINT(6.050703 49.372211)', 6.0507027, 49.372211),
('1876', '1865', '1873', 'POINT(6.050704 49.701684)', 6.0507038, 49.701684),
('739', '2488', '728', 'POINT(6.050704 49.066623)', 6.0507042, 49.066623),
('1530', '2963', '1533', 'POINT(6.050705 48.152197)', 6.0507051, 48.152197),
('321', '306', '310', 'POINT(6.050706 49.940682)', 6.0507057, 49.940682),
('1505', '1801', '1805', 'POINT(6.050708 48.651939)', 6.0507083, 48.651939),
('1359', '2648', '1378', 'POINT(6.050709 49.345502)', 6.0507092, 49.345502),
('433', '421', '425', 'POINT(6.050711 48.602562)', 6.0507112, 48.602562),
('2497', '2475', '2486', 'POINT(6.050712 49.274347)', 6.0507121, 49.274347),
('378', '355', '359', 'POINT(6.050712 49.06919)', 6.0507124, 49.06919),
('228', '194', '194', 'POINT(6.050714 49.847824)', 6.0507141, 49.847824),
('2629', '2607', '2614', 'POINT(6.050716 49.5874)', 6.0507158, 49.5874),
('3142', '3116', '3120', 'POINT(6.050716 49.702348)', 6.0507164, 49.702348),
('2737', '2734', '2749', 'POINT(6.050728 49.549586)', 6.0507282, 49.549586),
('2407', '2380', '2384', 'POINT(6.050732 47.995561)', 6.0507317, 47.995561),
('1168', '1179', '1183', 'POINT(6.050733 48.608645)', 6.0507333, 48.608645),
('3168', '3134', '3134', 'POINT(6.050735 48.571591)', 6.0507352, 48.571591),
('3038', '3064', '3067', 'POINT(6.050737 48.144937)', 6.0507365, 48.144937),
('673', '3354', '2176', 'POINT(6.050737 49.705881)', 6.0507371, 49.705881),
('625', '1119', '1123', 'POINT(6.050738 49.032513)', 6.0507382, 49.032513),
('3494', '3468', '3479', 'POINT(6.050743 49.727685)', 6.0507432, 49.727685),
('2200', '2188', '2188', 'POINT(6.050745 49.721128)', 6.0507451, 49.721128),
('2393', '2385', '2389', 'POINT(6.050746 49.79862)', 6.0507459, 49.79862),
('1753', '1723', '1726', 'POINT(6.050747 48.822828)', 6.0507473, 48.822828),
('1932', '1928', '1928', 'POINT(6.050748 49.021706)', 6.0507475, 49.021706),
('2531', '2793', '2801', 'POINT(6.050754 48.995569)', 6.0507539, 48.995569),
('419', '1456', '1134', 'POINT(6.050755 48.014565)', 6.0507546, 48.014565),
('804', '827', '834', 'POINT(6.050755 48.126835)', 6.0507552, 48.126835),
('586', '2152', '803', 'POINT(6.050756 49.557999)', 6.0507555, 49.557999),
('430', '2190', '453', 'POINT(6.050761 49.861421)', 6.0507609, 49.861421),
('1527', '2348', '2355', 'POINT(6.050764 48.391553)', 6.0507642, 48.391553),
('2209', '2202', '2205', 'POINT(6.050766 49.782379)', 6.0507657, 49.782379),
('606', '8334', '614', 'POINT(6.05077 49.588571)', 6.0507695, 49.588571),
('194', '157', '164', 'POINT(6.050776 49.567724)', 6.0507764, 49.567724),
('250', '205', '205', 'POINT(6.050776 49.236674)', 6.050776, 49.236674),
('1347', '1325', '1329', 'POINT(6.050778 49.661394)', 6.0507781, 49.661394),
('853', '1752', '1756', 'POINT(6.05078 49.050196)', 6.0507796, 49.050196),
('820', '786', '812', 'POINT(6.050785 49.921762)', 6.0507852, 49.921762),
('1508', '1501', '1501', 'POINT(6.050788 49.243763)', 6.0507877, 49.243763),
('273', '262', '265', 'POINT(6.050788 48.277132)', 6.0507882, 48.277132),
('2630', '2607', '2611', 'POINT(6.050796 49.573769)', 6.0507963, 49.573769),
('2502', '2495', '2498', 'POINT(6.050797 49.457895)', 6.0507974, 49.457895),
('194', '141', '141', 'POINT(6.0508 49.567829)', 6.0507997, 49.567829),
('1243', '1225', '1255', 'POINT(6.050804 49.450821)', 6.0508041, 49.450821),
('2860', '2837', '2841', 'POINT(6.050812 48.058309)', 6.0508118, 48.058309),
('370', '1291', '580', 'POINT(6.050815 49.346772)', 6.0508153, 49.346772),
('2174', '2182', '2182', 'POINT(6.050817 49.878027)', 6.0508172, 49.878027),
('648', '1247', '1247', 'POINT(6.050821 48.630749)', 6.0508213, 48.630749),
('3281', '3277', '3281', 'POINT(6.050827 49.281607)', 6.0508271, 49.281607),
('1470', '1541', '1544', 'POINT(6.050829 48.266481)', 6.0508293, 48.266481),
('1993', '2341', '2345', 'POINT(6.050833 48.977293)', 6.0508334, 48.977293),
('2524', '2509', '2513', 'POINT(6.050837 49.694856)', 6.0508371, 49.694856),
('172', '153', '157', 'POINT(6.05084 49.286413)', 6.0508402, 49.286413),
('284', '273', '277', 'POINT(6.050841 49.233851)', 6.050841, 49.233851),
('2930', '2904', '2908', 'POINT(6.050844 48.143925)', 6.0508438, 48.143925),
('543', '513', '520', 'POINT(6.050845 48.660583)', 6.0508445, 48.660583),
('1890', '1894', '1897', 'POINT(6.050845 48.030761)', 6.0508453, 48.030761),
('2368', '2371', '2379', 'POINT(6.0508 48.140088)', 6.0508, 48.140088),
('1404', '1400', '1404', 'POINT(6.050851 48.678816)', 6.0508507, 48.678816),
('164', '149', '157', 'POINT(6.050858 49.229201)', 6.0508577, 49.229201),
('1158', '1139', '1147', 'POINT(6.05086 49.737366)', 6.0508596, 49.737366),
('3191', '3169', '3173', 'POINT(6.050862 49.729637)', 6.0508624, 49.729637),
('1593', '2031', '2039', 'POINT(6.050864 48.739578)', 6.0508643, 48.739578),
('1267', '1252', '1252', 'POINT(6.050868 49.86548)', 6.0508678, 49.86548),
('3741', '3715', '3719', 'POINT(6.050871 49.7216)', 6.0508708, 49.7216),
('1378', '1385', '1385', 'POINT(6.050873 49.928757)', 6.0508734, 49.928757),
('287', '257', '261', 'POINT(6.05088 49.641305)', 6.0508796, 49.641305),
('1524', '1520', '1528', 'POINT(6.050882 49.324965)', 6.050882, 49.324965),
('1814', '1802', '1806', 'POINT(6.050884 48.777194)', 6.0508839, 48.777194),
('2982', '2960', '2960', 'POINT(6.050887 49.72456)', 6.0508871, 49.72456),
('277', '254', '254', 'POINT(6.050889 49.765933)', 6.0508885, 49.765933),
('2382', '2389', '2393', 'POINT(6.050889 49.574472)', 6.0508894, 49.574472),
('2482', '2908', '2916', 'POINT(6.050898 49.860916)', 6.0508975, 49.860916),
('516', '516', '516', 'POINT(6.050909 49.771761)', 6.0509087, 49.771761),
('239', '194', '194', 'POINT(6.050909 48.065838)', 6.0509092, 48.065838),
('3026', '3023', '3026', 'POINT(6.050912 49.665948)', 6.0509118, 49.665948),
('868', '883', '890', 'POINT(6.050917 48.126862)', 6.0509165, 48.126862),
('298', '1094', '235', 'POINT(6.050918 49.946501)', 6.0509175, 49.946501),
('2652', '2644', '2648', 'POINT(6.050921 49.832596)', 6.0509208, 49.832596),
('381', '340', '340', 'POINT(6.050922 48.091791)', 6.0509215, 48.091791),
('313', '1898', '1898', 'POINT(6.050924 48.762385)', 6.0509242, 48.762385),
('1883', '1894', '1898', 'POINT(6.050929 49.074218)', 6.0509289, 49.074218),
('2331', '2308', '2312', 'POINT(6.050931 48.15688)', 6.0509314, 48.15688),
('2880', '2876', '2876', 'POINT(6.050931 49.815635)', 6.050931, 49.815635),
('310', '1490', '790', 'POINT(6.050939 49.538989)', 6.0509387, 49.538989),
('313', '1367', '309', 'POINT(6.050943 48.247823)', 6.0509431, 48.247823),
('264', '241', '245', 'POINT(6.050947 48.700699)', 6.050947, 48.700699),
('298', '2114', '447', 'POINT(6.050951 49.767557)', 6.0509509, 49.767557),
('1228', '1213', '1213', 'POINT(6.050953 49.803413)', 6.0509527, 49.803413),
('2067', '2044', '2048', 'POINT(6.050955 49.293314)', 6.050955, 49.293314),
('351', '298', '313', 'POINT(6.050964 48.151367)', 6.0509635, 48.151367),
('3022', '3022', '3026', 'POINT(6.050964 49.834969)', 6.0509642, 49.834969),
('1081', '1089', '1092', 'POINT(6.050968 49.33099)', 6.0509676, 49.33099),
('284', '261', '261', 'POINT(6.050968 48.756431)', 6.0509678, 48.756431),
('1631', '1609', '1616', 'POINT(6.050968 48.591282)', 6.0509679, 48.591282),
('544', '544', '548', 'POINT(6.050972 49.892098)', 6.0509719, 49.892098),
('284', '1857', '1345', 'POINT(6.050972 48.156127)', 6.0509722, 48.156127),
('295', '303', '307', 'POINT(6.050973 49.543196)', 6.0509733, 49.543196),
('1733', '1681', '1685', 'POINT(6.050976 49.947474)', 6.050976, 49.947474),
('2576', '2553', '2557', 'POINT(6.050978 49.274002)', 6.0509784, 49.274002),
('1755', '1747', '1759', 'POINT(6.050984 49.017856)', 6.050984, 49.017856),
('217', '191', '191', 'POINT(6.050988 49.235643)', 6.0509881, 49.235643),
('280', '1116', '258', 'POINT(6.050992 49.834522)', 6.0509917, 49.834522),
('2134', '2130', '2134', 'POINT(6.050997 49.457178)', 6.0509967, 49.457178),
('265', '235', '239', 'POINT(6.050998 49.234005)', 6.0509981, 49.234005),
('1337', '1277', '1281', 'POINT(6.051002 48.613817)', 6.0510015, 48.613817),
('2056', '2042', '2686', 'POINT(6.051002 48.660113)', 6.0510021, 48.660113),
('415', '430', '434', 'POINT(6.051011 49.432435)', 6.0510108, 49.432435),
('605', '571', '575', 'POINT(6.051013 49.746144)', 6.0510125, 49.746144),
('242', '2737', '1355', 'POINT(6.051013 49.587074)', 6.0510127, 49.587074),
('3133', '3115', '3118', 'POINT(6.051014 49.634775)', 6.0510136, 49.634775),
('1879', '1864', '1868', 'POINT(6.051014 48.265844)', 6.051014, 48.265844),
('1512', '1515', '1515', 'POINT(6.051017 48.008947)', 6.0510172, 48.008947),
('821', '814', '814', 'POINT(6.051017 49.739666)', 6.0510172, 49.739666),
('516', '1099', '1103', 'POINT(6.05102 48.158651)', 6.0510201, 48.158651),
('298', '272', '276', 'POINT(6.051032 49.745383)', 6.0510315, 49.745383),
('2017', '2025', '2029', 'POINT(6.051039 49.927944)', 6.0510392, 49.927944),
('378', '359', '363', 'POINT(6.05104 48.988392)', 6.0510401, 48.988392),
('277', '299', '303', 'POINT(6.051041 49.554162)', 6.0510407, 49.554162),
('164', '138', '142', 'POINT(6.051044 48.096856)', 6.0510443, 48.096856),
('359', '2078', '531', 'POINT(6.051045 49.84849)', 6.0510449, 49.84849),
('3035', '3035', '3035', 'POINT(6.051046 49.638687)', 6.0510461, 49.638687),
('570', '622', '626', 'POINT(6.051049 48.602432)', 6.0510493, 48.602432),
('288', '284', '284', 'POINT(6.051052 48.657756)', 6.0510515, 48.657756),
('926', '1292', '1300', 'POINT(6.051053 48.132748)', 6.0510528, 48.132748),
('2223', '2212', '2216', 'POINT(6.051055 48.113926)', 6.0510548, 48.113926),
('269', '1475', '1887', 'POINT(6.051056 47.976346)', 6.0510561, 47.976346),
('790', '1236', '1243', 'POINT(6.051057 48.662082)', 6.0510569, 48.662082),
('2938', '2941', '2945', 'POINT(6.051059 48.020974)', 6.0510592, 48.020974),
('2863', '2867', '2867', 'POINT(6.051062 48.024458)', 6.0510623, 48.024458),
('2034', '2015', '2019', 'POINT(6.051063 49.474542)', 6.0510629, 49.474542),
('1569', '1561', '1565', 'POINT(6.051064 49.230541)', 6.0510635, 49.230541),
('668', '706', '710', 'POINT(6.05107 49.708424)', 6.0510698, 49.708424),
('2164', '2156', '2160', 'POINT(6.051074 49.287394)', 6.0510737, 49.287394),
('261', '2058', '254', 'POINT(6.051078 48.775616)', 6.0510782, 48.775616),
('2332', '3190', '2389', 'POINT(6.051079 48.997339)', 6.0510786, 48.997339),
('1020', '1727', '2483', 'POINT(6.05108 48.67011)', 6.0510802, 48.67011),
('875', '1837', '1107', 'POINT(6.051081 48.037663)', 6.0510814, 48.037663),
('367', '352', '355', 'POINT(6.051085 48.644215)', 6.0510854, 48.644215),
('1745', '1711', '1711', 'POINT(6.051087 49.552085)', 6.0510871, 49.552085),
('2790', '2778', '2790', 'POINT(6.051088 49.280323)', 6.0510878, 49.280323),
('2914', '2921', '2925', 'POINT(6.051088 48.146525)', 6.051088, 48.146525),
('1938', '1931', '1931', 'POINT(6.05109 48.096832)', 6.0510902, 48.096832),
('1396', '1385', '1392', 'POINT(6.051095 49.474107)', 6.0510945, 49.474107),
('2744', '2721', '2721', 'POINT(6.051097 48.064674)', 6.0510965, 48.064674),
('396', '717', '721', 'POINT(6.051103 49.738862)', 6.0511027, 49.738862),
('1171', '1844', '1447', 'POINT(6.051108 48.110522)', 6.0511079, 48.110522),
('2929', '2921', '2925', 'POINT(6.051109 49.796556)', 6.0511091, 49.796556),
('411', '1849', '392', 'POINT(6.051114 49.65424)', 6.0511137, 49.65424),
('2417', '2402', '2406', 'POINT(6.051116 48.05637)', 6.0511161, 48.05637),
('1074', '1295', '1303', 'POINT(6.051116 49.350012)', 6.051116, 49.350012),
('3439', '3495', '3506', 'POINT(6.051117 49.728071)', 6.0511169, 49.728071),
('292', '494', '494', 'POINT(6.051118 49.063364)', 6.0511179, 49.063364),
('887', '1562', '1303', 'POINT(6.051121 49.245108)', 6.0511207, 49.245108),
('2442', '2442', '2442', 'POINT(6.051121 48.816901)', 6.0511208, 48.816901),
('2451', '2433', '2437', 'POINT(6.051127 48.247937)', 6.0511273, 48.247937),
('1663', '1655', '1663', 'POINT(6.05113 49.015893)', 6.0511304, 49.015893),
('358', '3008', '336', 'POINT(6.051131 48.149782)', 6.0511311, 48.149782),
('2014', '2033', '2036', 'POINT(6.051146 49.651303)', 6.0511464, 49.651303),
('194', '146', '146', 'POINT(6.051148 49.23087)', 6.0511479, 49.23087),
('2285', '2277', '2281', 'POINT(6.051149 49.468714)', 6.0511493, 49.468714),
('290', '846', '846', 'POINT(6.05115 49.804856)', 6.0511495, 49.804856),
('2328', '2294', '2294', 'POINT(6.051151 49.767443)', 6.0511506, 49.767443),
('2255', '2247', '2251', 'POINT(6.051152 49.287568)', 6.0511517, 49.287568),
('1471', '2456', '2460', 'POINT(6.051153 48.989847)', 6.0511533, 48.989847),
('2937', '2933', '2937', 'POINT(6.05115 49.560772)', 6.05115, 49.560772),
('2038', '2061', '2076', 'POINT(6.051156 49.885065)', 6.0511556, 49.885065),
('373', '3177', '1442', 'POINT(6.051156 48.150276)', 6.051156, 48.150276),
('1397', '4274', '1401', 'POINT(6.051157 49.903992)', 6.0511573, 49.903992),
('847', '2035', '2039', 'POINT(6.051161 48.589244)', 6.0511611, 48.589244),
('2201', '2194', '2198', 'POINT(6.051164 49.464991)', 6.0511641, 49.464991),
('1959', '1962', '1966', 'POINT(6.05117 49.229737)', 6.0511695, 49.229737),
('2531', '2827', '2838', 'POINT(6.051172 49.865855)', 6.0511717, 49.865855),
('2666', '2651', '2651', 'POINT(6.05118 49.753046)', 6.0511795, 49.753046),
('1893', '1935', '1935', 'POINT(6.051182 49.097659)', 6.0511816, 49.097659),
('232', '202', '202', 'POINT(6.051182 48.450187)', 6.0511822, 48.450187),
('3790', '3749', '3749', 'POINT(6.051183 49.701192)', 6.0511834, 49.701192),
('726', '722', '726', 'POINT(6.051186 49.330886)', 6.0511862, 49.330886),
('299', '277', '280', 'POINT(6.051186 49.543314)', 6.0511862, 49.543314),
('190', '175', '179', 'POINT(6.051188 49.230762)', 6.0511879, 49.230762),
('1189', '1171', '1171', 'POINT(6.051191 48.098276)', 6.0511913, 48.098276),
('718', '707', '711', 'POINT(6.051196 49.449907)', 6.0511958, 49.449907),
('2850', '2835', '2835', 'POINT(6.051196 49.740282)', 6.0511963, 49.740282),
('359', '1989', '1131', 'POINT(6.051197 48.814298)', 6.0511971, 48.814298),
('2006', '2201', '2205', 'POINT(6.051199 48.072218)', 6.0511991, 48.072218),
('2277', '2262', '2262', 'POINT(6.0512 49.746011)', 6.0511996, 49.746011),
('1860', '1853', '1856', 'POINT(6.0512 48.686129)', 6.0512002, 48.686129),
('2719', '2711', '2715', 'POINT(6.051204 49.289104)', 6.0512038, 49.289104),
('2551', '2539', '2551', 'POINT(6.051205 49.724762)', 6.0512052, 49.724762),
('414', '384', '392', 'POINT(6.051206 49.664144)', 6.0512056, 49.664144),
('301', '1229', '1229', 'POINT(6.051206 48.654472)', 6.0512064, 48.654472),
('202', '157', '157', 'POINT(6.051207 48.071439)', 6.0512073, 48.071439),
('2025', '2032', '2032', 'POINT(6.05121 49.426166)', 6.0512098, 49.426166),
('235', '198', '201', 'POINT(6.051214 49.731329)', 6.0512135, 49.731329),
('705', '719', '723', 'POINT(6.051224 49.739566)', 6.0512238, 49.739566),
('2622', '2584', '2592', 'POINT(6.051237 49.432328)', 6.0512366, 49.432328),
('273', '235', '239', 'POINT(6.051246 49.227961)', 6.0512462, 49.227961),
('1456', '2988', '1441', 'POINT(6.051247 48.641631)', 6.0512467, 48.641631),
('190', '153', '160', 'POINT(6.051247 49.742228)', 6.0512467, 49.742228),
('522', '567', '571', 'POINT(6.05125 49.94689)', 6.0512501, 49.94689),
('292', '1535', '1539', 'POINT(6.051259 48.63853)', 6.0512587, 48.63853),
('2908', '2893', '2896', 'POINT(6.05126 49.723665)', 6.0512597, 49.723665),
('1576', '1576', '1584', 'POINT(6.051266 49.03743)', 6.0512661, 49.03743),
('1209', '3065', '1206', 'POINT(6.051271 49.879261)', 6.0512707, 49.879261),
('2586', '4937', '2575', 'POINT(6.051273 48.51347)', 6.0512734, 48.51347),
('2675', '2671', '2671', 'POINT(6.051276 48.133333)', 6.0512761, 48.133333),
('2127', '2123', '2138', 'POINT(6.051277 49.443985)', 6.0512768, 49.443985),
('346', '324', '328', 'POINT(6.051282 49.835528)', 6.0512817, 49.835528),
('2285', '2273', '2277', 'POINT(6.051282 49.80925)', 6.0512823, 49.80925),
('2899', '3326', '2910', 'POINT(6.051287 49.372055)', 6.0512871, 49.372055),
('2208', '2186', '2186', 'POINT(6.051292 48.017882)', 6.0512915, 48.017882),
('2222', '2472', '2472', 'POINT(6.051292 49.8596)', 6.0512918, 49.8596),
('2553', '2545', '2545', 'POINT(6.051299 48.568932)', 6.0512994, 48.568932),
('2880', '3524', '3558', 'POINT(6.051311 49.728311)', 6.051311, 49.728311),
('415', '367', '370', 'POINT(6.051317 49.816199)', 6.0513167, 49.816199),
('2081', '2096', '2100', 'POINT(6.051318 49.322616)', 6.0513178, 49.322616),
('676', '1221', '698', 'POINT(6.051318 48.13922)', 6.0513184, 48.13922),
('1010', '1007', '1025', 'POINT(6.051318 49.268099)', 6.0513184, 49.268099),
('2154', '2117', '2121', 'POINT(6.051319 49.767108)', 6.0513185, 49.767108),
('441', '1177', '874', 'POINT(6.051319 49.563113)', 6.0513191, 49.563113),
('1021', '2136', '1055', 'POINT(6.051343 48.044166)', 6.0513425, 48.044166),
('228', '243', '250', 'POINT(6.051344 49.559943)', 6.0513438, 49.559943),
('2160', '2145', '2145', 'POINT(6.051351 47.982681)', 6.0513512, 47.982681),
('2864', '2827', '2830', 'POINT(6.051355 48.564419)', 6.0513548, 48.564419),
('1016', '1012', '1016', 'POINT(6.05136 48.126082)', 6.0513601, 48.126082),
('2481', '3009', '3009', 'POINT(6.05136 48.120294)', 6.0513603, 48.120294),
('242', '216', '220', 'POINT(6.051371 49.742415)', 6.0513709, 49.742415),
('621', '625', '632', 'POINT(6.051373 49.426494)', 6.051373, 49.426494),
('460', '2588', '1378', 'POINT(6.051376 49.463868)', 6.0513761, 49.463868),
('2987', '2987', '2990', 'POINT(6.051381 49.644827)', 6.0513807, 49.644827),
('347', '2521', '325', 'POINT(6.051384 48.464864)', 6.0513842, 48.464864),
('251', '221', '224', 'POINT(6.051385 48.809768)', 6.0513845, 48.809768),
('269', '1519', '1519', 'POINT(6.051387 48.002774)', 6.0513872, 48.002774),
('246', '212', '212', 'POINT(6.051388 49.74591)', 6.0513877, 49.74591),
('2755', '2740', '2744', 'POINT(6.051389 49.468194)', 6.0513891, 49.468194),
('1795', '1802', '1810', 'POINT(6.05139 49.731813)', 6.0513904, 49.731813),
('520', '2029', '531', 'POINT(6.051392 48.647497)', 6.051392, 48.647497),
('2149', '2160', '2167', 'POINT(6.051401 49.263099)', 6.0514005, 49.263099),
('579', '1938', '606', 'POINT(6.051402 48.152416)', 6.0514016, 48.152416),
('2317', '2288', '2291', 'POINT(6.05141 49.725921)', 6.0514097, 49.725921),
('2404', '3060', '2790', 'POINT(6.051412 49.916165)', 6.0514124, 49.916165),
('2712', '2708', '2712', 'POINT(6.051413 49.740612)', 6.0514133, 49.740612),
('1722', '1666', '1719', 'POINT(6.051414 49.228096)', 6.0514137, 49.228096),
('2696', '2692', '2692', 'POINT(6.051415 49.431033)', 6.0514151, 49.431033),
('2066', '2055', '2062', 'POINT(6.051418 49.698586)', 6.0514179, 49.698586),
('2564', '2530', '2530', 'POINT(6.051423 47.995775)', 6.0514234, 47.995775),
('2894', '2882', '2890', 'POINT(6.051424 49.463229)', 6.0514242, 49.463229),
('1943', '1913', '1920', 'POINT(6.051425 48.145152)', 6.0514246, 48.145152),
('2818', '2822', '2826', 'POINT(6.051425 49.432088)', 6.0514253, 49.432088),
('2186', '2175', '2193', 'POINT(6.051426 49.55496)', 6.0514255, 49.55496),
('2221', '2213', '2217', 'POINT(6.051426 49.628816)', 6.0514263, 49.628816),
('3031', '3024', '3027', 'POINT(6.051435 49.647581)', 6.0514346, 49.647581),
('698', '1310', '1314', 'POINT(6.051436 48.602752)', 6.0514363, 48.602752),
('288', '239', '239', 'POINT(6.05144 48.65211)', 6.0514398, 48.65211),
('3303', '3310', '3314', 'POINT(6.051442 49.660464)', 6.0514415, 49.660464),
('1659', '1655', '1659', 'POINT(6.051443 49.445132)', 6.0514425, 49.445132),
('2516', '2538', '2542', 'POINT(6.051443 49.70186)', 6.0514433, 49.70186),
('2907', '2895', '2899', 'POINT(6.051443 49.822028)', 6.0514433, 49.822028),
('1801', '1978', '1981', 'POINT(6.051445 49.372379)', 6.0514452, 49.372379),
('2359', '2378', '2385', 'POINT(6.051445 49.274517)', 6.051445, 49.274517),
('847', '847', '854', 'POINT(6.051446 48.608831)', 6.0514461, 48.608831),
('542', '542', '542', 'POINT(6.051447 49.069367)', 6.0514467, 49.069367),
('291', '1938', '336', 'POINT(6.051448 48.014781)', 6.0514475, 48.014781),
('228', '194', '194', 'POINT(6.051448 49.537378)', 6.051448, 49.537378),
('905', '920', '924', 'POINT(6.051449 48.127051)', 6.0514493, 48.127051),
('2448', '2441', '2448', 'POINT(6.05145 49.459196)', 6.0514497, 49.459196),
('2642', '2624', '2624', 'POINT(6.051452 48.571779)', 6.0514524, 48.571779),
('999', '1048', '1055', 'POINT(6.051456 49.905347)', 6.0514561, 49.905347),
('2242', '2242', '2246', 'POINT(6.051459 49.702519)', 6.0514586, 49.702519),
('3052', '2535', '3045', 'POINT(6.051466 49.58756)', 6.051466, 49.58756),
('254', '1445', '696', 'POINT(6.05147 49.549744)', 6.0514699, 49.549744),
('1794', '1782', '1786', 'POINT(6.05147 49.032689)', 6.0514701, 49.032689),
('702', '706', '713', 'POINT(6.051477 49.721023)', 6.0514774, 49.721023),
('2617', '3204', '3216', 'POINT(6.051481 49.706069)', 6.0514805, 49.706069),
('292', '573', '322', 'POINT(6.051482 48.467281)', 6.0514821, 48.467281),
('434', '453', '456', 'POINT(6.051484 49.23902)', 6.0514842, 49.23902),
('3106', '3091', '3094', 'POINT(6.051486 49.727872)', 6.0514856, 49.727872),
('2936', '2932', '2932', 'POINT(6.051488 49.721307)', 6.0514876, 49.721307),
('2464', '2778', '2778', 'POINT(6.051491 48.995748)', 6.0514906, 48.995748),
('1541', '1511', '1530', 'POINT(6.051492 48.140301)', 6.0514918, 48.140301),
('504', '493', '500', 'POINT(6.051494 49.558158)', 6.0514939, 49.558158),
('1056', '2131', '1037', 'POINT(6.051502 49.588729)', 6.0515023, 49.588729),
('2867', '2845', '2845', 'POINT(6.051504 48.058525)', 6.0515037, 48.058525),
('224', '190', '190', 'POINT(6.051507 49.736334)', 6.0515071, 49.736334),
('385', '381', '381', 'POINT(6.051514 49.236845)', 6.0515141, 49.236845),
('1763', '1771', '1782', 'POINT(6.051524 49.567885)', 6.0515236, 49.567885),
('857', '1490', '865', 'POINT(6.051527 49.922454)', 6.0515265, 49.922454),
('1677', '1654', '1654', 'POINT(6.051528 49.243933)', 6.0515275, 49.243933),
('569', '3189', '557', 'POINT(6.051529 49.861572)', 6.0515287, 49.861572),
('3152', '3133', '3133', 'POINT(6.051529 49.661551)', 6.0515294, 49.661551),
('1030', '1018', '1026', 'POINT(6.05153 49.916198)', 6.0515301, 49.916198),
('1115', '1384', '1388', 'POINT(6.051535 48.266686)', 6.0515351, 48.266686),
('2423', '2435', '2438', 'POINT(6.051541 49.458059)', 6.0515411, 49.458059),
('291', '227', '231', 'POINT(6.051545 49.567989)', 6.0515451, 49.567989),
('711', '715', '715', 'POINT(6.05155 48.630923)', 6.0515503, 48.630923),
('1156', '4404', '1167', 'POINT(6.051551 48.050957)', 6.0515505, 48.050957),
('1903', '2236', '2244', 'POINT(6.051554 49.450987)', 6.0515539, 49.450987),
('445', '1370', '617', 'POINT(6.05156 49.346941)', 6.05156, 49.346941),
('3498', '3577', '3580', 'POINT(6.051567 49.281776)', 6.0515669, 49.281776),
('2077', '2066', '2066', 'POINT(6.051574 49.878176)', 6.0515743, 49.878176),
('254', '966', '970', 'POINT(6.051575 48.679004)', 6.0515747, 48.679004),
('299', '269', '273', 'POINT(6.051579 49.234022)', 6.0515786, 49.234022),
('2304', '2510', '2323', 'POINT(6.051589 48.739763)', 6.0515892, 48.739763),
('1071', '1067', '1071', 'POINT(6.05159 49.695011)', 6.0515904, 49.695011),
('157', '123', '131', 'POINT(6.051595 49.229372)', 6.0515954, 49.229372),
('217', '179', '179', 'POINT(6.0516 48.066057)', 6.0515996, 48.066057),
('1259', '1244', '1247', 'POINT(6.051601 49.737541)', 6.0516007, 49.737541),
('1248', '1233', '1237', 'POINT(6.051603 49.729799)', 6.0516032, 49.729799),
('318', '299', '310', 'POINT(6.051608 48.127078)', 6.0516083, 48.127078),
('209', '164', '164', 'POINT(6.051609 47.989549)', 6.0516089, 47.989549),
('456', '1222', '1229', 'POINT(6.051611 49.72177)', 6.0516109, 49.72177),
('329', '332', '332', 'POINT(6.05162 49.543806)', 6.0516204, 49.543806),
('1580', '1550', '1558', 'POINT(6.051623 49.325133)', 6.0516226, 49.325133),
('2856', '2882', '2886', 'POINT(6.051623 49.641507)', 6.0516226, 49.641507),
('1670', '1707', '1711', 'POINT(6.051626 48.991532)', 6.0516256, 48.991532),
('1961', '1950', '1950', 'POINT(6.051627 48.157097)', 6.0516274, 48.157097),
('983', '979', '983', 'POINT(6.051628 49.724734)', 6.0516277, 49.724734),
('1756', '1741', '1744', 'POINT(6.051629 49.86563)', 6.0516285, 49.86563),
('617', '614', '614', 'POINT(6.051632 49.928905)', 6.0516317, 49.928905),
('1214', '1207', '1214', 'POINT(6.051637 48.762567)', 6.051637, 48.762567),
('2452', '2452', '2456', 'POINT(6.051646 49.766087)', 6.0516456, 49.766087),
('444', '2268', '795', 'POINT(6.05165 49.666121)', 6.0516504, 49.666121),
('249', '208', '212', 'POINT(6.051654 48.700903)', 6.051654, 48.700903),
('369', '1210', '960', 'POINT(6.051662 48.151591)', 6.0516617, 48.151591),
('2168', '2261', '2265', 'POINT(6.051663 49.074395)', 6.0516629, 49.074395),
('2571', '2571', '2574', 'POINT(6.051671 48.156352)', 6.051671, 48.156352),
('526', '560', '568', 'POINT(6.051672 49.946647)', 6.0516721, 49.946647),
('1639', '3126', '1763', 'POINT(6.051675 48.303465)', 6.0516751, 48.303465),
('2348', '3143', '3146', 'POINT(6.051678 49.832749)', 6.0516776, 49.832749),
('216', '544', '551', 'POINT(6.051679 49.76772)', 6.0516791, 49.76772),
('1553', '1586', '1586', 'POINT(6.051687 48.59147)', 6.0516866, 48.59147),
('543', '505', '513', 'POINT(6.051691 49.815782)', 6.0516908, 49.815782),
('288', '1003', '277', 'POINT(6.05169 49.53915)', 6.05169, 49.53915),
('1595', '1583', '1587', 'POINT(6.051696 49.293483)', 6.0516959, 49.293483),
('1774', '1759', '1759', 'POINT(6.051702 48.077937)', 6.0517022, 48.077937),
('288', '1792', '1347', 'POINT(6.051709 48.009159)', 6.0517093, 48.009159),
('736', '672', '676', 'POINT(6.05171 49.915382)', 6.0517102, 49.915382),
('1618', '2378', '2390', 'POINT(6.05171 49.803566)', 6.0517104, 49.803566),
('2299', '2647', '2650', 'POINT(6.051711 49.274171)', 6.0517108, 49.274171),
('503', '1907', '1457', 'POINT(6.051713 48.66032)', 6.051713, 48.66032),
('1718', '1684', '1691', 'POINT(6.051716 49.018034)', 6.0517155, 49.018034),
('2764', '2757', '2757', 'POINT(6.051718 49.821535)', 6.0517175, 49.821535),
('273', '250', '254', 'POINT(6.051719 49.543357)', 6.051719, 49.543357),
('2783', '2801', '2809', 'POINT(6.05172 49.83512)', 6.0517199, 49.83512),
('209', '1697', '179', 'POINT(6.051723 49.235812)', 6.0517226, 49.235812),
('1636', '1550', '1557', 'POINT(6.051723 48.266042)', 6.051723, 48.266042),
('261', '1579', '1583', 'POINT(6.051727 48.614007)', 6.0517265, 48.614007),
('1289', '2693', '874', 'POINT(6.051735 49.0727)', 6.0517345, 49.0727),
('318', '303', '303', 'POINT(6.051736 49.234175)', 6.0517358, 49.234175),
('1980', '1977', '1980', 'POINT(6.051736 49.947621)', 6.0517364, 49.947621),
('183', '153', '157', 'POINT(6.051739 48.097072)', 6.0517391, 48.097072),
('2208', '2190', '2197', 'POINT(6.051741 49.457342)', 6.0517412, 49.457342),
('429', '1161', '978', 'POINT(6.051743 48.132965)', 6.0517426, 48.132965),
('649', '1395', '626', 'POINT(6.051743 49.892249)', 6.0517431, 49.892249),
('2021', '2010', '2010', 'POINT(6.051749 48.114142)', 6.0517494, 48.114142),
('2141', '2152', '2156', 'POINT(6.05175 47.976567)', 6.0517501, 47.976567),
('2541', '2560', '2563', 'POINT(6.051751 48.02119)', 6.0517507, 48.02119),
('2732', '2721', '2725', 'POINT(6.051752 48.024677)', 6.0517524, 48.024677),
('2032', '2021', '2032', 'POINT(6.051753 49.432599)', 6.0517533, 49.432599),
('3373', '3376', '3380', 'POINT(6.051753 49.634952)', 6.051753, 49.634952),
('732', '945', '952', 'POINT(6.051755 49.746325)', 6.0517552, 49.746325),
('1929', '3423', '2753', 'POINT(6.051755 49.834667)', 6.0517552, 49.834667),
('2475', '2852', '2490', 'POINT(6.051764 49.587234)', 6.0517644, 49.587234),
('499', '458', '465', 'POINT(6.051767 48.602618)', 6.0517673, 48.602618),
('1348', '1326', '1337', 'POINT(6.051771 48.988571)', 6.051771, 48.988571),
('2758', '2750', '2754', 'POINT(6.051773 48.037884)', 6.0517727, 48.037884),
('276', '250', '250', 'POINT(6.051774 49.709248)', 6.0517738, 49.709248),
('220', '190', '190', 'POINT(6.051783 48.097049)', 6.0517829, 48.097049),
('2161', '2134', '2138', 'POINT(6.051784 49.63886)', 6.051784, 49.63886),
('2491', '2476', '2480', 'POINT(6.051785 48.146745)', 6.0517845, 48.146745),
('277', '262', '265', 'POINT(6.051785 48.657928)', 6.0517852, 48.657928),
('461', '1411', '1415', 'POINT(6.051787 48.670315)', 6.0517867, 48.670315),
('406', '391', '391', 'POINT(6.051788 49.745531)', 6.0517875, 49.745531),
('2650', '2646', '2650', 'POINT(6.051789 48.06489)', 6.0517886, 48.06489),
('1707', '1704', '1704', 'POINT(6.051789 49.928105)', 6.0517891, 49.928105),
('445', '442', '445', 'POINT(6.051791 49.708823)', 6.0517907, 49.708823),
('2174', '2193', '2193', 'POINT(6.051799 49.230711)', 6.051799, 49.230711),
('1070', '1093', '1108', 'POINT(6.051801 48.997513)', 6.0518005, 48.997513),
('2623', '2608', '2615', 'POINT(6.051806 48.056589)', 6.051806, 48.056589),
('2049', '2034', '2037', 'POINT(6.051812 49.474706)', 6.0518124, 49.474706),
('1520', '2585', '2593', 'POINT(6.051813 49.708606)', 6.0518126, 49.708606),
('2257', '2250', '2254', 'POINT(6.051813 49.287563)', 6.0518134, 49.287563),
('576', '576', '580', 'POINT(6.05181 48.085329)', 6.05181, 48.085329),
('2295', '2250', '2254', 'POINT(6.051822 49.280493)', 6.0518216, 49.280493),
('1950', '3041', '1946', 'POINT(6.051823 48.775807)', 6.0518225, 48.775807),
('1229', '2328', '1801', 'POINT(6.051826 48.150003)', 6.0518257, 48.150003),
('1427', '1480', '1480', 'POINT(6.051837 49.552248)', 6.0518374, 49.552248),
('2362', '2395', '2399', 'POINT(6.051838 48.248135)', 6.0518375, 48.248135),
('1587', '1576', '1580', 'POINT(6.051838 49.474272)', 6.0518381, 49.474272),
('1639', '1639', '1643', 'POINT(6.051841 48.114268)', 6.0518407, 48.114268),
('1079', '1072', '1075', 'POINT(6.051843 49.73904)', 6.0518432, 49.73904),
('812', '2007', '2015', 'POINT(6.051846 48.817084)', 6.0518455, 48.817084),
('982', '2313', '1554', 'POINT(6.051855 49.654424)', 6.0518547, 49.654424),
('429', '3244', '1910', 'POINT(6.051856 48.150497)', 6.0518561, 48.150497),
('865', '1595', '1598', 'POINT(6.051858 49.063544)', 6.0518581, 49.063544),
('541', '1232', '1239', 'POINT(6.051863 49.728267)', 6.0518628, 49.728267),
('1501', '1977', '1984', 'POINT(6.051864 49.350182)', 6.0518638, 49.350182),
('2816', '3592', '3363', 'POINT(6.051865 49.796709)', 6.0518654, 49.796709),
('3330', '3296', '3303', 'POINT(6.051867 49.245281)', 6.0518667, 49.245281),
('377', '2162', '2162', 'POINT(6.051886 48.589434)', 6.0518855, 48.589434),
('523', '1506', '1509', 'POINT(6.051887 49.651483)', 6.0518874, 49.651483),
('239', '213', '216', 'POINT(6.05189 49.537879)', 6.0518899, 49.537879),
('2762', '2755', '2755', 'POINT(6.05189 48.072437)', 6.0518904, 48.072437),
('2547', '2535', '2539', 'POINT(6.051892 49.287736)', 6.051892, 49.287736),
('2936', '2899', '2899', 'POINT(6.051897 49.767618)', 6.051897, 49.767618),
('2287', '2294', '2298', 'POINT(6.051898 48.071654)', 6.0518984, 48.071654),
('2020', '2508', '2031', 'POINT(6.051898 49.805034)', 6.051898, 49.805034),
('3041', '2989', '2996', 'POINT(6.0519 49.468879)', 6.0519004, 49.468879),
('1588', '1942', '1585', 'POINT(6.051905 48.686331)', 6.0519046, 48.686331),
('1861', '1888', '1895', 'POINT(6.051905 49.229906)', 6.0519048, 49.229906),
('2354', '3352', '2373', 'POINT(6.051908 49.560934)', 6.0519077, 49.560934),
('564', '725', '747', 'POINT(6.05191 49.230929)', 6.0519102, 49.230929),
('331', '1229', '1233', 'POINT(6.051912 48.654675)', 6.0519123, 48.654675),
('1961', '1979', '1979', 'POINT(6.051915 49.097834)', 6.0519146, 49.097834),
('321', '1563', '815', 'POINT(6.051915 49.866002)', 6.0519153, 49.866002),
('453', '2536', '846', 'POINT(6.05192 49.904141)', 6.0519196, 49.904141),
('1884', '2244', '2247', 'POINT(6.051922 48.814484)', 6.0519223, 48.814484),
('265', '235', '239', 'POINT(6.051933 49.543477)', 6.0519325, 49.543477),
('2292', '3255', '2386', 'POINT(6.051935 49.701348)', 6.0519352, 49.701348),
('2359', '2355', '2362', 'POINT(6.051941 49.450072)', 6.0519405, 49.450072),
('2311', '2307', '2307', 'POINT(6.051943 49.289273)', 6.0519427, 49.289273),
('844', '821', '821', 'POINT(6.051946 49.664323)', 6.0519456, 49.664323),
('2404', '2397', '2400', 'POINT(6.051951 49.740435)', 6.0519513, 49.740435),
('2069', '2081', '2081', 'POINT(6.051953 49.426332)', 6.0519534, 49.426332),
('474', '470', '470', 'POINT(6.051955 49.731504)', 6.0519545, 49.731504),
('277', '1936', '254', 'POINT(6.051955 49.746164)', 6.051955, 49.746164),
('3015', '2996', '2996', 'POINT(6.051959 49.724918)', 6.0519587, 49.724918),
('1389', '2969', '1636', 'POINT(6.051961 48.641817)', 6.0519609, 48.641817),
('227', '190', '190', 'POINT(6.051969 49.774334)', 6.051969, 49.774334),
('2855', '2829', '2829', 'POINT(6.05197 48.133548)', 6.0519704, 48.133548),
('2156', '2156', '2156', 'POINT(6.051981 48.018101)', 6.0519806, 48.018101),
('217', '179', '179', 'POINT(6.051983 49.228133)', 6.0519832, 49.228133),
('2914', '2910', '2914', 'POINT(6.051984 49.432492)', 6.0519844, 49.432492),
('1647', '2542', '1639', 'POINT(6.051991 49.537446)', 6.0519911, 49.537446),
('1434', '1457', '1460', 'POINT(6.051992 48.638702)', 6.051992, 48.638702),
('2896', '4911', '2843', 'POINT(6.051997 48.513662)', 6.051997, 48.513662),
('2840', '2833', '2833', 'POINT(6.052 49.723838)', 6.0519999, 49.723838),
('2300', '2933', '2937', 'POINT(6.052015 49.352983)', 6.0520154, 49.352983),
('2321', '2325', '2329', 'POINT(6.05202 48.569123)', 6.0520197, 48.569123),
('595', '580', '584', 'POINT(6.052021 49.444149)', 6.0520208, 49.444149),
('2595', '2945', '2949', 'POINT(6.052029 49.835675)', 6.0520289, 49.835675),
('1502', '1640', '1648', 'POINT(6.05203 49.372219)', 6.0520296, 49.372219),
('2327', '2338', '2342', 'POINT(6.052035 48.044389)', 6.0520348, 48.044389),
('1175', '1172', '1179', 'POINT(6.052036 49.704671)', 6.0520363, 49.704671),
('2674', '2671', '2671', 'POINT(6.052038 49.809403)', 6.0520375, 49.809403),
('3120', '3116', '3120', 'POINT(6.052041 49.858429)', 6.0520412, 49.858429),
('2617', '2632', '2632', 'POINT(6.052042 47.982898)', 6.0520418, 47.982898),
('1446', '1427', '1431', 'POINT(6.052052 48.126299)', 6.0520515, 48.126299),
('745', '2339', '1594', 'POINT(6.052052 49.322784)', 6.052052, 49.322784),
('2957', '2964', '2968', 'POINT(6.052054 48.120509)', 6.0520535, 48.120509),
('2229', '2214', '2218', 'POINT(6.052055 49.841664)', 6.0520553, 49.841664),
('1332', '3438', '1377', 'POINT(6.052058 49.879415)', 6.0520583, 49.879415),
('253', '1422', '941', 'POINT(6.052059 49.767275)', 6.0520593, 49.767275),
('336', '1349', '874', 'POINT(6.052065 49.563274)', 6.0520653, 49.563274),
('1854', '1820', '1827', 'POINT(6.052069 49.728467)', 6.0520689, 49.728467),
('3062', '3673', '3680', 'POINT(6.052069 49.268273)', 6.0520691, 49.268273),
('314', '310', '310', 'POINT(6.052073 49.816354)', 6.0520728, 49.816354),
('2737', '2726', '2730', 'POINT(6.052079 48.564608)', 6.0520788, 48.564608),
('359', '572', '576', 'POINT(6.05208 48.002982)', 6.0520799, 48.002982),
('261', '2311', '531', 'POINT(6.052089 48.465073)', 6.0520889, 48.465073),
('209', '183', '191', 'POINT(6.052093 49.560105)', 6.052093, 49.560105),
('366', '1732', '351', 'POINT(6.052098 48.152636)', 6.0520982, 48.152636),
('1985', '1963', '1963', 'POINT(6.052098 48.56182)', 6.0520982, 48.56182),
('1262', '1255', '1262', 'POINT(6.05211 48.809952)', 6.0521095, 48.809952),
('314', '269', '277', 'POINT(6.052112 48.668295)', 6.052112, 48.668295),
('1127', '4599', '1273', 'POINT(6.052113 48.647684)', 6.0521127, 48.647684),
('224', '186', '186', 'POINT(6.052113 49.74259)', 6.0521134, 49.74259),
('2380', '2362', '2362', 'POINT(6.052114 49.426659)', 6.0521144, 49.426659),
('2744', '2732', '2732', 'POINT(6.052115 47.99599)', 6.0521154, 47.99599),
('3030', '3019', '3019', 'POINT(6.052117 49.464032)', 6.0521174, 49.464032),
('2568', '3114', '3121', 'POINT(6.052119 49.645006)', 6.0521193, 49.645006),
('430', '423', '427', 'POINT(6.052131 48.668101)', 6.0521305, 48.668101),
('336', '332', '332', 'POINT(6.052131 49.746084)', 6.0521306, 49.746084),
('2808', '2965', '2969', 'POINT(6.052132 49.468359)', 6.0521324, 49.468359),
('299', '1111', '269', 'POINT(6.052138 48.014991)', 6.0521377, 48.014991),
('2676', '2665', '2669', 'POINT(6.05214 49.263269)', 6.0521401, 49.263269),
('552', '537', '541', 'POINT(6.052142 49.731969)', 6.0521424, 49.731969),
('898', '1388', '1392', 'POINT(6.052144 48.127269)', 6.0521444, 48.127269),
('2383', '2383', '2387', 'POINT(6.052154 49.740789)', 6.0521541, 49.740789),
('1664', '1691', '1694', 'POINT(6.052155 48.602941)', 6.0521545, 48.602941),
('2505', '2478', '2482', 'POINT(6.052156 49.53907)', 6.0521564, 49.53907),
('2794', '2801', '2801', 'POINT(6.052159 49.431199)', 6.0521589, 49.431199),
('3195', '3183', '3183', 'POINT(6.052164 49.726076)', 6.0521638, 49.726076),
('1748', '1797', '1800', 'POINT(6.052166 49.698741)', 6.0521664, 49.698741),
('3002', '2995', '2998', 'POINT(6.052169 49.463394)', 6.0521693, 49.463394),
('3017', '3002', '3005', 'POINT(6.05217 49.432254)', 6.0521698, 49.432254),
('269', '1146', '232', 'POINT(6.052173 48.652282)', 6.0521733, 48.652282),
('593', '1190', '1198', 'POINT(6.052173 48.609022)', 6.052173, 48.609022),
('3622', '3573', '3577', 'POINT(6.052174 49.647765)', 6.0521742, 49.647765),
('2485', '2488', '2496', 'POINT(6.052175 49.555121)', 6.0521746, 49.555121),
('2870', '3280', '2843', 'POINT(6.052177 48.571971)', 6.0521771, 48.571971),
('2052', '2014', '2026', 'POINT(6.052178 49.274686)', 6.0521778, 49.274686),
('3351', '3318', '3321', 'POINT(6.05218 49.660641)', 6.0521804, 49.660641),
('1201', '1235', '1235', 'POINT(6.052182 49.069543)', 6.0521816, 49.069543),
('1599', '1490', '1779', 'POINT(6.052182 49.916315)', 6.0521824, 49.916315),
('1779', '3148', '1798', 'POINT(6.052183 49.702033)', 6.0521829, 49.702033),
('2150', '2139', '2142', 'POINT(6.052187 49.372547)', 6.052187, 49.372547),
('1476', '1468', '1472', 'POINT(6.052189 49.445296)', 6.0521886, 49.445296),
('1431', '1506', '1521', 'POINT(6.052191 49.345836)', 6.0521912, 49.345836),
('2624', '2602', '2605', 'POINT(6.052194 49.53754)', 6.0521936, 49.53754),
('1885', '2424', '2424', 'POINT(6.05219 48.140524)', 6.05219, 48.140524),
('2740', '2740', '2740', 'POINT(6.052196 48.058741)', 6.0521958, 48.058741),
('2776', '2769', '2769', 'POINT(6.052201 49.702692)', 6.052201, 49.702692),
('3559', '3566', '3574', 'POINT(6.052205 49.822183)', 6.0522046, 49.822183),
('2924', '3377', '3381', 'POINT(6.052205 49.459361)', 6.0522048, 49.459361),
('1854', '1842', '1850', 'POINT(6.052214 49.022058)', 6.0522142, 49.022058),
('2786', '2768', '2771', 'POINT(6.052215 49.587721)', 6.0522154, 49.587721),
('563', '895', '574', 'POINT(6.052216 49.905496)', 6.0522164, 49.905496),
('422', '530', '553', 'POINT(6.052218 49.721203)', 6.0522183, 49.721203),
('265', '981', '779', 'POINT(6.052219 49.549904)', 6.0522193, 49.549904),
('254', '224', '228', 'POINT(6.052222 49.239192)', 6.0522217, 49.239192),
('1682', '1664', '1667', 'POINT(6.052222 49.706248)', 6.0522219, 49.706248),
('2025', '2066', '2066', 'POINT(6.052225 48.031199)', 6.0522252, 48.031199),
('3262', '3221', '3240', 'POINT(6.052228 49.728062)', 6.0522282, 49.728062),
('2085', '2112', '2119', 'POINT(6.052228 48.995927)', 6.052228, 48.995927),
('463', '2865', '1415', 'POINT(6.05223 49.721487)', 6.0522301, 49.721487),
('1025', '4625', '1081', 'POINT(6.052241 48.051171)', 6.0522412, 48.051171),
('1947', '1933', '1936', 'POINT(6.052243 48.266893)', 6.0522429, 48.266893),
('1863', '2215', '2230', 'POINT(6.052247 48.050341)', 6.0522467, 48.050341),
('2197', '2182', '2185', 'POINT(6.052249 49.736508)', 6.0522494, 49.736508),
('2011', '2029', '2037', 'POINT(6.052251 49.050552)', 6.0522512, 49.050552),
('1397', '1752', '1394', 'POINT(6.052252 49.558324)', 6.0522517, 49.558324),
('2224', '2183', '2183', 'POINT(6.052252 49.237015)', 6.0522519, 49.237015),
('516', '2228', '730', 'POINT(6.052253 49.588889)', 6.0522526, 49.588889),
('224', '183', '183', 'POINT(6.052258 49.008948)', 6.0522582, 49.008948),
('1767', '1752', '1759', 'POINT(6.052267 49.244104)', 6.0522669, 49.244104),
('396', '673', '385', 'POINT(6.052268 49.500721)', 6.0522683, 49.500721),
('659', '666', '670', 'POINT(6.05228 48.631098)', 6.0522797, 48.631098),
('3470', '3201', '3470', 'POINT(6.052281 49.661708)', 6.0522808, 49.661708),
('2528', '2513', '2517', 'POINT(6.052285 49.458223)', 6.0522846, 49.458223),
('202', '160', '160', 'POINT(6.05229 48.066277)', 6.0522902, 48.066277),
('2094', '2105', '2116', 'POINT(6.052293 48.977652)', 6.0522925, 48.977652),
('292', '235', '239', 'POINT(6.052293 48.660959)', 6.052293, 48.660959),
('374', '1089', '1093', 'POINT(6.052296 48.679192)', 6.0522955, 48.679192),
('595', '561', '565', 'POINT(6.052301 48.127295)', 6.0523008, 48.127295),
('1310', '2307', '2311', 'POINT(6.052301 49.922057)', 6.0523009, 49.922057),
('2032', '2006', '2014', 'POINT(6.052301 47.989767)', 6.0523011, 47.989767),
('2341', '2329', '2337', 'POINT(6.052302 49.281944)', 6.052302, 49.281944),
('2554', '2524', '2524', 'POINT(6.052304 49.451152)', 6.0523038, 49.451152),
('460', '1329', '1104', 'POINT(6.052308 49.34711)', 6.0523077, 49.34711),
('1924', '2372', '2372', 'POINT(6.052313 49.878323)', 6.052313, 49.878323),
('1625', '1636', '1640', 'POINT(6.052316 49.234193)', 6.0523161, 49.234193),
('2009', '2330', '2050', 'POINT(6.05232 48.739949)', 6.0523204, 48.739949),
('776', '772', '783', 'POINT(6.052332 49.916357)', 6.0523321, 49.916357),
('239', '198', '198', 'POINT(6.052344 49.729973)', 6.0523442, 49.729973),
('468', '595', '599', 'POINT(6.052348 48.641884)', 6.0523482, 48.641884),
('775', '748', '756', 'POINT(6.05235 48.991708)', 6.0523497, 48.991708),
('295', '762', '766', 'POINT(6.052351 48.151807)', 6.052351, 48.151807),
('3304', '3281', '3285', 'POINT(6.052354 49.721969)', 6.0523542, 49.721969),
('2841', '2848', '2856', 'POINT(6.052361 49.641689)', 6.0523613, 49.641689),
('1764', '1771', '1775', 'POINT(6.052362 48.762753)', 6.0523623, 48.762753),
('1486', '1513', '1520', 'POINT(6.052363 49.325302)', 6.0523631, 49.325302),
('280', '261', '261', 'POINT(6.052366 49.543968)', 6.0523657, 49.543968),
('2796', '2788', '2788', 'POINT(6.052368 49.724909)', 6.0523684, 49.724909),
('2048', '2814', '2814', 'POINT(6.052373 48.15658)', 6.0523731, 48.15658),
('538', '1173', '1181', 'POINT(6.052384 49.861211)', 6.0523835, 49.861211),
('220', '183', '183', 'POINT(6.052384 49.500252)', 6.0523839, 49.500252),
('2321', '2343', '2347', 'POINT(6.052385 48.248224)', 6.052385, 48.248224),
('2430', '2591', '2591', 'POINT(6.052386 49.865781)', 6.052386, 49.865781),
('1939', '4406', '1943', 'POINT(6.052388 48.303662)', 6.0523877, 48.303662),
('2070', '2414', '2425', 'POINT(6.05239 49.666298)', 6.0523897, 49.666298),
('1584', '1591', '1595', 'POINT(6.052395 49.074572)', 6.0523953, 49.074572),
('312', '264', '268', 'POINT(6.052398 48.676259)', 6.0523978, 48.676259),
('213', '186', '190', 'POINT(6.052402 49.009106)', 6.0524022, 49.009106),
('291', '2196', '243', 'POINT(6.052403 48.009375)', 6.0524027, 48.009375),
('2310', '2299', '2302', 'POINT(6.052403 49.766241)', 6.0524029, 49.766241),
('1147', '1162', '1162', 'POINT(6.052405 48.591657)', 6.0524048, 48.591657),
('429', '1274', '482', 'POINT(6.052412 48.756798)', 6.0524117, 48.756798),
('927', '1829', '1840', 'POINT(6.052413 48.660519)', 6.0524133, 48.660519),
('1452', '1437', '1441', 'POINT(6.052432 48.011951)', 6.0524316, 48.011951),
('161', '142', '142', 'POINT(6.052434 48.097289)', 6.0524337, 48.097289),
('292', '1195', '262', 'POINT(6.052435 49.832903)', 6.0524347, 49.832903),
('358', '2547', '369', 'POINT(6.052435 49.767918)', 6.052435, 49.767918),
('1714', '2336', '1714', 'POINT(6.052436 48.266242)', 6.0524358, 48.266242),
('2055', '2029', '2033', 'POINT(6.052437 49.293652)', 6.0524371, 49.293652),
('1550', '1516', '1516', 'POINT(6.05244 47.976779)', 6.0524399, 47.976779),
('287', '1322', '1325', 'POINT(6.052442 48.614194)', 6.0524418, 48.614194),
('2241', '2256', '2268', 'POINT(6.052443 48.021406)', 6.0524425, 48.021406),
('2156', '2201', '2205', 'POINT(6.052443 49.274339)', 6.0524426, 49.274339),
('2856', '2833', '2833', 'POINT(6.052443 48.024897)', 6.0524429, 48.024897),
('299', '2366', '277', 'POINT(6.052444 49.53931)', 6.0524437, 49.53931),
('1590', '1605', '1613', 'POINT(6.052444 48.114358)', 6.0524439, 48.114358),
('2723', '2693', '2704', 'POINT(6.052446 48.133191)', 6.0524464, 48.133191),
('2161', '2161', '2165', 'POINT(6.052455 49.23598)', 6.0524554, 49.23598),
('389', '1464', '393', 'POINT(6.052455 49.81593)', 6.052455, 49.81593),
('422', '2054', '972', 'POINT(6.052463 48.038103)', 6.0524625, 48.038103),
('280', '262', '262', 'POINT(6.052465 49.543518)', 6.0524651, 49.543518),
('3023', '3030', '3585', 'POINT(6.052469 49.80372)', 6.0524692, 49.80372),
('1801', '1805', '1812', 'POINT(6.052474 49.234346)', 6.0524737, 49.234346),
('778', '778', '778', 'POINT(6.052476 48.097265)', 6.0524757, 48.097265),
('3056', '3067', '3071', 'POINT(6.052477 49.835271)', 6.0524765, 49.835271),
('236', '228', '232', 'POINT(6.052479 49.280813)', 6.0524785, 49.280813),
('2635', '2628', '2628', 'POINT(6.052481 48.065105)', 6.0524807, 48.065105),
('722', '1067', '1070', 'POINT(6.052481 49.500406)', 6.0524813, 49.500406),
('3180', '3161', '3165', 'POINT(6.052482 49.821684)', 6.0524823, 49.821684),
('1715', '1704', '1707', 'POINT(6.052482 49.65774)', 6.052482, 49.65774),
('2632', '2646', '2650', 'POINT(6.052488 49.457508)', 6.0524878, 49.457508),
('504', '526', '541', 'POINT(6.052488 49.587388)', 6.0524884, 49.587388),
('2886', '2845', '2849', 'POINT(6.052492 49.635126)', 6.0524922, 49.635126),
('663', '1639', '1643', 'POINT(6.052493 48.602808)', 6.0524934, 48.602808),
('2432', '2425', '2425', 'POINT(6.052496 48.056808)', 6.0524956, 48.056808),
('2310', '2291', '2295', 'POINT(6.052496 49.947769)', 6.0524964, 49.947769),
('736', '713', '717', 'POINT(6.052498 49.746507)', 6.052498, 49.746507),
('640', '655', '658', 'POINT(6.052499 49.432765)', 6.0524985, 49.432765),
('895', '902', '906', 'POINT(6.052502 48.98875)', 6.0525019, 48.98875),
('198', '175', '179', 'POINT(6.052507 49.421704)', 6.0525071, 49.421704),
('3341', '3318', '3318', 'POINT(6.052513 49.83481)', 6.052513, 49.83481),
('548', '526', '541', 'POINT(6.052514 49.892401)', 6.0525141, 49.892401),
('411', '400', '400', 'POINT(6.052516 49.709427)', 6.0525161, 49.709427),
('280', '254', '258', 'POINT(6.052519 48.662427)', 6.0525194, 48.662427),
('1076', '2755', '2766', 'POINT(6.052523 49.639046)', 6.0525225, 49.639046),
('2617', '2231', '2647', 'POINT(6.052529 48.99769)', 6.052529, 48.99769),
('1603', '1573', '1573', 'POINT(6.052531 49.230881)', 6.0525308, 49.230881),
('1516', '1546', '1549', 'POINT(6.052533 48.114486)', 6.0525334, 48.114486),
('2560', '2537', '2541', 'POINT(6.052535 48.150236)', 6.0525351, 48.150236),
('2189', '2186', '2189', 'POINT(6.052539 49.928268)', 6.0525393, 49.928268),
('673', '688', '696', 'POINT(6.052541 49.035343)', 6.0525406, 49.035343),
('999', '984', '988', 'POINT(6.052543 49.745678)', 6.0525434, 49.745678),
('355', '333', '333', 'POINT(6.052545 49.708978)', 6.0525451, 49.708978),
('1953', '1961', '1961', 'POINT(6.052547 48.775991)', 6.0525467, 48.775991),
('2190', '2175', '2178', 'POINT(6.052548 48.248334)', 6.0525476, 48.248334),
('2138', '2111', '2126', 'POINT(6.052553 49.287733)', 6.052553, 49.287733),
('2679', '2694', '2705', 'POINT(6.052556 49.708785)', 6.0525555, 49.708785),
('534', '2833', '784', 'POINT(6.052556 48.150718)', 6.0525557, 48.150718),
('2104', '2093', '2101', 'POINT(6.052559 49.280663)', 6.0525592, 49.280663),
('1040', '2359', '1067', 'POINT(6.052561 49.848788)', 6.0525612, 49.848788),
('1918', '1933', '1936', 'POINT(6.052562 49.474871)', 6.0525618, 49.474871),
('1294', '1287', '1294', 'POINT(6.052572 48.098718)', 6.0525717, 48.098718),
('2247', '2225', '2228', 'POINT(6.052574 48.817266)', 6.0525736, 48.817266),
('1752', '2146', '1805', 'POINT(6.05258 49.245267)', 6.0525801, 49.245267),
('2874', '2885', '2889', 'POINT(6.052582 49.55241)', 6.0525817, 49.55241),
('2598', '2579', '2579', 'POINT(6.052582 48.072656)', 6.0525818, 48.072656),
('1509', '1494', '1501', 'POINT(6.052582 49.474438)', 6.0525818, 49.474438),
('399', '1584', '769', 'POINT(6.052584 49.739217)', 6.0525836, 49.739217),
('2706', '2713', '2713', 'POINT(6.05259 48.071869)', 6.0525896, 48.071869),
('373', '2480', '369', 'POINT(6.052592 49.946851)', 6.052592, 49.946851),
('280', '1416', '1195', 'POINT(6.052593 49.654598)', 6.0525928, 49.654598),
('1374', '1348', '1355', 'POINT(6.052596 49.063722)', 6.0525959, 49.063722),
('317', '1001', '295', 'POINT(6.0526 49.727454)', 6.0525999, 49.727454),
('1060', '1397', '1067', 'POINT(6.052602 49.245451)', 6.0526016, 49.245451),
('2057', '2031', '2035', 'POINT(6.052607 49.72845)', 6.0526066, 49.72845),
('1090', '1658', '1662', 'POINT(6.052608 48.589624)', 6.0526084, 48.589624),
('1879', '2561', '2284', 'POINT(6.052609 49.350351)', 6.0526088, 49.350351),
('2478', '2478', '2481', 'POINT(6.052617 48.686541)', 6.0526172, 48.686541),
('2571', '2616', '2620', 'POINT(6.052618 49.416199)', 6.0526177, 49.416199),
('639', '2501', '938', 'POINT(6.052622 49.538038)', 6.0526223, 49.538038),
('1318', '1306', '1306', 'POINT(6.052623 49.231211)', 6.052623, 49.231211),
('265', '243', '247', 'POINT(6.052624 49.796863)', 6.0526242, 49.796863),
('1333', '1371', '1371', 'POINT(6.052627 48.654885)', 6.0526265, 48.654885),
('627', '1281', '1281', 'POINT(6.052629 49.651665)', 6.0526286, 49.651665),
('313', '1181', '935', 'POINT(6.052631 49.805192)', 6.0526313, 49.805192),
('2685', '2682', '2682', 'POINT(6.052632 49.287904)', 6.0526324, 49.287904),
('2821', '2821', '2821', 'POINT(6.052641 49.76779)', 6.0526411, 49.76779),
('429', '504', '512', 'POINT(6.052646 49.231101)', 6.0526455, 49.231101),
('1135', '2039', '2043', 'POINT(6.052647 49.098008)', 6.0526474, 49.098008),
('430', '2221', '445', 'POINT(6.052649 48.814671)', 6.052649, 48.814671),
('1584', '2779', '1558', 'POINT(6.052653 49.469044)', 6.0526527, 49.469044),
('2358', '2974', '2373', 'POINT(6.052654 49.561095)', 6.0526539, 49.561095),
('2540', '2533', '2533', 'POINT(6.052666 49.743383)', 6.0526655, 49.743383),
('314', '1278', '366', 'POINT(6.052668 49.866151)', 6.0526679, 49.866151),
('2171', '2152', '2156', 'POINT(6.05267 48.01832)', 6.0526698, 48.01832),
('2254', '3677', '2254', 'POINT(6.052673 48.642002)', 6.0526734, 48.642002),
('284', '250', '254', 'POINT(6.052679 49.54364)', 6.0526793, 49.54364),
('2344', '2337', '2344', 'POINT(6.052682 49.289441)', 6.0526817, 49.289441),
('2164', '2153', '2156', 'POINT(6.052686 49.450238)', 6.0526855, 49.450238),
('619', '4933', '914', 'POINT(6.052686 48.513847)', 6.052686, 48.513847),
('606', '1798', '1573', 'POINT(6.052687 49.701503)', 6.0526865, 49.701503),
('430', '2566', '434', 'POINT(6.052694 49.26022)', 6.0526942, 49.26022),
('246', '220', '220', 'POINT(6.052696 49.731679)', 6.0526958, 49.731679),
('232', '198', '198', 'POINT(6.052697 49.426497)', 6.0526968, 49.426497),
('194', '146', '146', 'POINT(6.052704 48.625518)', 6.0527044, 48.625518),
('1618', '1614', '1644', 'POINT(6.052707 49.740588)', 6.052707, 49.740588),
('737', '737', '737', 'POINT(6.052708 49.746317)', 6.0527082, 49.746317),
('1808', '1786', '1786', 'POINT(6.052712 49.774505)', 6.0527117, 49.774505),
('2285', '2266', '2285', 'POINT(6.052712 49.725073)', 6.0527123, 49.725073),
('243', '205', '205', 'POINT(6.052725 48.044608)', 6.0527249, 48.044608),
('2914', '2903', '2903', 'POINT(6.052726 48.964599)', 6.0527255, 48.964599),
('1037', '2127', '1052', 'POINT(6.052726 48.638874)', 6.0527262, 48.638874),
('3199', '3225', '3232', 'POINT(6.052731 49.432655)', 6.0527314, 49.432655),
('2392', '2381', '2388', 'POINT(6.052732 47.983114)', 6.0527323, 47.983114),
('709', '2001', '720', 'POINT(6.052738 49.086132)', 6.0527383, 49.086132),
('3186', '3174', '3174', 'POINT(6.052741 49.537608)', 6.0527411, 49.537608),
('2523', '2501', '2504', 'POINT(6.052743 48.569315)', 6.0527431, 48.569315),
('3031', '3027', '3031', 'POINT(6.05274 49.72401)', 6.05274, 49.72401),
('2216', '2631', '2638', 'POINT(6.052746 48.126519)', 6.0527459, 48.126519),
('2717', '2698', '2698', 'POINT(6.052747 48.120725)', 6.0527467, 48.120725),
('1845', '1871', '1878', 'POINT(6.052753 49.947223)', 6.0527527, 49.947223),
('1948', '1918', '1925', 'POINT(6.052757 49.353152)', 6.0527571, 49.353152),
('2464', '2449', '2452', 'POINT(6.052765 49.444314)', 6.0527653, 49.444314),
('1644', '1622', '1633', 'POINT(6.052772 49.372386)', 6.0527722, 49.372386),
('1336', '1332', '1336', 'POINT(6.052776 48.003197)', 6.0527764, 48.003197),
('3139', '3210', '3214', 'POINT(6.052784 49.835825)', 6.0527838, 49.835825),
('314', '307', '310', 'POINT(6.052786 49.704826)', 6.0527856, 49.704826),
('3326', '3315', '3315', 'POINT(6.052792 49.809556)', 6.0527917, 49.809556),
('613', '2107', '1381', 'POINT(6.052793 49.322953)', 6.0527926, 49.322953),
('1837', '1997', '1810', 'POINT(6.052795 48.152855)', 6.0527949, 48.152855),
('2650', '2617', '2621', 'POINT(6.052798 49.841811)', 6.0527981, 49.841811),
('2815', '2812', '2812', 'POINT(6.052801 49.767462)', 6.0528009, 49.767462),
('1677', '1670', '1670', 'POINT(6.052803 49.080617)', 6.0528029, 49.080617),
('2763', '2718', '2722', 'POINT(6.052803 48.564796)', 6.0528034, 48.564796),
('2680', '2676', '2680', 'POINT(6.052808 47.996206)', 6.0528079, 47.996206),
('1123', '1119', '1126', 'POINT(6.052817 49.478398)', 6.0528166, 49.478398),
('2206', '2191', '2191', 'POINT(6.052817 48.56201)', 6.0528167, 48.56201),
('1374', '1359', '1359', 'POINT(6.052817 49.056601)', 6.0528172, 49.056601),
('2834', '3706', '3238', 'POINT(6.052817 49.268445)', 6.0528172, 49.268445),
('1423', '1408', '1415', 'POINT(6.052824 49.728622)', 6.0528244, 49.728622),
('397', '1873', '666', 'POINT(6.052829 49.816509)', 6.0528287, 49.816509),
('2830', '2830', '2834', 'POINT(6.052829 49.563441)', 6.0528292, 49.563441),
('632', '1777', '1407', 'POINT(6.05283 48.015206)', 6.0528299, 48.015206),
('726', '4011', '1063', 'POINT(6.052833 48.647872)', 6.0528327, 48.647872),
('636', '1554', '1558', 'POINT(6.052834 48.810135)', 6.0528336, 48.810135),
('1681', '2461', '1835', 'POINT(6.052836 48.668482)', 6.0528361, 48.668482),
('542', '550', '553', 'POINT(6.052836 48.127481)', 6.0528364, 48.127481),
('1115', '1081', '1089', 'POINT(6.052844 49.472474)', 6.0528444, 49.472474),
('434', '696', '471', 'POINT(6.052845 49.560267)', 6.0528449, 49.560267),
('1168', '3558', '1314', 'POINT(6.052848 49.879569)', 6.0528478, 49.879569),
('2555', '2585', '2592', 'POINT(6.052856 49.742769)', 6.0528562, 49.742769),
('2392', '3129', '3136', 'POINT(6.052858 49.645191)', 6.052858, 49.645191),
('303', '243', '247', 'POINT(6.052861 49.464195)', 6.0528607, 49.464195),
('442', '404', '415', 'POINT(6.052861 48.668274)', 6.0528614, 48.668274),
('358', '933', '358', 'POINT(6.052874 49.746258)', 6.0528736, 49.746258),
('365', '1832', '1545', 'POINT(6.052875 48.603129)', 6.0528747, 48.603129),
('2770', '2785', '2789', 'POINT(6.052875 49.468524)', 6.0528752, 49.468524),
('2665', '2673', '2673', 'POINT(6.052881 49.26344)', 6.0528811, 49.26344),
('2721', '2740', '2744', 'POINT(6.052888 48.058958)', 6.052888, 48.058958),
('508', '1607', '448', 'POINT(6.05289 49.539228)', 6.0528895, 49.539228),
('2773', '2761', '2761', 'POINT(6.052892 48.572159)', 6.0528922, 48.572159),
('1758', '1747', '1747', 'POINT(6.052894 48.609211)', 6.0528935, 48.609211),
('253', '220', '220', 'POINT(6.052895 49.732125)', 6.0528946, 49.732125),
('2753', '2757', '2757', 'POINT(6.052895 49.740967)', 6.0528949, 49.740967),
('2613', '2609', '2613', 'POINT(6.0529 49.715567)', 6.0529001, 49.715567),
('2861', '2857', '2857', 'POINT(6.0529 49.431364)', 6.0529003, 49.431364),
('539', '2672', '554', 'POINT(6.052902 49.432416)', 6.0529023, 49.432416),
('2044', '2022', '2029', 'POINT(6.052914 49.274856)', 6.052914, 49.274856),
('3738', '3723', '3727', 'POINT(6.052914 49.647951)', 6.052914, 49.647951),
('2631', '2590', '2594', 'POINT(6.052915 48.031417)', 6.0529148, 48.031417),
('1505', '1486', '1486', 'POINT(6.052916 49.06972)', 6.0529158, 49.06972),
('3238', '3234', '3242', 'POINT(6.052916 49.698897)', 6.0529158, 49.698897),
('2175', '2186', '2193', 'POINT(6.052916 49.463559)', 6.0529163, 49.463559),
('3105', '3168', '3168', 'POINT(6.052919 49.726231)', 6.0529186, 49.726231),
('298', '3108', '276', 'POINT(6.052919 49.660818)', 6.0529194, 49.660818),
('945', '1772', '1297', 'POINT(6.052923 49.702202)', 6.0529227, 49.702202),
('2219', '2219', '2219', 'POINT(6.052925 49.555282)', 6.0529247, 49.555282),
('441', '1209', '906', 'POINT(6.052926 49.537702)', 6.0529259, 49.537702),
('2259', '2247', '2247', 'POINT(6.052928 49.372715)', 6.0529281, 49.372715),
('1738', '1723', '1726', 'POINT(6.052929 48.823378)', 6.0529294, 48.823378),
('2875', '2849', '2853', 'POINT(6.05293 49.713056)', 6.0529297, 49.713056),
('299', '265', '265', 'POINT(6.052936 49.033041)', 6.0529356, 49.033041),
('561', '2165', '1176', 'POINT(6.052936 49.445461)', 6.0529357, 49.445461),
('557', '2844', '849', 'POINT(6.052937 48.050561)', 6.0529372, 48.050561),
('751', '784', '796', 'POINT(6.052941 48.12022)', 6.0529406, 48.12022),
('2944', '2918', '2922', 'POINT(6.052943 49.702868)', 6.0529434, 49.702868),
('456', '749', '749', 'POINT(6.052944 49.916463)', 6.0529443, 49.916463),
('228', '194', '194', 'POINT(6.052948 49.022233)', 6.0529478, 49.022233),
('1406', '2063', '1850', 'POINT(6.052955 48.267105)', 6.0529548, 48.267105),
('4032', '4036', '4040', 'POINT(6.052958 49.459526)', 6.0529577, 49.459526),
('1749', '1734', '1741', 'POINT(6.052959 49.239364)', 6.0529591, 49.239364),
('243', '198', '201', 'POINT(6.052963 49.706425)', 6.0529632, 49.706425),
('2584', '2558', '2565', 'POINT(6.052964 49.587881)', 6.0529639, 49.587881),
('753', '1484', '757', 'POINT(6.052964 49.905643)', 6.0529639, 49.905643),
('2250', '3242', '3250', 'POINT(6.052965 48.996106)', 6.0529646, 48.996106),
('3225', '3210', '3210', 'POINT(6.052966 49.822338)', 6.0529661, 49.822338),
('247', '2498', '183', 'POINT(6.052969 49.550064)', 6.0529685, 49.550064),
('408', '427', '430', 'POINT(6.05297 48.982876)', 6.0529701, 48.982876),
('844', '829', '833', 'POINT(6.052971 49.728252)', 6.0529708, 49.728252),
('571', '3268', '1953', 'POINT(6.052973 49.72167)', 6.0529729, 49.72167),
('220', '179', '179', 'POINT(6.052981 48.066497)', 6.0529809, 48.066497),
('2048', '2040', '2040', 'POINT(6.052982 49.237185)', 6.0529821, 49.237185),
...]
Cursors are not idempotent. Guess what happens now! Now explain the name ;-)
[38]:
ls_again = res.fetchall()
ls_again
[38]:
[]
So what is the correct thing to do now?
[39]:
res = cur.execute('SELECT * FROM eu_y_x_select_6algorithms_fullTable LIMIT 5')
ls = res.fetchall()
ls
[39]:
[(1,
'1',
6.050001,
49.727499,
'3139',
'3139',
'3139',
'3120',
'3139',
'3139',
'3120',
'3139',
'5',
'4.1e+02',
'383.72153',
'1',
'1',
'1',
'1',
'1',
'1',
'0.962',
'0.984',
'0.968',
'0.962',
'0.989',
'0.979',
'0',
'17.7',
b'\x00\x01\x00\x00\x00\x00\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@|\x01\x00\x00\x00\xb6\x11Ov33\x18@\xb5o\xee\xaf\x1e\xddH@\xfe'),
(2,
'2',
6.0500017,
49.922155,
'1022',
'2303',
'970',
'872',
'5596',
'1524',
'872',
'5596',
'5',
'2.9e+02',
'2374.1411',
'0',
'0',
'0',
'0',
'0',
'0',
'0.948',
'0.990',
'0.960',
'0.948',
'0.994',
'0.980',
'0',
'43.7',
b'\x00\x01\x00\x00\x00\x00E\xfaH\xa533\x18@\xe3k\xcf,\t\xf6H@E\xfaH\xa533\x18@\xe3k\xcf,\t\xf6H@|\x01\x00\x00\x00E\xfaH\xa533\x18@\xe3k\xcf,\t\xf6H@\xfe'),
(3,
'3',
6.0500021,
48.602377,
'380',
'1336',
'332',
'362',
'1336',
'1340',
'332',
'1340',
'4',
'4.4e+02',
'435.97781',
'1',
'1',
'1',
'1',
'1',
'1',
'0.947',
'0.975',
'0.956',
'0.947',
'0.981',
'0.968',
'0',
'0.2',
b'\x00\x01\x00\x00\x00\x00\xe0\xec \xc033\x18@in\x85\xb0\x1aMH@\xe0\xec \xc033\x18@in\x85\xb0\x1aMH@|\x01\x00\x00\x00\xe0\xec \xc033\x18@in\x85\xb0\x1aMH@\xfe'),
(4,
'4',
6.0500089,
48.151979,
'3153',
'3142',
'3142',
'3127',
'3138',
'3142',
'3127',
'3153',
'2',
'4.5e+02',
'422.00537',
'1',
'1',
'1',
'1',
'1',
'1',
'0.930',
'0.970',
'0.943',
'0.930',
'0.978',
'0.962',
'0',
'-14.2',
b'\x00\x01\x00\x00\x00\x00&\tx\x8853\x18@\xe2VA\x0ct\x13H@&\tx\x8853\x18@\xe2VA\x0ct\x13H@|\x01\x00\x00\x00&\tx\x8853\x18@\xe2VA\x0ct\x13H@\xfe'),
(5,
'5',
6.0500102,
49.58841,
'666',
'4221',
'651',
'33',
'5611',
'2723',
'33',
'5611',
'8',
'3.7e+02',
'2413.7483',
'0',
'0',
'0',
'0',
'0',
'0',
'0.941',
'0.983',
'0.946',
'0.941',
'0.992',
'0.969',
'0',
'22.1',
b'\x00\x01\x00\x00\x00\x00\x9d\xdd\xb5\xdf53\x18@\xd7Q\xd5\x04Q\xcbH@\x9d\xdd\xb5\xdf53\x18@\xd7Q\xd5\x04Q\xcbH@|\x01\x00\x00\x00\x9d\xdd\xb5\xdf53\x18@\xd7Q\xd5\x04Q\xcbH@\xfe')]
Something useful is selecting a different so called row factory for cursors to get dictionaries instead of lists.
[40]:
import sqlite3
db.row_factory = sqlite3.Row
[41]:
res = db.execute('SELECT * from eu_y_x_select_6algorithms_fullTable')
row = res.fetchone()
row
[41]:
<sqlite3.Row at 0x76cddc22b670>
[42]:
row.keys()
[42]:
['ogc_fid',
'id',
'x',
'y',
'a1_95',
'a2_95',
'a3_95',
'a4_95',
'a5_95',
'a6_95',
'min_rh_95',
'max_rh_95',
'beam',
'digital_elev',
'elev_low',
'qc_a1',
'qc_a2',
'qc_a3',
'qc_a4',
'qc_a5',
'qc_a6',
'se_a1',
'se_a2',
'se_a3',
'se_a4',
'se_a5',
'se_a6',
'deg_fg',
'solar_ele',
'GEOMETRY']
[43]:
row[0]
[43]:
1
[44]:
row['ogc_fid'],row['deg_fg']
[44]:
(1, '0')
So, a typical code looks like this:
[ ]:
for row in res.fetchmany(size=5):
print(row['id'])
Rasterlite, mixing rasters and databases together
As explained before, it also possibile merging raster dataset within the database, as an alternative to distributing files. The rasterlite engine supports colortable, internal pyramids and tiling, so it is unexpectedly efficient, with a small memory footprint for its operations. First of all, it is required installing a few stuff.
sudo apt install rasterlite2-bin libtiff-tools
Note that a previous version had a completely different toolset and implementation. What follows is about version 2. Version 1 is officially deprecated.
[50]:
!rl2tool 2>&1 # Do you know stderr vs stdout difference?
did you forget setting some request MODE ?
usage: rl2tool MODE [ ARGLIST ]
==============================================================
-h or --help print this help message
-v or --version print version infos
mode: CREATE
will create a new RasterLite2 Raster Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-smp or --sample-type keyword Sample Type keyword (see list)
-pxl or --pixel-type keyword Pixel Type keyword (see list)
-bds or --num-bands integer Number of Bands
-cpr or --compression keyword Compression keyword (see list)
-qty or --quality integer Compression Quality [0-100]
-tlw or --tile-width integer Tile Width [pixels]
-tlh or --tile-height integer Tile Height [pixels]
-srid or --srid integer SRID value
or
-nosrid or --no-srid
-res or --resolution number pixel resolution(X and Y)
-xres or --x-resol number pixel resolution(X specific)
-yres or --y-resol number pixel resolution(Y specific)
-nd or --no-data pixel NO-DATA pixel value
SampleType Keywords:
----------------------------------
1-BIT 2-BIT 4-BIT INT8 UINT8 INT16 UINT16
INT32 UINT32 FLOAT DOUBLE
PixelType Keywords:
----------------------------------
MONOCHROME PALETTE GRAYSCALE RGB MULTIBAND DATAGRID
Compression Keywords:
----------------------------------
NONE DEFLATE DEFLATE_NO LZMA LZMA_NO LZ4 LZ4_NO ZSTD ZSTD_NO PNG JPEG WEBP LL_WEBP FAX4 JP2 LL_JP2
Extra args supported by MULTIBAND:
----------------------------------
-red or --red-band pixel RED band index
-green or --green-band pixel GREEN band index
-blue or --blue-band pixel BLUE band index
-nir or --nir-band pixel NIR band index
-ndvi or --auto-ndvi boolean Enabling/Disabling Auto NDVI
-strict or --strict-resolution Enables Strict Resolution
-mixed or --mixed-resolution Enables Mixed Resolutions
-paths or --input-paths Enables Input Path recording
-nomd5 or --no-input-md5 Disables Input MD5 checksum
-noxml or --no-xml-summary Disables Input MXL Summariy
mode: DROP
will drop an existing RasterLite2 Raster Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
mode: IMPORT
will create a new Raster Section by importing an
external image or raster file
==============================================================
-db or --db-path pathname RasterLite2 DB path
-src or --src-path pathname input Image/Raster path
-dir or --dir-path pathname input directory path
-ext or --file-ext extension file extension (e.g. .tif)
-cov or --coverage string Coverage's name
-srid or --srid integer optional: force SRID value
-wf or --worldfile requires a Worldfile
-pyr or --pyramidize immediately build Pyramid levels
mode: EXPORT
will export an external image from a Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-dst or --dst-path pathname output Image/Raster path
-cpr or --compression keyword TIFF Compression (see list)
-cov or --coverage string Coverage's name
-base or --base-resolution base resolution
-res or --resolution number pixel resolution(X and Y)
-xres or --x-resol number pixel resolution(X specific)
-yres or --y-resol number pixel resolution(Y specific)
-minx or --min-x number X coordinate (lower-left corner)
-miny or --min-y number Y coordinate (lower-left corner)
-maxx or --max-x number X coordinate (upper-right corner)
-maxy or --max-y number Y coordinate (upper-left corner)
-cx or --center-x number X coordinate (center)
-cy or --center-y number Y coordinate (center)
-outw or --out-width number image width (in pixels)
-outh or --out-height number image height (in pixels)
In order to export a raster you are expected to specify:
- the intended resolution (-res OR -xres AND -yres)
- the output image size (-outw AND -outh)
- a single tie-point, defined as one of:
- Output Image Center point: -cx AND -cy
- Output Image LowerLeft corner: -minx AND -miny
- Output Image LowerRight corner: -maxx AND -miny
- Output Image UpperLeft corner: -minx AND -maxy
- Output Image UpperRight corner: -maxx AND -maxy
TIFF Compression Keywords:
----------------------------------
NONE DEFLATE LZMA JPEG LZW FAX3 FAX4
mode: SECTION-EXPORT
will export an external image from a Coverage/Section
==============================================================
-db or --db-path pathname RasterLite2 DB path
-dst or --dst-path pathname output Image/Raster path
-cpr or --compression keyword TIFF Compression (see list)
-cov or --coverage string Coverage's name
-sec or --section-name string Section's name
-sid or --section-id number Section's ID
-base or --base-resolution base resolution
-res or --resolution number pixel resolution(X and Y)
-xres or --x-resol number pixel resolution(X specific)
-yres or --y-resol number pixel resolution(Y specific)
-full or --full-section Full Section's extent: both
Width and Height will be automatically computed
accordingly to resolution.
-minx or --min-x number X coordinate (lower-left corner)
-miny or --min-y number Y coordinate (lower-left corner)
-maxx or --max-x number X coordinate (upper-right corner)
-maxy or --max-y number Y coordinate (upper-left corner)
-cx or --center-x number X coordinate (center)
-cy or --center-y number Y coordinate (center)
-outw or --out-width number image width (in pixels)
-outh or --out-height number image height (in pixels)
In order to export a raster you are expected to specify:
- the intended resolution (-res OR -xres AND -yres)
- the output image size (-outw AND -outh)
- a single tie-point, defined as one of:
- Output Image Center point: -cx AND -cy
- Output Image LowerLeft corner: -minx AND -miny
- Output Image LowerRight corner: -maxx AND -miny
- Output Image UpperLeft corner: -minx AND -maxy
- Output Image UpperRight corner: -maxx AND -maxy
TIFF Compression Keywords:
----------------------------------
NONE DEFLATE LZMA JPEG LZW FAX3 FAX4
mode: DELETE
will delete a Raster Section
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-sec or --section-name string Section's name
-sid or --section-id number Section's ID
mode: PYRAMIDIZE
will (re)build all Pyramid levels supporting a Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-sec or --section-name string optional: Section's name
-sid or --section-id number optional: Section's ID
default is "All Sections"
-f or --force optional: rebuilds from scratch
mode: PYRAMIDIZE-MONOLITHIC
will (re)build all Pyramid levels (Monolithic) supporting a Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-lev or --virt-levels number number of virt-levels
could be one of: 1, 2 or 3
mode: DE-PYRAMIDIZE
will delete Pyramid levels
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-sec or --section-name string optional: Section's name
-sid or --section-id number optional: Section's ID
default is "All Sections"
mode: LIST
will list Raster Sections within a Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-sec or --section-name string optional: Section's name
-sid or --section-id number optional: Section's ID
default is "All Sections"
mode: MAP
will output a PNG Map representing all Raster Sections
within a Coverage
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-dst or --dst-path pathname output Image/Raster path
-outw or --out-width number image width (in pixels)
-outh or --out-height number image height (in pixels)
mode: CATALOG
will list all Coverages from within a RasterLite2 DB
==============================================================
-db or --db-path pathname RasterLite2 DB path
mode: HISTOGRAM
will create a PNG showing a band Histogram
==============================================================
-db or --db-path pathname RasterLite2 DB path
-cov or --coverage string Coverage's name
-sec or --section-name string optional: Section's name
-sid or --section-id number optional: Section's ID
default is "Coverage statistics"
-bnd or --band-index integer a valid band index
default is band index 0
-dst or --dst-path pathname output PNG path
default is ./hist_cov_sec_idx.png
optional DB specific settings:
==============================================================
-mt or --max-threads num max number of concurrent threads
-cs or --cache-size num DB cache size (how many pages)
-m or --in-memory using IN-MEMORY database
-jo or --journal-off unsafe [but faster] mode
Some general information: a limited number of formats is directly supported, that is (Geo)TIFF, JPG and Grid with an optional world file to describe a geotransform. The coverage is mainly tought as a compact prêt-à-porter distribution with styles included. For instance, a very simple WMS server (wmslite) is one of the include tools for visualization from a conventional desktop GIS such as QGIS.
A simple example with rasterlite2
[51]:
!gdalinfo -stats geodata/dem/GMTED2010.tif
Driver: GTiff/GeoTIFF
Files: geodata/dem/GMTED2010.tif
geodata/dem/GMTED2010.tif.aux.xml
Size is 720, 600
Coordinate System is:
GEOGCRS["WGS 84",
ENSEMBLE["World Geodetic System 1984 ensemble",
MEMBER["World Geodetic System 1984 (Transit)"],
MEMBER["World Geodetic System 1984 (G730)"],
MEMBER["World Geodetic System 1984 (G873)"],
MEMBER["World Geodetic System 1984 (G1150)"],
MEMBER["World Geodetic System 1984 (G1674)"],
MEMBER["World Geodetic System 1984 (G1762)"],
MEMBER["World Geodetic System 1984 (G2139)"],
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]],
ENSEMBLEACCURACY[2.0]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
USAGE[
SCOPE["Horizontal component of 3D system."],
AREA["World."],
BBOX[-90,-180,90,180]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Origin = (29.000000000000000,4.000000000000000)
Pixel Size = (0.008333333340000,-0.008333333340000)
Metadata:
AREA_OR_POINT=Area
Image Structure Metadata:
INTERLEAVE=BAND
Corner Coordinates:
Upper Left ( 29.0000000, 4.0000000) ( 29d 0' 0.00"E, 4d 0' 0.00"N)
Lower Left ( 29.0000000, -1.0000000) ( 29d 0' 0.00"E, 1d 0' 0.00"S)
Upper Right ( 35.0000000, 4.0000000) ( 35d 0' 0.00"E, 4d 0' 0.00"N)
Lower Right ( 35.0000000, -1.0000000) ( 35d 0' 0.00"E, 1d 0' 0.00"S)
Center ( 32.0000000, 1.5000000) ( 32d 0' 0.00"E, 1d30' 0.00"N)
Band 1 Block=720x5 Type=Int16, ColorInterp=Gray
Min=523.000 Max=4788.000
Minimum=523.000, Maximum=4788.000, Mean=1146.933, StdDev=318.701
NoData Value=-32768
Metadata:
STATISTICS_MAXIMUM=4788
STATISTICS_MEAN=1146.9329444444
STATISTICS_MINIMUM=523
STATISTICS_STDDEV=318.7007155709
STATISTICS_VALID_PERCENT=100
Our goal is loading this simple coverage in an ad-hoc Rasterlite database
[52]:
! rm -f /tmp/dem.sqlite
! rl2tool CREATE -db /tmp/dem.sqlite -cov GMTED2010 -smp INT16 -pxl DATAGRID -cpr LZMA -srid 4326 -res 0.008333333340000 -nd -32768
rl2tool: request is CREATE
===========================================================
DB path: /tmp/dem.sqlite
Coverage: GMTED2010
Sample Type: INT16
Pixel Type: DATAGRID
Number of Bands: 1
NO-DATA pixel: -32768
Compression: LZMA DeltaFilter (7-zip, lossless)
Tile size (pixels): 512 x 512
Srid: 4326
Pixel base resolution: X=0.00833333334 Y=0.00833333334
======= Coverage Policies =======
Strict Resolution check: Disabled
Mixed Resolutions mode: Disabled
Section's Input Paths: Disabled
Section's MD5 Checksum: Enabled
Section's XML Summary: Enabled
===========================================================
SQLite version: 3.37.2
SpatiaLite version: 5.0.1
RasterLite2 version: 1.1.0-beta1
Raster Coverage "GMTED2010" successfully created
Operation CREATE successfully completed
Now it is possible to load the GeoTIFF file:
[53]:
! rl2tool IMPORT -db /tmp/dem.sqlite -cov GMTED2010 -srid 4326 -wf -src geodata/dem/GMTED2010.tif -pyr
rl2tool; request is IMPORT
===========================================================
DB path: /tmp/dem.sqlite
Input Source path: geodata/dem/GMTED2010.tif
Coverage: GMTED2010
Using the WorldFile
Forced SRID: 4326
Immediately building Pyramid Levels
===========================================================
SQLite version: 3.37.2
SpatiaLite version: 5.0.1
RasterLite2 version: 1.1.0-beta1
------------------
Importing: geodata/dem/GMTED2010.tif
Image Size (pixels): 720 x 600
SRID: 4326
LowerLeft Corner: X=29.00 Y=-1.00
UpperRight Corner: X=35.00 Y=4.00
Pixel resolution: X=0.00833333334 Y=0.00833333334
>> Image successfully imported in: 0 mins 01 secs
----------
Pyramid levels successfully built for Section 1
>> Total time: 0 mins 01 secs
Operation IMPORT successfully completed
Styling needs to be specified using a SLD (OGC standard, also managed in Qgis).
[54]:
# spatialite-gui /tmp/dem.splite
[55]:
! cat etopo1_styles/etopo.xml
<?xml version="1.0" encoding="UTF-8"?>
<RasterSymbolizer version="1.1.0" xsi:schemaLocation="http://www.opengis.net/se http://schemas.opengis.net/se/1.1.0/Symbolizer.xsd" xmlns="http://www.opengis.net/se" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>etopo</Name>
<Description>
<Title>ETOPO1 Color Map</Title>
<Abstract>derived from the original "etopo2" color rule (GRASS GIS)</Abstract>
</Description>
<Opacity>1.0</Opacity>
<ColorMap>
<Interpolate fallbackValue="#ffffff">
<LookupValue>Rasterdata</LookupValue>
<InterpolationPoint>
<Data>-11000.00000000</Data>
<Value>#000000</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>-5000.00000000</Data>
<Value>#000064</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>-1000.00000000</Data>
<Value>#3232c8</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>-1.00000000</Data>
<Value>#9696ff</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>0.00000000</Data>
<Value>#009600</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>270.00000000</Data>
<Value>#5aa55a</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>300.00000000</Data>
<Value>#5aaf5a</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>500.00000000</Data>
<Value>#32b432</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>500.00000000</Data>
<Value>#46aa46</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>1000.00000000</Data>
<Value>#46914b</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>1000.00000000</Data>
<Value>#469b4b</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>2000.00000000</Data>
<Value>#969c64</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>3000.00000000</Data>
<Value>#dcdcdc</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>4000.00000000</Data>
<Value>#f5f5f5</Value>
</InterpolationPoint>
<InterpolationPoint>
<Data>8850.00000000</Data>
<Value>#ffffff</Value>
</InterpolationPoint>
</Interpolate>
</ColorMap>
</RasterSymbolizer>
[ ]: