Spatial #6
Comments
|
Entity splitting (dotnet/efcore#620) would make querying easier since the "index" is really just a table containing a key and the lat-long values. from b in db.Buildings
where b.Lattitude >= minLat && b.Lattitude <= maxLat
&& b.Longitude >= minLong && b.Longitude <= maxLong
select b; |
|
On the other hand, maybe introducing a from b in db.Buildings
where b.Location.Inside(minLat, maxLat, minLong, maxLong)
select b; |
|
Oh wait, I missed something: R*Trees aren't for indexing points. They're for indexing areas. Instead of a LatLong, it would be more like a Rectangle. |
|
One interesting point made by the documentation: You can use the R*Tree index to perform coarse-grained filtering before performing more expensive, fine-grained filtering. For example, get all the things that fit within a square then figure out if those things fit within a circle:
|
|
What are your ideas about making geospatial queries work in SQLite as it isn't supported at all? Adding geospatial capabilities to this library? |
|
(Brainstorming here) Nearly all of the geospatial calculations would happen in EF on the client side, but we'd try and leverage the R*Tree index to do preliminary filtering. In the illustration above, you could imagine the query would be something like "find all the places withing a 10 mile radius. We'd query SQLite for all the places inside a 10x10 mile square, then perform the actual radius calculation on just those results (instead of doing it for every row in the database). |
|
Also, if you were storing complex shapes inside the database, you could store the bounding box for those shapes inside the R*Tree index to help with the "first pass" (server-side) filtering. |
|
I get it now! The R*Tree on SQLite would probably suffice for most of my use cases so far 😃. I'd be happy to help as this is one of the things I'd love to see implemented! |
|
My initial goal with this issue was just to make creating R*Tree indexes easier in EF Core Migrations. There's nothing really blocking spatial on SQLite today. Apps are free to use a library like Microsoft.Spatial or NetTopologySuite to perform client-side calculations and persist the values as well-known text/binary in the database. They could even manually create an R*Tree index and leverage it in their queries. Perhaps a better alternative to all of this is actually to leverage SpatiaLite (or similar) on the server and translate Microsoft.Spatial or NetTopologySuite calls on the client to the appropriate SQL. But I'd like to see how dotnet/efcore#1100 plays out before diving into an implementation like that. |
|
It looks I'll be working on spatial support for EF Core soon. We'll probably leverage NetTopologySuite. Given that, I'll probably forego any R*Tree support and just translate to server-side SpatiaLite calls. I'll need to figure out how best to package and distribute SpatiaLite on NuGet for all the .NET Core and Xamarin platforms... (ericsink/SQLitePCL.raw#215) |
|
Looks like SpatiaLite is available on Debian/Ubuntu as the |
|
And it's available as |
|
Note to self: After creating a database, need to call |
|
I've made good progress on this in bricelam/EFCore:nts. We hope to have something ready for 2.2.0-preview2. |
Dependencies
Translations
Original description:
Could possibly add some Migrations operations for creating R*Tree indexes
Some of this overlaps with dotnet/efcore#1100
The text was updated successfully, but these errors were encountered: