topic Re: Creating views in SQLite using runtime database in ArcGIS Runtime SDK for iOS Questions

Creating views in SQLite using runtime database

MichaelDavis3 — Wed, 30 May 2018 23:04:30 GMT

I'm increasingly running into situations where for whatever reason I end up querying all the features in a AGSGDBFeatureLayer and iterating through them to do X. Typically X is pulling data for summary statistics of some sort, various counts of related things, tallying up distances, etc...

I'm wondering if it would be possible, and if anyone has attempted, to use the SQLite db powering the runtime database to create custom views. The thought being that once a project database has been downloaded to a device a little routine could go in and join things up in views to power these summary activities and save the compute required to run these summary routines... which run a serious risk of causing trouble when we get into large quantities of data.

We do this all the time on the web side of things and I'm a bit jealous of our web developers.

Re: Creating views in SQLite using runtime database

MarkDostal — Tue, 10 Jul 2018 16:35:17 GMT

Thank you for your question. It might be possible to do what you suggest, but take a look at the `queryStatisticsWithParameters` method on `AGSFeatureTable`:

ArcGIS Runtime SDK for iOS: AGSFeatureTable Class Reference

Depending on what you're doing, that may fit your need.

We have a sample for it here:

arcgis-runtime-samples-ios/StatisticalQueryViewController.swift at v.next · Esri/arcgis-runtime-samples-ios · GitHub

Mark

Re: Creating views in SQLite using runtime database

MichaelDavis3 — Tue, 10 Jul 2018 16:47:50 GMT

Thanks Mark! I think that will be just the ticket for a few of our use cases.

In some cases it would be really helpful if we could do queries like this against joined tables. Is this supported in v100 or are there plans to support joined tables in the future?

Re: Creating views in SQLite using runtime database

ChristopherMilack1 — Tue, 10 Jul 2018 17:26:48 GMT

Michael,

I've used the SQLite DB directly without issue in several apps that I've worked on but there are limitations. There is no way to run spatial joins directly in SQLite or deserialize geometry BLOBS after you read them. If you're trying to do non-spatial joins/aggregation it should work just fine and you can create views just like you would in any other SQLite DB. Like Mark said, summary stats are also an option.

FYI, With Objective-C, I used FMDB and with Swift I started using SQLite.swift.

Re: Creating views in SQLite using runtime database

MarkDostal — Tue, 10 Jul 2018 19:18:34 GMT

Michael,

Any ServiceFeatureTable (a related table or not) will support queryStatistics (if advanced query operation is enabled on the Service).

If by "join" you mean the JOIN Feature in Analysis available in AGOL, then yes, it is supported as well. Join here creates a new hosted Service with the joined fields and some statistic fields. So in Runtime, we just treat it as another ServiceFeatureTable and you can perform query operations on that.

Hope that helps,

Mark