6

I'm building an iOS application for public release. My architecture currently includes Mapbox, Parse, geoJSON and SQLite for the local store. The app will store spatial objects locally as a string in a SQLite database, and when a user opens the map view the app will query for spatial objects that exist within the map extent. Parsing/querying the raw geoJSON data would be very costly so I want to index the tables that contain any spatial data.

What's the best approach for this? Is it efficient enough to store the spatial object's bounding box (or envelope) in a separate column and index that column? Or should I go a step further and split out the envelop into it's component xmin, xmax, ymin, and ymax values, store those in their own columns and index each of those columns?

Or perhaps there's another approach I haven't considered yet? Maybe I shouldn't be storing the geoJSON at all and only the geometries? I haven't fully committed to any design decisions yet, so I was hoping to hear thoughts from other's with more experience before I start building out the db.

Improve this question

1 Answer 1

Reset to default
1

SQLite has R*Tree support, which is probably the best way to approach this problem. See https://www.sqlite.org/rtree.html for an explanation of the concept and implementation. That also explains that your SQLite may have the R*Tree disabled, so that would obviously be worth a check first.

If that is a bit too much complexity, consider just using Spatialite as an extension in SQLite. That will bring in some additional complexity for iOS, but also a lot more power. It depends on exactly what you need. If you decide to use Spatialite, you may want to import your GeoJSON into the Spatialite (or possibly GeoPackage) geometry formats. (Disclosure: I'm a occasional Spatialite developer).

Another extension option that does GeoPackage support is libgpkg from Luciad. It doesn't have all of the functions that Spatialite has, but its lighter weight and may suit your needs better.

Improve this answer
4
  • After reviewing the RTree documentation and finding some tutorials on how to compile a custom amalgamation of SQLite for iOS (not clear on all those details yet), it looks like RTree offers exactly what I need. However, the Spatialite extension seems to offer a lot more than just efficient virtual tables for indexing envelopes. I'm still trying to figure out how to turn the extension on in iOS, but if I can do that I think Spatialite is the route I'm going to take. Thanks for the suggestions!
    – William Smith
    Commented Aug 22, 2015 at 2:25
  • The RTree in Spatialite an GeoPackage is actually just a table that holds xmin, ymin, xmax, ymax values. You can as well create similar table by yourself, index the fields and make queries like (select id from rtree_tablename_geom where minx<BBOX_maxx and maxx>BBOX_minx and miny<BBOX_maxy and maxy>BBOX_miny);
    – user30184
    Commented Aug 22, 2015 at 16:44
  • The point of R*Tree in SQLite (and hence gpkg / splite) isn't the table with the values, its the efficient indexing.
    – BradHards
    Commented Aug 23, 2015 at 0:28
  • As it turns out, I had a lot of difficulty getting Spatialite to work. I was able to get the pod installed and everything seemed fine at first, but later I realized there was a problem with the compiled architectures; no 64 bit. I found a Homebrew example where all of the architectures were built, but the resulting file size concerned me. Ultimately, I dropped back and went with vanilla R*Tree because the fast indexing is my primary requirement. Some of those spatial functions written into Spatialite would have made my life a lot easier, though.
    – William Smith
    Commented Aug 24, 2015 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.