GIS support

[arlyon]

Describe the solution you'd like

Really been enjoying using this so far, but I'm now in the need of GIS data. I'd be interested in developing PostGIS and Spatialite support, whether that be included in the base ORM or as an extension (as geoalchemy2 is to sqlalchemy). There is an extension to pypika called pypika-gis that would presumably do some of the heavy lifting, so I was wondering if I could get some pointers on how to get started?

Describe alternatives you've considered

I considered using sqlalchemy, sqlalchemy-aio, geoalchemy2, but it feels like a bit of a "hack" to run it in a separate thread.

[abondar]

Hi, thanks you for using Tortoise, I am glad you like it.

Regarding GIS support there is set of problems that have to be conquered to make it real. First of all, I think that really should be separate project because it just brings much more to project and not all users would need it, and for some of them it would be confusing. Bad news is Tortoise currently not really extension ready, but I think we could make it so based on this case.

On connection level we would need to include Shapley as dependency and set custom decoder/encoder for asyncpg connection. https://magicstack.github.io/asyncpg/current/usage.html#example-automatic-conversion-of-postgis-types Also we would need some flow for adding startup tasks to connections, so they would initialise custom decoders on connection startup or load some extensions.

On executor level - pypika-gis seems a little bit lacking to me, but I think we could at least try it. I think idiomatic way to implement all custom query functions would be to make new class for each function and do make them inherit from Q, so it will fit just fine in current code base and could be used in more complex OR statements. Those classes would override resolve_for_model to return matching criterion.

Hard thing would be to implement returning items in custom formats that are generated by some functions, not just plain geometry object. I think we could accomplish it only in .values and values_list because usual queries doesn't have any interface to change returned objects formats and I think it should stay this way.

So summing up, what have to be done: In extension:

• Custom function objects derived from Function object that inherits from Q object
• Method that receives connection object and sets custom encoder/decoder for it
• GeometryField itself

In Tortoise:

• Allow values and values_list queries to accept Function objects so user can receive output calculated in database
• Create extension mechanism for Tortoise.init() so it could run some additional startups for connection if it's required

With this I think it should implement support for postgis. Spatialite support seems less priority, so I think we shouldn't try implementing both of them simultaneously. Also, I am not really experienced with PostGIS so may be missing something crucial to implementation, so if you noticed that I am missing something crucial - feel free to add.

If you are solid on trying implementing it - you can implement extension and I would enhance Tortoise itself so it would be integratable

[grigi]

It would get a lot more testing if it is part of core, but it is likely to cause a lot of chaos if done as part of it at first... I think @abondar is right in requesting that it be done as a separate project at first. It might be worth it to do a throw-away fork, and then pull the relevant pieces in(edit)

I do find SQLite a great way to testing the gross concepts, as it is so easy to manipulate. So I would be interested in helping with it.

Re pypika-gis and maturity, it seems a very new project. We should contact @eduardogspereira and ask him what his plans are?

So I'll start with a list of questions?

• What Primitives do we need? Just Point and Polygon?
• What Operations do we need? Contains, Intersect, Distance, Merge, etc... ?
• For each Primitive and Operation combination what data do we get back?
• For simple filtering operations, we can have result sets in the same way we do now?
• For aggregation operations, we need to return something specific?
  • On that topic, Tortoise and aggregations need a lot of work...
• What would be a concise syntax to do each of these?
• Right... do we really need each of these?

Assumptions for change in Tortoise:

• We would need some sort of capabilities for db drivers. Right now we assume that each db has the same capabilities, and it is something I considered adding. This will definitely break that assumption.
• db_url needs to be extendable. (not critical as it is a convenience feature)

[arlyon]

Thanks for the responses good to get some conversation started. I think, given that extension support is limited, I'll fork it at and (like you mentioned) it can be drawn from later. Does that seem reasonable?

My use case is fairly simple, luckily, so it is a good target for a base to work from. This next data is checked against PostGIS, but Spatialite should be almost the same...

• Primitives: These should really just be the ones defined in openGIS. polygon and point would be enough to start with, but there is also linestring as well as multi____ for each of those three as well. Additionally, there is geometrycollection. These are all part of the geometry type. For now:
  • Polygons
  • Points
• Operations: Once again, to start off, I think Contains and Distance should be enough. Centroid, and ClosestPoint may be useful. They are all only really filters though.
• Operation signatures:
  • The first two take two geometry objects and return a boolean
  • Centroid takes a geometry type, and returns a point
  • ClosestPoint two geometry objects and returns a point
• Filtering: Follow the convention from geoalchemy filtering and, as @abondar said, implement a Function for each operation.

I haven't considered aggregations, but I'll think about what may be needed. If you are planning on improving aggregations anyway, maybe we can hold off on including that for now and implement it at the same time as tortoise-orm aggregations get some love.

One potential feature I haven't considered is the Geography type which is similar to geometry, but reasons in terms of the "round earth geometry system", and only has access to a limited subset of functions. I propose we leave it out for now too.

I think that I will, if you agree:

1. Fork, and create the GeometryField
   • Define the type of geometry on the field (POINT or POLYGON for now)
   • Get / save the appropriate shapely object for the given field
   • Spatialite serializer / deserializer
2. Add filtering for the functions above

Then, given everything goes well,

• PostGIS support
• Capability detection
• Add custom "select" clauses to the value function (which django simply passes on to annotate)
• More primitives...
• More operations...

[abondar]

Well, seems all sane to me. Regarding using .annotate() for using functions - may be should stick to it too, so our API would be more predictable for new users.

[mykolasolodukha]

Hello all! Did someone work on this issue? I want to contribute but haven't found any fork/extension to start from.

[arlyon]

Hi. I did get a prototype working for use with a project at university, however I was never able to find the time to finish it. I revisited it this summer, but it is about 2 years old at this point and some 300 commits behind.

https://github.com/arlyon/tortoise-orm/tree/gis

This was the project it was used in (a smart-bike booking system):

https://github.com/dragorhast/server

[mykolasolodukha]

Thanks a lot! I'll start working on that thing next week.

[fullonic]

hi @TrDex, have you done any progress on this issue? Thanks

[mykolasolodukha]

@fullonic unfortunately, I dropped this one and it's unlikely I'll pick it up again.

[Sovetnikov]

There is project that brings gis related fields into tortoise-orm for postgis https://github.com/revensky/tortoise-gis