Support creation of Spatial Index of GeoJSON data stored in JSON documents

Spatial indexes are important in any application where storage of geographical information like coordinates is needed. In such cases, efficient query of geo data implies use of R-TREE / spatial indexes.

For encoding of geographical data in a collection, the GeoJSON format is a suitable standard is also supported by MySQL. GeoJSON data is to be converted to the GEOMETRY datatype, which can then be indexed.

FR1

for the client it MUST be possible to create spatial indexes on GeoJSON data

FR2

for the client it MUST be possible to specify parameters for the GeoJSON parsing function (ST_GeomFromGeoJSON), such as options and srid

FR3

if the client provides a docpath that does not refer to a valid GeoJSON sub-document, a error MUST be returned

FR4

if the client doesn't provide a column-type for a GEOJSON index, it defaults to GEOMETRY

Background

The create_collection_index StmtExecute command of the mysqlx namespace is used to create indexes for collections.

See: admin_cmd_handler.cc

It currently accepts the following list of arguments

• schema: string
• collection: string
• name: string
• unique: bool
• constraint: object or list-of-object
  • member: docpath
  • required: bool
  • type: string [optional]

Adding SPATIAL support

new top-level field "type"

Up to now only plain INDEXes could be created.

A new field 'type' shall be added which:

• allows "INDEX"
• defaults to "INDEX" if it isn't set (backward compatibility)

SPATIAL index-type

To allow SPATIAL indexes, the .type additionally allows the value:

• SPATIAL

new constraint.type

If type is SPATIAL the constraint.type must accept a single value:

• GEOJSON

The GEOJSON leads to:

• extraction function: ST_GeomFromGeoJson(doc->${.member})
• column-type: GEOMETRY

default constraint.type

If index-type is SPATIAL and no constraint.type is specified, it defaults to "GEOJSON"

new constraint fields

If constraint.type is GEOJSON then new, optional constraint fields

• options: int, default: 1
• srid: int, default: 4326

shall be accepted.

accepted schema

• schema: string
• collection: string
• name: string
• type: string [optional, default="INDEX", allowed=INDEX|SPATIAL]
• unique: bool
• constraint: object or list-of-object
  • member: docpath
  • required: bool
  • type: string [optional]
  • if type == GEOJSON:
  • options: int [optional, default: 1]
  • srid: int [optional, default: 4326]

SQL generation

ALTER TABLE, ADD COLUMN part:

<col> GEOMETRY 
    GENERATED ALWAYS AS (ST_GeomFromGeoJSON(doc->'$.geo', 1, 4326))
    STORED NOT NULL

Notes:

• the data type for the generated column MUST be GEOMETRY
• SPATIAL indexes can currently only be created on STORED generated columns, not VIRTUAL
• SPATIAL indexes require NOT NULL, so any request that does not have the field set as required shall generate an error

ALTER TABLE, ADD INDEX part:

<tbl> ADD SPATIAL INDEX <name> (<col>);

Example

• schema: 'foo'
• collection: 'bar'
• name: 'geoidx'
• unique: FALSE
• type: 'SPATIAL'
• constraint: object
  • member: '$.geo'
  • required: TRUE
  • type: GEOJSON
  • options: 1
  • srid: 4326

Generated SQL:

alter table foo.bar
  add column geo geometry generated always as
    (st_geomfromgeojson(doc->'$.geo', 1, 4326)) stored not null,
  add spatial index geoidx (geo);

Error Handling

Currently, generating a SPATIAL index without NOT NULL results in:

ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL

This error must be handled and translated into:

GEOJSON index requires 'constraint.required: TRUE'

Spatial indexes require NOT UNIQUE, so any request that does have the argument set shall generate an error:

"Unique spatial index is not supported"

Creating indexes require (an optional) type of index, so any request that does have the argument set on an unsupported value shall generate an error:

"Argument value '%s' for index type is invalid"