Difference between revisions of "ARK2/Spatial"
From ARK
(→Requirements) |
(→Design) |
||
| (9 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
== Requirements == | == Requirements == | ||
| Line 26: | Line 24: | ||
Again, support for spatial search and processing is uneven: | 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 | * 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, | + | * 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 | * 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) | * PostgreSQL and SQLite support spatial functions but only by adding extensions (PostGIS and Spatialite) | ||
| Line 36: | Line 34: | ||
* On PostgreSQL and SQLite if GEOS is available then it will be used on the original data | * 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 | * 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. | 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 == | == 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/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/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. | * https://github.com/symm/gisconverter (and several others) - Various file importer libraries with partial OpenGIS class support but no functions. | ||
| Line 51: | Line 54: | ||
== Platform Support == | == Platform Support == | ||
| − | * MySql >= 5.5 supports 2D geometry, >= 5.6 spatial processing, >= 5.7.6 for spatial indexing | + | * 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, | + | * 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 | * PostGIS native 3D geometry, spatial index, and spatial processing | ||
* Spatialite native 3D geometry, spatial index, and spatial processing | * Spatialite native 3D geometry, spatial index, and spatial processing | ||
Latest revision as of 16:00, 21 September 2021
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
