ARK2/Spatial

Requirements

Basic spatial support is required, e.g. storing find points or area bounding boxes, as well as the advanced mapping interface options, such as spatial search and processing. We use the OpenGIS standard as defined by the OGC and implemented by various spatial databases.

Basic back-end support:

• Lossless storage of standard geometries, including CRS
• Validate geometries
• Interchange geometries via API

Advanced back-end support:

• Spatial search
• Spatial processing
• File import/export

Design

The lossless storage and interchange of spatial data is a core function, but cross-database support is poor:

• MySQL/MariaDB has a native GEOMETRY type, but this is only 2D
• PostgreSQL and SQLite have 3D support, but only by adding extensions (PostGIS and Spatialite)
• Adding extensions may be difficult on hosted platforms, or the additional spatial functions provided may not be needed by a site

The lossless storage of geometries in the database therefore needs to use standard datatypes in either WKT or WKB format. The downside to this is that the data cannot then be used for spatial search or processing within the database. If spatial search or processing is required, then the data will need to be duplicated in proper spatial fields.

Again, support for spatial search and processing is uneven:

• MySQL supports standard SQL spatial functions from 5.6 onwards, and spatial indexes for InnoDB from 5.7.6 onwards
• MariaDB supports standard SQL spatial functions from 5.5 onwards, and spatial indexes for InnoDB from 10.2.2 onwards
• Both MySQL and MariaDB support spatial indexes in MyISAM from 5.5 onwards
• PostgreSQL and SQLite support spatial functions but only by adding extensions (PostGIS and Spatialite)

As spatial search and processing are extended functions, enabling them will require installation on an appropriate platform, however fallbacks will be provided to the extent a platform supports them:

• On PostGIS and Spatialite the full native facilities will be used on a copy of the data
• On MySQL between 5.6 and 5.7.6 and on MariaDB, a MyISAM table will be used to store a copy in a GEOMETRY field with a spatial index and processed using the native spatial functions
• On MySQL 5.5, a MyISAM table will also be used, but the functions will either be the limited set available, or using GEOS if available
• On PostgreSQL and SQLite if GEOS is available then it will be used on the original data
• Otherwise spatial search and processing will be unavailable
• An option is to ship Spatialite with ARK and run it as the spatial engine only

A useful side-effect of this strategy is that the spatial search table could be stored on a separate database server, such as a local Spatialite instance or a shared PostGIS server.

Idea to investigate: if using Redis for cache, then has geospatial cache which can be used for spatial search in bounding box or radius.

Implementation

A number of OpenGIS compliant libraries do exist, but none that meet our full requirements:

• https://github.com/brick/geo - OpenGIS geometry library, requires GEOS, MySQL, PostGIS, or Spatialite. Has WKT and WKB support, but no file import/export support. Provides DBAL data types. Current but low-level maintenance, but not widely used.
• https://github.com/phayes/geoPHP - OpenGIS geometry library, internal routines but supports GEOS, lots of basic file import/export 2D only, effectively unmaintained but widely used, would need to fork and do major cleanups.
• https://github.com/creof/doctrine2-spatial - Modern, currently maintained, Doctrine ORM integration, but MySQL and PostGIS only. Could write backends for spatialite and GEOS?
• https://github.com/symm/gisconverter (and several others) - Various file importer libraries with partial OpenGIS class support but no functions.

Likely solution will be to use Brick and fork various file libraries to work with it. May also fork geoPHP to work as a Brick backend? Alternative is major refactor of geoPHP, or piecemeal integration of each converter library.

Front-end support will continue to use OpenLayers.

Platform Support

• MySql >= 5.5 supports 2D geometry & MyISAM spatial index, >= 5.6 spatial processing, >= 5.7.6 for InnoDB spatial indexing
• MariaDB >= 5.5 supports 2D? geometry and spatial processing natively, spatial index in InnoDB >= 10.2.2 https://mariadb.com/kb/en/mysqlmariadb-spatial-support-matrix/
• PostGIS native 3D geometry, spatial index, and spatial processing
• Spatialite native 3D geometry, spatial index, and spatial processing
• Postgresql no support, fake using WKT storage
• SQLite no support, fake using WKT storage
• GEOS provides spatial processing