Coordinate epoch in spatialite DBs ?

[Even Rouault]

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]

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]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/6596b678-1312-126f-c159-387f45ffbaba%40spatialys.com.

[a.fu...@lqt.it]

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]

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