Support for geospatial queries and types

[RichardPollock]

Does the current version of ArcadeDB have any support for geospatial types, queries, and indices?

I couldn’t see anything in the docs, and was thinking of features along the lines of the OpenGIS class types in MySQL (https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html) and R-tree-based SPATIAL INDEX functionality. Or the GeoJSON support, $geoIntersects and $nearSphere query operators in MongoDB.

If the support isn’t there, are there any proposed workarounds or anything on the roadmap? If not, we could probably contribute a bit of budget to bump it up the priority list.

[lvca]

@RichardPollock the support for geospatial is definitely in our roadmap, but today we don't have enough contributors can work on it. In OrientDB we used Lucene spatial index, but in ArcadeDB we'd like to have something native, namely creating the R-Tree on top of our LSM index. This allows more control. We had many issues with OrientDB handling 2 engines: orientdb + lucene that live on the same JVM...

Any help on this is welcome, in terms of sponsorship and code contributors.

[lvca]

Glad ArcadeDB is the front-runner :-) About the geospatial index, what kind of features do you guys need? Indexing of points, rectangles, circles, or complex polygons? Also, what kind of search would you execute against the geospatial index?

[RichardPollock]

I think the storage/indexing needs will be relatively straightforward: we'll only be storing points (latitude and longitude) in the graph and, while the queries are still to be finalised, they'll likely lean on functionality similar to MongoDB's $nearSphere and $geoWithin functionality so we can get a list of the points a) nearest another point and then b) within a GeoJSON polygon.

[lvca]

@RichardPollock thanks, very helpful. I see the most common approach is to convert lat/lang into a geohash. In this way you can store geo spatial coordinates in any index, and our LSM Tree would work perfectly. I found this popular library (https://github.com/davidmoten/geo) licensed Apache2, so it should be easy to embed. Once you convert lat/long in a geohash, you can index it as a long (64bit) and it provides API to retrieve adjacent points, calculate distance, etc.

After integrated the library we should provide some GEO spatial functions as SQL functions. If I understand correctly, the 2 main use cases would be something y ou should resolve with the following 2 examples:

• SELECT FROM Shop WHERE GEO_WITHIN( location, GEO_BOX( 15.231, 34.232, 16.000, 35.000 ) ) where location is a property in the Shop type representing the coordinates (lat + long). GEO_BOX() is a boundary box function that create a rectangle
• SELECT FROM Shop WHERE GEO_NEAR( location, GEO_POSITION( 15.231, 34.232 ), 30, "km" ). Here GEO_POSITION would create a point with long & lat. 30 is the distance in km (last parameter)

WDYT?

About this, I think we can create a POC in a week and another week to finalize the code, writing the tests, documentation, etc. I'd say 1 week sponsorship package will work, but please consider around 10 days ETA for the final implementation.

[RichardPollock]

That sounds great, Luca. Thanks once again. We'll wrap up our DB selection decision-making over the next couple of weeks, but this is definitely another point in favour of ArcadeDB. If we do end up using ArcadeDB, I'll drop a quick note through to the support@arcadedb.com to confirm that we'll go ahead with the sponsorship and hopefully we can take things from there.

[lvca]

Great, sounds good for me ;-)