Supplying altitude in Location causes ColumnValidationException or does not arrive at all

[ilarimikkonen]

Describe the bug Supplying altitude in Location causes ColumnValidationException. Example from Crash:

cr> select * from  "mttest31"."etthing";
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| __original_ngsi_entity__                                                                                                                                                                                                                                                                                                                                                                                                                                                          | entie_index |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| {"data": {"id": "test", "location": {"type": "geo:json", "value": {"coordinates": [51.716785, 8.752131, 11.0], "type": "Point"}}, "location_centroid": {"type": "geo:point", "value": "8.752131611, 51.716783624"}, "time_index": "2020-11-ption[ColumnValidationException: Validation failed for location: Cannot cast {\"coordinates\"=[51.716783624, 8.752131611, 11], \"type\"=\\'Point\\'} to type geo_shape]')", "failedBatchID": "a54a4481c4844272931574c7f13100e0"}       | test1190952 |

from QL logs:

crate.client.exceptions.ProgrammingError: SQLActionException[ColumnValidationException: Validation failed for location: Cannot cast {"coordinates"=[51.716783624, 8.752131611, 23], "type"='Point'} to type geo_shape]

To Reproduce Steps to reproduce the behavior:

1. create a sub from Orion to QL
2. Send data to Orion. Payload example:

{
    "id": "test",
    "location": {
        "type": "geo:json",
        "value": {
            "coordinates": [51.71678362411, 8.7521316110816, 23],
            "type": "Point"
        }
    }
}

Expected behavior Elevation is inserted or Documentation is updated stating that Elevation cannot be used.

Environment (please complete the following information): Data comes from Orion (version 2.4.2.) to QuantumLeap & Crate (3.3.5) via subscription.

Additional context Tested with 2 versions: 0.7.5 where data does not arrive to Crate DB and with smartsdk/quantumleap:redis-cache it arrives partially, as shown above. https://tools.ietf.org/html/rfc7946 states that elevation MAY be included.

[chicco785]

@ilarimikkonen cratedb does not support 3d coordinates, as the error in the log states:

crate.client.exceptions.ProgrammingError: SQLActionException[ColumnValidationException: Validation failed for location: Cannot cast {"coordinates"=[51.716783624, 8.752131611, 23], "type"='Point'} to type geo_shape]

[c0c0n3]

Expected behavior ... or Documentation is updated stating that Elevation cannot be used.

Yep, that's a sore point. Generally speaking, I feel we haven't done a good job about spec'ing what's an "acceptable" input, e.g. in your case, like you rightly point out, there should be a some kind of description of what types the notify endpoint can digest. To be fair tough, I think this is part of a broader problem with NGSI v2 which defines a generic data model of sorts (entity/attrs) that in practice isn't really useful for interoperability since at the end of the day JSON provides already a generic data model and communicating parties will still have to agree on the actual format of the data to exchange as well as on its meaning.

[chicco785]

Documentation actually provides this information in relation to CrateDB: https://quantumleap.readthedocs.io/en/latest/admin/crate/

CrateDB is a simple to use database backend for many applications. Nowadays a huge percentage of data is geo-tagged already. CrateDB can be used to store and query geographical information of many kinds using the geo_point and geo_shape types. With these it is possible to store geographical locations,ways, shapes, areas and other entities.These can be queried for distance, containment,intersection and so on. Currently, CrateDB supports 2D coordinates but it does not supports 3D coordinate

[chicco785]

@ilarimikkonen given that the crate limitation is actually documented, anything else you expect from us?

[ilarimikkonen]

nope, this is perfect. Thanks!

[c0c0n3]

[Crate] Documentation actually provides this information in relation to CrateDB:

The point I was trying to make is that we don't but also have no easy way of doing it :-)

Our spec basically says anything with an entity ID and type can be shovelled down the notify endpoint which isn't true as @ilarimikkonen has learnt the hard way :-) Now you could say, well you can pass in whatever you like as long as the record r resulting from QL transforming the input JSON e into tabular format can be inserted into the table T QL generated for the specified entity type. Which is what you suggest in referring @ilarimikkonen to the Crate docs? We could update the spec along these lines, but I don't think it'd help since we'd have to clarify how r and T depend on e. In turn this isn't an easy feat to accomplish because NGSI v2 says nothing about what kind of types (format + meaning) clients can exchange...