Using SQL in the Filter for a Web Map

1922
5
04-07-2021 06:44 AM
RobertBorchert
Frequent Contributor III

In Pro we can use a SQL definition query to filter related tables that meet criteria.

We can (and do) publish those as a Feature Service and it holds true when it gets to a Web Map.

In Web Maps it seems we can only Filter using basic filtering.

Is there a way to use SQL in the Filter so it can be written in only in the Web Map and not the service?

Tags (1)
5 Replies
Jelle_Stu_PR
Occasional Contributor II

Not that I am aware of. A workaround could be to add a field in Pro that defines if features holds your sql query or not. And then in your we map you can filter based on that field. This does require a regular extra analysis of your feature class if it regularly gets updated.

0 Kudos
RobertBorchert
Frequent Contributor III

Thats is a pretty good idea and I can see it working for other applications we use. However, the table (short name is EVW) is generated from an export from our Primavera application and it contains the status of every project.  And is updated every Monday the analysis could be triggered as part of the SQL that exports the table. The table would be related to a different table that has Field Project status updated and that can change by the minute and would not reflect in the other table.  However, you have given me ideas for other uses for your suggestion. 

0 Kudos
jcarlson
MVP Esteemed Contributor

Using the ArcGIS Online Assistant or Python, you can access the SQL statement reflected in the filter settings, and adjust it however you like. The string is stored in the Web Map's JSON as the "definitionExpression" property.

jcarlson_0-1617806267511.png

 

{
    "operationalLayers": [
        {
            "id": "Sales_Hosted_2910",
            "layerType": "ArcGISFeatureLayer",
            "url": "https://services6.arcgis.com/U1zX3Wx8hhY7Gb30/arcgis/rest/services/Sales_Hosted/FeatureServer/0",
            "visibility": true,
            "opacity": 1,
            "title": "Sales_Hosted",
            "itemId": "a405b06ae8e24f94a2768a4581b79e73",
            "layerDefinition": {
                "definitionExpression": "valid_sale = 1",
            ...

 

- Josh Carlson
Kendall County GIS
RobertBorchert
Frequent Contributor III

Thanks.  I didn't even think of that. We have used the assistant quite often when moving thins from AGO to Portal.  I will give that a shot.

0 Kudos
ArmstKP
Occasional Contributor III

@jcarlson Is there any way in the ArcGIS Online Assistant to obtain the logged-in user and then filter a layer's field for that value, for example in an "assigned_to" field?

0 Kudos