Definition Query on Hosted Feature Service

DarlaPyron2 · ‎10-11-2018

I'm working on an AGO map for our police department to use. They are inputting dates for vacation patrols and they are generally 1 to 7 days long. I have a def query on a hosted feature service to only display patrols for today, but I want them to stay on the map until the vacation is over. So this is the query on the HFS, and when you do the Query (POST) it will only select the relevant records. The problem neither I or anyone else here knows how to get that query to save to the feature service because on the AGO map it is still showing all the dots. My workaround was to just run it as a feature service off the server with a similar def query, but I'd rather not be in sde. Thank you.

MikeMinami · ‎11-02-2018

I believe the issue is that your organization is not configured to use standard SQL queries.

Go to the Organization tab, click the Security sub-tab and look for the checkbox that says:

Allow only standard SQL queries

I think this is probably unchecked. Esri recommends enabling it as described here:

Configure security settings—ArcGIS Online Help | ArcGIS

Here's the specific section of the documentaiton

You may also see the following options if your organization was created prior to mid September 2018, and configured with these settings disabled. It is recommended that you enable these two security settings.
Allow access to the organization through HTTPS only—Check this box to ensure that your organization's data as well as any temporary identification tokens that allow access to your data are encrypted during communications over the Internet. Once enabled, check your organization to confirm that nothing is affected, such as your home page, maps, scenes, and apps. For example, you may need to update layers to use HTTPS in your maps and scenes.
Allow only standard SQL queries—Check this box to prevent nonstandard queries from being accepted in your organization's hosted feature layers. This helps prevent SQL injection attacks. All ArcGIS applications automatically support standard SQL queries. If your organization is currently using any third-party applications for querying data, confirm that they are using standard SQL syntax.
Sixty days after enabling these settings, the disable option will no longer be available on the Organization page.

Thanks,

Mike

View solution in original post

MikeMinami · ‎10-12-2018

I think you might be able to do this another way, by setting up an arcade expression to draw the layer. The features outside of your date range wouldn't be drawn.

When you set the symbology for a layer, in the dropdown that let's you pick a field to symbolize with, you can define an arcade expression. I think using the Now() function of arcade and doing some date calculations with your date field, much like you're doing in SQL, you'd be able to use the Types style to draw features. Features that don't fall into your date range would fall into the 'Other' category of the Types style and wouldn't be drawn. Other Arcade functions that might be useful are the Date, DateAdd and DateDiff functions. Seems like your date values are in days, so you don't need to refresh during a given day. GIven a smaller time interval, you could probably set a refresh interval on the layer to cause it to update, with say dates that are changing by the hour, etc.

Here's an expression I wrote that will draw a feature if its date is within the last 24 hours.

datediff (now(), $feature.Datef,'hours') < 24

This expression returns True or False, so the drawing will show True or False as the "attribute value". You can return a different value, but this is just a simple example. I them moved the False values to the 'Other' category and turned off drawing of 'Other'. This is simple a drawing filter. The feature is still there, but just not drawn. For example, I was still able to display a popup for the feature if I clicked in the location on the map, even thought the feature wasn't visible.

Hope this helps,

Mike

DarlaPyron2 · ‎10-12-2018

Thank you, Mike. I will see if that solves my problem. The police dispatchers will be the ones entering the data, and the officers in the patrol cars will be able to use the web map app so I want to make this simple for them. I did not know about Arcade and doing some research it sounds like that is something I need to be using. Thanks again!

erica_poisson · ‎10-26-2018

Hi Mike,

I have a question about applying a definition query to a hosted feature layer being consumed within ArcGIS Pro. My HFS has a date field; I want to display features that were inspected after a certain date. When I open layer properties in Pro, go to the definition query tab and build my expression using the query builder, it initially shows a green check for a valid expression, before changing to invalid.

The goal is to easily visualize which feature have had an inspection since a certain date. They are overlaid on another copy of the HFS. This works perfectly in our AGOL web map, but we can't get it to work for Pro.

Thanks for any help,

Erica

MikeMinami · ‎10-26-2018

Hi Erica,

I am not seeing the same behavior. I am able to set a definition query in pro using a date field in my hosted feature layer in ArcGIS Online. I assume you are signed in to pro and are accessing your own content. How is the item shared? Does it draw correctly? Are you able to set a definition using other fields?

Mike

erica_poisson · ‎10-30-2018

Hi Mike,

I just tested the same layer with another definition query and it worked fine (this was on a string field w/ domains).

Also, to answer some of your questions - yes, I am logged into AGOL (administrator account - not sure if that matters), the feature layer is shared with one group, but is not owned by my account. Also of note, when the owner of the hosted feature layer tries to make the same definition query (by date) on the layer, while logged into his account, it also does not work for him.

Erica

MikeMinami · ‎10-30-2018

Is the problem specific to this layer? For example, are you able to set a definition query with a date field on a different hosted feature layer?

Are you able to use the Filter tool in ArcGIS Online to set up the same query?

Mike

erica_poisson · ‎10-31-2018

Hi Mike,

We are able to set up a filter, using the Date field, on the layer in ArcGIS Online. It was actually because of this that the user wanted to be able to have the same definition query set up in our ArcGIS Pro project.

I've also gone ahead and tested using a definition query on a date field with two other hosted feature layers that are in the same ArcGIS Pro project (and part of the same data collection effort in Collector) - the definition query failed on these two layers as well.

I have a few follow-up questions:

1) Could this be because these data are collected with Collector? I don't see why this would matter, but I know that dates can be quirky (at least with Survey123).

2) Could the fact that this is a shared ArcGIS Pro project be an issue?

Thanks for your continued help, I really appreciated it!

Erica

MikeMinami · ‎11-01-2018

Erica,

I don't think Collector would be the issue. What version of Pro are you running? I'm using the latest 2.2.3.

Are you querying on a date/time that is recent to the current time? This is a long shot that the time zone is somehow impacting the query. If the values are greater than a day ago, you're probably fine.

Are you able to track the request sent from Pro to ArcGIS Online? You can see if the query is working.

Are you able to share your layer so we can try it?

Mike

erica_poisson · ‎11-02-2018

Hi Mike,

I'm using Pro version 2.2.0.

I'm not sure how to track the request sent from Pro to ArcGIS Online...

This layer is not shared outside of my organization, however I can create a temporary group within my organization that I can invite you to. Would this be OK? You can contact me at erica.tefft@mass.gov and I will set it up.

Thank you,

Erica