General info #

In UnityBase@5.24.27 basic GIS (geometry) support is added.

Server, ORM and DDL generator support two new attribute types Geography and Geometry. Attribute definition example:

{
  "name": "geoPoint",
  "dataType": "Geometry",
  "caption": "Geo Point",
  "customSettings": {
    "geoTypeModifier": "PointZ",
    "geoSRIDModifier": "4326"
  }
}

geoTypeModifier and geoSRIDModifier is optional.

For select ORM always returns GeoJSON representation of data, for insert/update both GeoJSON and Text representations are acceptable.

Setup databases #

Sqlite #

SpatiaLite SQL functions reference list - https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

sudo apt install libsqlite3-mod-spatialite

In ubConfig On the connection level add load_extension

"executeWhenConnected": [
  "select load_extension('mod_spatialite')"
]

ubcli initDB -create will initialize spatial metadata automatically, in case database already exists it should be initialized manually

ubcli execSql -sql "SELECT InitSpatialMetaData(1)"

Postgres #

In case application uses attributes of type geometry, postgis extension must be installed on Postgres server, see Installing PostGIS

Indexing #

DDL generator do not add spatial indexes. It should be created manually (in migrations)

-- SQLite
select CreateSpatialIndex('table', 'column')
-- Postgres
create index GIDX_TGEO_GEO on tstgeo_geometry using GIST(geo)

To prevent DDL generator to drop such indexes it name should be added into dbExtensions entity metadata section with type OTHER:

"dbExtensions": {
  // notes to DDL generator to not drop MANUALLY created GIST index
  "GIDX_TGEO_GEO": {
    "type": "OTHER"
  }
}

GIDX_TGEO_GEO