Support mapping (and querying) of geometry collections

[roji]

GIS has a concept of a collection of geometry objects; this is represented by NetTopologySuite's GeometryCollection type, which extends Geometry and which we map to the database geometry/geography types (e.g. GeometryCollection in SQL Server). In most databases we also translate some spatial functions.

As part of #29427, we're allowing to map a .NET collection of any supported type to a JSON column in the database; but since collections of spatial objects have a special representation, we're excluding that specifically. Since GeometryCollection already implements IEnumerable<Geometry>, we probably don't want to also allow mapping e.g. Geometry[]. However, most database don't allow unpacking a geometry collection to a queryable table (PG supports this via ST_Dump); this means mapping Geometry[] to a regular JSON string column in the database does an advantage (queryability) over mapping to a database geometry collection.

Note: SQL Server OPENJSON doesn't natively support geometry, i.e. does not allow using geometry in its WITH clause. But we can still get WKT out as a string and just wrap it in a function which returns the geometry object.

[roji]

Some thoughts on why someone would want to use List<Point> over GeometryCollection:

• Strongly-type to Point only, as opposed to GeometryCollection which can hold any geometry
• As written above, on databases other than PG, it doesn't seem possible to unpack a database geometry collection to a resultset which can be queried with normal SQL. So if regular querying is desired, mapping a collection via JSON is needed.

[roji]

See npgsql/efcore.pg#2722 for an example of a user attempting to map List<Point> instead of GeometryCollection and how that complicated things. In a nutshell, GeometryCollection can also function as a non-collection geometry, so that general special functions can be used with it (e.g. you can intersect directly with it, rather than do a complex thing where you intersect with all of its points).

Thinking about this some more, there may be a good compromise here... We could allow users to map List<Geometry> as a string (via JSON), but require that to be done explicitly, i.e. by specifying the store type as nvarchar(max). This is in contrast with other collection types, where we'd default to JSON mapping even if there's no store type specified. That would help guide users towards GeometryCollection rather than JSON, but would still allow JSON where that's desired.

[roji]

Design decision: don't special case geometry in any way:

• MapPoint[] to primitive collections of points (e.g. JSON array with WKT or whatever). These don't support any spatial functions but are queryable like other primitive collections.
• Map GeometryCollection to geometry/geography. These aren't queryable (except on PostgreSQL) but support all the spatial functions.

[roji]

Requires #30677 to control JSON serialization of spatial types as WKT.

[roji]

@ajcvickers this should be doable not that JsonValueReaderWriter is available on the type mappings, as just another case in #30727. However, we also need CollectionToJsonStringConverter to use it (currently it tries to just do JsonSerializer.Serialize() on the NetTopologySuite object, which fails. I'll revisit this once we wire everything together, should be easy.

Note to self: remember that SQL Server OPENWITH with WITH doesn't support geometry; we'll have to rewrite it to OPENWITH without JSON in postprocessing, just like we do when ordering needs to be preserved. The casting to a SQL Server relational geometry value will be introduced there as well.

[roji]

Note mini-investigation of geometry and IN/equality: #31912 (comment)

[vigouredelaruse]

i wanted to help spur this tangent on by formally mentioning the dreaded geojson featurecollection and the challenging openapi schema (polyglot) of site and vendor specific implementions of ogc spec responses. there is also a nod to the use of jsonb in timescaledb

while bearing in mind this

consider https://api.weather.gov/zones/forecast/MIZ034. note that the response contains domain and organization specific properties

       "forecastOffices": [
            "https://api.weather.gov/offices/APX"
        ],
        "timeZone": [
            "America/Detroit"
        ],
        "observationStations": [
//etc

consider https://api.weather.gc.ca/collections/ahccd-stations/items. note that the response contains domain and organization specific properties

           "properties": {
                "identifier__identifiant": "2400305",
                "station_id__id_station": "2400305",
                "station_name__nom_station": "ALERT",
                "measurement_type__type_mesure": "temp_mean",
                "period__periode": "Ann",
                "trend_value__valeur_tendance": 1.5,
                "elevation__elevation": 65,
                "province__province": "NU",
                "joined__rejoint": 1,
                "year_range__annees": "1951-2020",
                "start_date__date_debut": "1950-07-01",
                "end_date__date_fin": "2020-12-01"
            },

the examples will vary for every endpoint by scientific domain and data collection epoch.

one should here formally state that this schema variability is a feature of the wireformat data models of the underlying scientific domains. indeed one (where one = hil +- human) queries the metadata, discovers dimensions and attributes of interest and composes further queries to actually retrieve the data.

for instance, the response to this request contains https:$reflike pointers to gridded met forecast datapoints
https://api.weather.gov/points/39.7456,-97.0892

following the link to the gridded data https://api.weather.gov/gridpoints/TOP/32,81 yields

implementations seeking to exploit this dataset might want to apply a tool such as timescaledb to the advertised timeseries atoms https://docs.timescale.com/use-timescale/latest/time-buckets/about-time-buckets/

once timescaledb is in the mix one quickly checks its support for jsonb and though for performance reasons it's not advised, timeseries functions are available against jsonb

one may here assert that

• modelling these responses and their location payloads is onerous if one can only query the geomertries with clr geometry properties
• ef core can enable a plethora of scientific jsonset persistence scenarios with inescapable schema variability by supporting geometries on .ToJson()
• note that postgres provider implementations exposing both asp.net core rest and odata endpoints are already tasked with modelling geometries twice, once for nts types and once for microsoft.spatial types, such that geo.distance works in odata, a magnificent thing by the way
• to be clear the ask on the querying side is for the ability to query $json.paths.geometry with a reasonable subset of the intersection of the spatial query functions of the various underlying providers.

one also here provides evidence that this (consume, mutate and persist gis datasets) has been a pain point for gis developers for a long time

mov ax, 'thanks'