WL#10734: Spatial index type

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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.
:   for the client it MUST be possible to create spatial indexes on GeoJSON data

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

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

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

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:


## new constraint.type

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


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


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


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


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


* 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"