Coordinate epoch in spatialite DBs ?

57 views
Skip to first unread message

Even Rouault

unread,
May 24, 2021, 2:22:32 PMMay 24
to spatiali...@googlegroups.com

Hi,

I've recently worked in adding coordinate epoch support for dynamic CRS for various geospatial raster and vector formats handled by GDAL in https://github.com/OSGeo/gdal/pull/3827 and https://github.com/rouault/gdal/blob/coordinate_epoch/gdal/doc/source/user/coordinate_epoch.rst

There's an intro about dynamic CRS in https://docs.opengeospatial.org/as/18-005r4/18-005r4.html#74, and a whole PDF about it in https://www.iogp.org/bookstore/product/geomatics-guidance-note-25-dynamic-versus-static-crss-and-use-of-the-itrf/ (gratis once subscribed)

So the question is: what about Spatialite ?

GeoPackage has a pending enhancement to add an "epoch" DOUBLE column to its gpkg_spatial_ref_sys table. See https://github.com/opengeospatial/geopackage/pull/600

The topic is also discussed for PostGIS in https://trac.osgeo.org/postgis/ticket/4911

Even

-- 
http://www.spatialys.com
My software is free, but my time generally not.

Alessandro Furieri

unread,
May 26, 2021, 5:51:57 AMMay 26
to SpatiaLite Users
Hi Even,

the idea to support dynamic CRS surely is a very interesting one.
it probably could not be a matter of the most urgent priority for many
average users, but correctly evaluating the effects of plate tectonics 
becomes a practical problem when precious geospatial data collected 
along different years must be preserved for long term storage.

my first preliminary technical considerations:

a) the SpatiaLite's own binary encoding for Geometries already stores
   an INT-32 field for defining the SRID
   
b) the simplest and most elegant solution for supporting dynamic CRS
   seems to be the one to introduce more Geometry Classes supporting 
   a further SINGLE PRECISION field (4 bytes) for storing the EPOCH
   
quick example; we currently have the following classes:

      3 = POLYGON XY
   1003 = POLYGON XYZ
   2003 = POLYGON XYM
   3003 = POLYGON XYZM
1000003 = COMPRESSED POLYGON
1001002 = COMPRESSED POLYGON Z
1002002 = COMPRESSED POLYGON M
1003002 = COMPRESSED POLYGON ZM

so we'll simply have to support the following further classes:

2000003 = POLYGON XY (WITH EPOCH)
2001003 = POLYGON XYZ (WITH EPOCH)
2002003 = POLYGON XYM (WITH EPOCH)
2003003 = POLYGON XYZM (WITH EPOCH)
2000003 = COMPRESSED POLYGON (WITH EPOCH)
2001002 = COMPRESSED POLYGON Z (WITH EPOCH)
2002002 = COMPRESSED POLYGON M (WITH EPOCH)
2003002 = COMPRESSED POLYGON ZM (WITH EPOCH)


Consequences and impact on cross-version compatibility
============================================================
- implementing such a modification is more boring than complex;
  just several patches into the codec supporting the binary 
  encoding will be required.
  
- the signatures of few SQL functions will then require to  
  be accordingly extended, as e.g.
  ST_GeomFromText( wkt String [[ , SRID Integer] , [EPOCH Double]] )
  ST_GeomFromWKB( wkbGeometry Binary [[ , SRID Integer] , [EPOCH Double]] )
  MakePoint( x Double , y Double , [[ , SRID Integer] , [EPOCH Double]] ) 
  SetSRID( geom Geometry , SRID Integer , [EPOCH Double] ) 
  ST_Epoch( geom Geometry )
  
- supporting a couple of CAST functions for swiftly converting
  geometries between the old and new encodings, as e.g.
  CastToEpochGeometry( geom Geometry , EPOCH Double )
  CastFromEpochGeometry ( geom Geometry )
  
- ST_Transform() will surely require most accurate considerations.

- PROS: directly incorporating the Epoch definition inside each
  single Geometry allows for storing data collected in different
  epochs into the same DB Table, which appears to be a highly 
  desirable feature. and has to bonus effect to avoid any
  change affecting "geometry_columns"
  
- CONS: future versions of SpatiaLite will continue to support
  in the most painless way any DB-file created by previous
  versions. Accessing any new DB-file containing Epoch-aware
  geometries will be absolutely impossible for all previous
  versions, but there is no solution for avoiding such an issue.
  
  
Few final considerations about "spatial_ref_sys"
============================================================
this table played a critical role in the past PROJ.4 era,
but it's no longer strictly required.
nowadays all the geodetic transformations are expected to
be determined by data contained into the PROJ's own SQLite 
database accompanying each new version of this library.

"spatial_ref_sys" still continues to be supported just
for the following reasons:

a. allow to previous versions of SpatiaLite based on PROJ.4
   to access new DB-files created by more recent versions  
   of PROJ (6, 7, 8 etc). 
   
b. "geometry_columns" has a Foreign Key constraint on
   its SRID column that requires to reference a 
   corresponding Primary Key defined by some other
   table.
   
c. the SRTEXT column contains the SRS WKT definitions
   to be copied into the .PRJ files supporting exported
   Shapefiles.
   
I presume that in the next 2-3 years we could finally get 
rid of any legacy support, assuming that almost all users 
will upgrade their SpatiaLite versions in the meanwhile,
and this will definitely resolve point a)

For what concerns point b) we just need a very skeletal
table just enumerating all the supported SRIDs: the only
required columns are AUTH_NAME, SRID and REF_SYS_NAME.
We could greatly simplify the initialization of this
table when creating a new DB-file by simply copying
the content of "crs_view" from the PROJ own DB-file.

extra columns such as PROJ4TEXT and SRTEXT (when and
if needed) could be then provided by a companion VIEW
that will provide the required fields by calling on
the fly the appropriate APIs provided by PROJ.

bye Sandro

Even Rouault

unread,
May 26, 2021, 6:08:44 AMMay 26
to spatiali...@googlegroups.com, Alessandro Furieri

Hi Sandro,

That's a workable plan although I'm a bit sad that the solutions for GeoPackage (new column in gpkg_spatial_ref_sys), PostGIS (not adopted yet, but perhaps an extra column in a feature table) and Spatialite (new geometry types) diverge.

A few observations:

- what about rasters... ? Having the epoch in spatial_ref_sys would make it also immediately available to raster.

- I'd suggest perhaps the new geometry types, if that's what is adopted, to have provision for future extensions. Like have in the header a sequence of (extension_code, length_of_extension) (could be one byte each, preceded by a byte giving the number of tuples), where extension_code here would COORDINATE_EPOCH=1 and length_of_extension=sizeof(double)=8). That way backward and forward compatibility would be ensured.

- I don't think you can get rid of spatial_ref_sys as a table. We'll still want people to be able to store their custom CRS with their own WKT

Even

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/984934da-4246-4bab-a56f-c5690aff5200n%40googlegroups.com.

Even Rouault

unread,
May 26, 2021, 6:12:57 AMMay 26
to spatiali...@googlegroups.com, Alessandro Furieri

Amendment regarding my suggestion of the sequence of (extension_code, length_of_extension): I'd suggest for length_of_extension to use a variable-size integer scheme (so 0-127 would fit in one byte, 128-16383 in 2 bytes, etc.) to allow potential large extensions.

a.fu...@lqt.it

unread,
May 26, 2021, 6:24:22 AMMay 26
to spatiali...@googlegroups.com
On Wed, 26 May 2021 12:08:40 +0200, Even Rouault wrote:
> A few observations:
>
> - what about rasters... ? Having the epoch in spatial_ref_sys would
> make it also immediately available to raster.
>

Hi Even,

in RasterLite2 all Raster tiles and sections are supported by
an individual Geometry representing the corresponding BBOX,
so it will completely painless supporting epochs.

even better: also in the case of very complex coverages
based on many separate GeoTIFFs (I'm thinking of the
700+ GeoTiffs composing the whole coverage of ortophotos
of Tuscany Region) we'll be able to faitfully store
the actual EPOCH of each photogram imported into the
Coverage.

bye Sandro

Nyall Dawson

unread,
May 27, 2021, 6:50:10 PMMay 27
to SpatiaLite Users
On Wednesday, 26 May 2021 at 7:51:57 pm UTC+10 Alessandro Furieri wrote:

- PROS: directly incorporating the Epoch definition inside each
  single Geometry allows for storing data collected in different
  epochs into the same DB Table, which appears to be a highly 
  desirable feature.

Are you aware of anyone who has specifically asked for this functionality?

This was a topic which was discussed in depth by a working group during the writing of https://north-road.com/wp-content/uploads/2020/01/handling_of_4d_coordinates_in_desktop_gis.pdf , consisting of members of various interested bodies (including representatives from ESRI). The consensus was that this is NOT desirable functionality and that per-dataset epoch is a better solution to the problem. (Specifically see 3.16 - Business Requirement 16 on page 25.)

Nyall



Reply all
Reply to author
Forward
0 new messages