Living Atlas Demographic Layers & Identify Results & too many columns error

675
9
11-12-2019 01:11 PM
ToddAtkins
Occasional Contributor

I've added the Esri Living Atlas Tapestry Layer (https://www.arcgis.com/home/item.html?id=c2a2e156485b4feab11b86976fe9c011) using AGSArcGISMapImageLayer to an app using 100.6. The layer shows up as expected, however when I try and identify it using  identifyLayersAtScreenPoint:tolerance:returnPopupsOnly:maximumResultsPerLayer:completion:() I get the following error:

ArcGIS Runtime Error Occurred. Set a breakpoint on C++ exceptions to see the original callstack and context for this error:  Error Domain=com.esri.arcgis.runtime.error Code=1001 "SQL error or missing database" UserInfo={NSLocalizedFailureReason=too many columns on State_0, NSLocalizedDescription=SQL error or missing database, Additional Message=too many columns on State_0}

So the error is telling me there's too many fields for it to do an identify but I cannot figure out how to limit the fields returned (I only need about 10 of them for our purposes). I've asked this question in the past (and Esri said they were going to look into it) but never did figure out a solution other than creating a copy of the data with just the fields we want and hosting it ourself. Is there a workaround for this or are we still stuck hosting the data? I'd like to stop having to do this since it eats up credits duplicating data already out there, plus it's disconnected from the authoritative layer that Esri is publishing.

I'm Identifying the layer using this method:

self.agsMapView?.identifyLayers(atScreenPoint: screenPoint, tolerance: 12.0, returnPopupsOnly: false, maximumResultsPerLayer: 4) { (results: [AGSIdentifyLayerResult]?, error: Error?) in {
 ...
}‍‍‍

Thankful for any help...

0 Kudos
9 Replies
Nicholas-Furness
Esri Regular Contributor

Hi Todd Atkins.

Yeah. I hear you. With Runtime 100 our goal was to simplify usage and ‌in this case while we have reduced majority use cases to a simple API, we're not supporting cases like this very well. Still working on a solution, but in the meantime here's a workaround.

You can create a separate AGSServiceFeatureTable that points at the same service endpoint. Set its featureRequestMode to .manual. When you do an identify, rather than calling AGSMapView.identify(), translate that into an AGSServiceFeatureTable.populateFromServiceWithParameters:clearCache:outFields:() call. Set the clearCache parameter to true. Set the outFields parameter to an array of field names. The completion block will be given an AGSFeatureQueryResult much as if you had called AGSFeatureTable.query(), but the features will only have the fields you want. The table will contain the results of your query too should you need to go back to them. You could even set up the AGSQueryParameters for the populate call to not return geometries. Really up to you.

Depending on your use case, you might want to call AGSServiceFeatureTable.clearCacheWithKeepLocalEdits:() passing in false as soon as you're done with the results. Or you can hold on to them. It's up to you, your app, and your memory constraints.

Hope that helps. Let me know if you have any questions.

0 Kudos
ToddAtkins
Occasional Contributor

Hey that's very creative and helpful, thanks! I'll give it a try and report back.

0 Kudos
ToddAtkins
Occasional Contributor

So I gave this a try but I'm getting an error: "Cannot call this method in this context". Here's an example of what I'm doing. Watching the requests in Charles I see it requesting the layer's info (http://demographics9.arcgis.com/arcgis/rest/services/USA_Demographics_and_Boundaries_2019/MapServer/... ) which includes a listing of all 2,075 fields in that layer. Is this issue possibly why the request is failing or am I making some other sort of rookie mistake here?

    func doQuery(mapPoint: AGSPoint, mapScale: Double) {
        var layerId: Int?
        
        self.layer.subLayerContents.forEach { subLayer in
            guard let sl = subLayer as? AGSArcGISMapImageSublayer else {
                return
            }
            
            if sl.isVisible(atScale: mapScale) && sl.sublayerID > 2 {
                layerId = sl.sublayerID
            }
        }
        
        guard layerId != nil else {
            return
        }
        
        
        let queryParams = AGSQueryParameters()
        queryParams.geometry = mapPoint
        queryParams.spatialRelationship = .intersects
        queryParams.returnGeometry = false
//        queryParams.whereClause = "OBJECTID = 1000"
        
        guard let url = URL(string: "\(DEMOGRAPHICSURL)/\(String(describing: layerId!))") else {
            return
        }
        print("\(DEMOGRAPHICSURL)/\(String(describing: layerId!))")
        let table = AGSServiceFeatureTable(url: url)
        table.featureRequestMode = .manualCache
        table.credential = self.loginState.agolCreds

        
        table.populateFromService(with: queryParams, clearCache: true, outFields: ["OBJECTID"]) {(results, error) in
            let strongTable = table
    
            guard error == nil else {
                print("Error doing demographic identify. \(error?.localizedDescription ?? "N/A")")
                return
            }
            
            guard let features = results?.featureEnumerator().allObjects else {
                return
            }
            
            features.forEach { feature in
                print(feature.attributes)
            }
        }
        
    }‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
Nicholas-Furness
Esri Regular Contributor

I'm really sorry, Todd. I was wrong. Even if we use the work-around to only populate the fields we want, internally Runtime loads all the fields up front and this is tripping up SQLite (which defaults to a limit of 2000).

I can't think of a way to do this. I had hoped that using ArcGIS Online views would work, but they require that you ‌have a source service that has the word FeatureServer in the name.

The layer is failing to load because there are too many features fields, and then you're getting the context error.

I'll keep digging, but it may not be possible for Runtime to do this right now. I do think the error could be better (we have done some work on improving this message elsewhere but it looks like this workflow wasn't considered).

0 Kudos
ToddAtkins
Occasional Contributor

No problem, thanks for the input. I think what I'll try next is just craft a query and hit the rest endpoint directly with URLSession.

0 Kudos
Nicholas-Furness
Esri Regular Contributor

Sounds good. Note that you could use AGSRequestOperation or AGSJSONRequestOperation which can make use of cached credentials (or you can assign a credential to it). The former returns data, the latter a JSON Dictionary.

Here's an example, albeit not relying on credentials (in fact, I'm using it to get credentials). But in short, you create the operation, register a listener to handle the result, and then add it to the shared AGSOperationQueue. It uses NSURLSession behind the scenes and is a handy shortcut for many scenarios.

0 Kudos
Nicholas-Furness
Esri Regular Contributor

This should get you to where you want to get. It's a bit limited. It returns AGSGraphics rather than an AGSFeatureSet of some sort, but I think it'll do what you need:

func doQuery(mapPoint: AGSPoint, fields: [String] = ["*"], returnGeometry: Bool = true, completion: (([AGSGraphic]?) -> Void)? = nil) {
    guard let serviceURL = layer.url?.appendingPathComponent("\(3)") else { return }
    
    var mapPointData: Data?
    do {
        let mapPointJSON = try mapPoint.toJSON()
        mapPointData = try JSONSerialization.data(withJSONObject: mapPointJSON)
    } catch {
        print("Could not convert AGSPoint to JSON: \(error.localizedDescription)")
        completion?(nil)
        return
    }
    
    guard mapPointData != nil,
        let mapPointJSONString = String(data: mapPointData!, encoding: .utf8) else {
            completion?(nil)
            return
    }
    
    // Assuming that Web Mercator is a good fallback.
    let inSR = mapPoint.spatialReference ?? AGSSpatialReference.webMercator()
    let outSR = mapView.spatialReference ?? AGSSpatialReference.webMercator()

    // Stolen from what a Query sends
    let parameters: [String : Any] = [
        "f": "json",
        "geometry": mapPointJSONString,
        "geometryType": "esriGeometryPoint",
        "inSR":  inSR.wkid,
        "maxAllowableOffset": 0.000000,
        "outFields": fields.joined(separator: ","),
        "outSR":outSR.wkid,
        "returnDistinctValues": false,
        "returnGeometry": returnGeometry,
        "returnM": true,
        "returnZ": true,
        "spatialRel": "esriSpatialRelIntersects"
    ]
    
    let queryUrl = serviceURL.appendingPathComponent("query")
    let operation = AGSJSONRequestOperation(remoteResource: nil, url: queryUrl, queryParameters: parameters)

    operation.registerListener(self) { (result, error) in
        if let error = error {
            print("Error performing query! \(error.localizedDescription)")
            completion?(nil)
            return
        }
        
        guard let result = result as? [String: Any],
            let fields = (result["fields"] as? [Any])?.compactMap({ try? AGSField.fromJSON($0) as? AGSField }),
            let graphics = (result["features"] as? [[String: Any]])?.compactMap({ data -> AGSGraphic? in
                let attributes = data["attributes"] as? [String: Any]
                let geometry: AGSGeometry? = {
                    if let geomDict = data["geometry"] as? [String: Any] {
                        let geom = try? AGSGeometry.fromJSON(geomDict) as? AGSGeometry
                        if let geom = geom, geom.spatialReference == nil {
                            // Bit of a hack to force an SR onto the geometry
                            return AGSGeometryEngine.projectGeometry(geom, to: outSR)
                        }
                        return geom
                    }
                    return nil
                }()
                if attributes == nil && geometry == nil { return nil }
                return AGSGraphic(geometry: geometry, symbol: nil, attributes: attributes)
            }) else
        {
            completion?(nil)
            return
        }
        
        completion?(graphics)
    }

    // Uncomment this to see what's being sent…
    // if let rc = AGSRequestConfiguration.global().copy() as? AGSRequestConfiguration {
    //     rc.debugLogRequests = true
    //     rc.debugLogResponses = true
    //     operation.requestConfiguration = rc
    // }

    AGSOperationQueue.shared().addOperation(operation)
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note line 41 where I don't have to provide a credential because I'm using AGSJSONRequestOperation and if we've already authenticated for that service URL with Runtime, it'll use that credential automatically. And I've been a bit lazy in terms of returning errors, and the layer is hardcoded, but you can call this with something like this:

doQuery(mapPoint: mapPoint, fields: ["OBJECTID", "NAME"]) { graphics in
    guard let graphics = graphics else {
        print("Something went wrong during the query")
        return
    }
    
    print("Got \(graphics.count) graphics back!")
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hope this helps!

ToddAtkins
Occasional Contributor

Wow thanks for the code! It works very nicely for what I'm trying to do. I'll buy you a beer in Palm Springs, haha. I would also posit that this may be worth putting in the examples section for those trying to use the Living Atlas demographic layer.

Nicholas-Furness
Esri Regular Contributor

Yep. I'll look into that.

Also, note that there was some overkill. We don't need to provide a credential at all since AGSJSONRequestOperation (and AGSRequestOperation) will automatically use the credential that was previously used to display the AGSArcGISMapImageServiceLayer in the first place. D'oh! I've updated my last response to reflect this.

Here's a more refined Gist which does propagate errors a little better: Custom Query · GitHub 

0 Kudos