Les utilisateurs ont besoin d'appliquer un filtre de date dynamique à une couche d'entités dans leur carte web. Par exemple, ils peuvent avoir besoin d'afficher toutes les entités qui ont été modifiées le jour même. Au lieu de devoir mettre à jour manuellement le filtre dans la carte web chaque jour, les étapes ci-dessous vous guideront pour que cette date se mette à jour automatiquement chaque jour.<\/P>
<\/P>
Note : Avant de continuer, assurez-vous que l'option 'Allow only standard SQL queries' n'est pas cochée dans Mon Organisation > Modifier les paramètres > onglet Sécurité :<\/STRONG><\/P><\/P><\/P><\/P>Étapes<\/SPAN> :<\/STRONG><\/P>1. Dans votre carte web, appliquez un filtre à votre couche :<\/P><\/P>2. Enregistrez votre carte web et utilisez un utilitaire tel que GeoJobe Admin Tools<\/A> ou AGOL Assistant<\/A> pour modifier le JSON. Dans l'exemple ci-dessous, AGOL Assistant est utilisé. Après vous être connecté à AGOL Assistant avec vos identifiants AGOL, cliquez sur 'I want to'<\/STRONG> et choisissez 'View an Item's JSON' :<\/STRONG><\/P><\/STRONG><\/P><\/P>3. Naviguez jusqu'à et sélectionnez la carte web que vous avez enregistrée précédemment. À droite, faites défiler jusqu'à voir Données <\/STRONG> et trouvez la couche qui a le filtre (definitionExpression) appliqué :<\/P><\/P><\/P>4. Cliquez sur le crayon en haut à droite pour commencer l'édition. Vous devrez ensuite mettre à jour la definitionExpression en utilisant une fonction SQL (c.-à-d. GETDATE()<\/STRONG>). Dans l'exemple ci-dessous, en soustrayant 1 et en ajoutant 1 à la fonction GETDATE<\/STRONG>, on soustrait/ajoute un jour. De plus, la fonction CONVERT<\/STRONG> est utilisée pour tronquer l'heure de la date. Cela garantit que vous obtenez toute la journée plutôt qu'une période de 24 heures. Ainsi, l'expression filtrera toutes les entités entre hier et demain (c'est-à-dire aujourd'hui).<\/P><\/P><\/P><\/P>NOTE : <\/STRONG>L'expression de définition dépendra de la géodatabase que vous utilisez. Ce qui précède fonctionne pour un service d'entités hébergé ArcGIS Online. Cependant, si vous utilisez un service hébergé dans ArcGIS Datastore (qui stocke les données dans une base PostgreSQL) ou un service d'entités consommant des données depuis Oracle, l'expression de définition serait :<\/P><\/P>"last_edited_date BETWEEN (CURRENT_DATE - 1) and (CURRENT_DATE + 1)"<\/STRONG><\/P><\/P>Pour SQL Server, l'expression de définition serait :<\/P><\/P>"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 1) and (CURRENT_TIMESTAMP + 1)"<\/STRONG><\/P><\/P>5. Enregistrez vos modifications en cliquant sur l'option enregistrer en haut à droite.<\/P><\/P>6. Retournez sur ArcGIS Online et rouvrez votre carte web en allant dans 'My Content' et en sélectionnant d'ouvrir la carte web dans le visualiseur ArcGIS Online. Si vous cliquez sur l'option Filtre, l'expression devrait être vide sous les onglets Afficher et Modifier :<\/P><\/P><\/P>Le filtre sera dynamique, donc au fil des jours le filtre se mettra à jour sans intervention de l'utilisateur.
Kudos, this is very helpful.
This is excellent, thank you Jake!
This is great! I just wish it was possible to do in the Filter Widget so that the user could cancel or change it
Shay.
Thanks for this post, this is exactly what I was looking for!
I'm currently struggling to get the "definitionExpression" to be read properly, and consistently have a non-descriptive syntax error returned that prevents saving. Has anyone else experienced this issue?
Thanks!
aquaraider333 where is your data stored? For example, Oracle Enterprise Geodatabase, ArcGIS Online Hosted Feature Service, ArcGIS Data Store. Also, what is the query you are trying to write?
I don't think you can have a JSON object inside a value of an object unless it's in an array but I might be wrong (can't test it at the moment to verify). Also, the line with difinitionExpression is missing a comma at the end.
Thanks for the prompt reply Sviva and Jake!
Sviva Manager I'm not sure I understand what you mean. I am curious if there is another area of code that needs to be altered to recognize the definitionExpression parameter. I totally missed the comma, thanks for pointing that out.
jskinner-esristaff The data being accessed through a hosted ArcGIS Online feature service. The query I'm trying to write scans the GPS_Date field, and returns only those values that have occurred in the last 91 days, to avoid inflation.
This is my first time taking a dip into JSON, but from everything I've read the single line should be enough to apply the definition query. Thanks for helping me out!
Hi,
I must admit I thought this is a different post (I wrote a post on how to acomplish this in the filter of a web appbuild).
Maybe it doesn't know the variable because of the layer type but that's a wild guess really.
Good luck.
Jake Skinner -
I was unable to get this to work correctly with ArcGIS Server services/SQL Server database. I have tried various combinations of the query (in case my syntax was off, in case something specific was needed for JSON to parse the query, etc.), but no features are shown in the map. Also, when trying to access the table in the web map I get a "Error: accessing data failed". Below is the defitionExpression saved to the web map's JSON (using AGOL Assistant tool):
"COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1) OR COMPLETEDATE IS NULL"
I ran the same query directly against the SQL database (SQL Server Management Studio) and was able to return results with no problem. e.g:
SELECT *FROM dbo.ROADWAY_ROADREPORTWHERE COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1)
^ returned me correct results
Maybe the recent release ArcGIS Online perhaps broke this functionality? Is your workflow still working for Hosted Feature Services? Any way you could test against my service and/or other services in SQL Server?
My next attempt is to use the same definition query applied to the feature class/map document before I go to publish to ArcGIS Server.
However, any guidance to make this work with ArcGIS Online web maps would be great.
Ryan.Nosek_DuPage can you share the ArcGIS Server service with a Group in AGOL? You can invite me (jskinner_CountySandbox) to this Group and I can take a look.
I am having no luck with SQL server and ArcGIS server. My attribute table is returned empty. Any idea?I am trying to
all the features collected before 15 days from the last_edit_date field. I am testing with your sample "last_edited_date BETWEEN (CURRENT_TIMESTAMP - 15) and (CURRENT_TIMESTAMP + 1)". but no success. Any idea?
Alex Gole
The way your SQL Query is written above should actually be returning "all records last edited within the last 15 days", which might be quite a bit different than what you stated you wanted your query to be: "trying to return features collected before 15 days from the last_edited_date." Your stated query would actually be a little more complex because you would have to loop through all of your records to find the record with the latest (largest value/most recent) last_edited_date, then compare and only return those records created before 15 days from the record with the latest last_edited_date.
Other things to check: make sure you have editor tracking enabled on your feature class (this creates the fields last_edited_date, create_date, created_user, and last_edited_user), and try adding another date field to your dataset to test your SQL queries.
Jake Skinner
Sorry for not getting back earlier - the below query did work for me in the map document as a Definition Query on the feature class before I went to publish an ArcGIS Server Map Service. Still was unable to get it to work in AGO. I will share a service and add you to a group later today to test in AGO.
COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1)
Ryan,
"all records last edited within the last 15 days" is what I want. Sorry if I wrote something wrong.
What I get from that query is:
Alex,
I had your same issues - it is a valid SQL Query syntax, but not honored using the webmap's JSON work-around Jake showed us. However, the same syntax/query he provided does work if you use it on the feature class in the mxd prior, then publish to your ArcGIS Server. Hope that helps.
Alex Gole I was able to get the following query to work to show the features within the last 15 days:
"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 15) and (CURRENT_TIMESTAMP + 1)"
If you want to share your feature service to a Group in AGOL and invite my user account (jskinner_CountySandbox), I can take a look to see if I can get it working.
Jake if you open "Homeless" map. The layer is called "Homeless Camps"
alex.gole_ELDORADOCOUNTY I'm reproducing the same behavior you are. What version of SQL Server are you using? Also, what version of ArcGIS Server?
Can you export the Homeless Camps feature class to a File Geodatabase, zip this File Geodatabase, and upload the zip file to the AGOL Group? I would like to import this data into my SQL Server instance to see if I am able to reproduce.
Here you go. It is shared. "ziphomeless: is the name of the zip file. I am using ArcGIS Server 10.4.1 and SQL server 2014.
Alex Gole I was unable to reproduce this when I created an ArcGIS Server feature services using the following definition query:
"last_edited_date > (CURRENT_DATE - 15)"
One difference is that I am unable to store credentials with the service since my ArcGIS Server instance is not externally accessible. You may want to remove this option and see if you are able to filter the data correctly. Another difference is that I tested with 10.5.1.
Is it possible to do this and update a feature dynamically to show all features where LastEditDate is not within the current Month range? So each month the feature query is updated to show all the features that have not been edited in the current month.
Any idea why GETUTCDATE does not work in the place of CURRENT_TIMESTAMP for a service based on a Geodatabase stored in SQL Server? Is CURRENT_TIMESTAMP the only DateTime Function supported?
Would any know how I would filter a date field by the last hour? I am currently rendering a NOAA Observed Weather Station to show current air temperature/wind speed/etc. When I add the map service to AGOL the service creates three points that show the current weather over the past three hours. I would like to show only one point for the past hour. I played around with a couple functions but was unsuccessful in the filter. The code below is what I currently have in JSON format in the Admin Tools. Any help would be appreciated.
"definitionExpression": "(clat BETWEEN 31 AND 37) AND (clon BETWEEN -114.5 AND -108.95) AND (obstime BETWEEN timestamp '2018-02-07 07:00:00' AND timestamp '2018-02-08 06:59:59')"<SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
Hey Eric, I was able to get mine to work with the following code
"definitionExpression": "(CURRENT_TIMESTAMP < DATESTART AND CURRENT_TIMESTAMP + 14 > DATESTART) OR (CURRENT_TIMESTAMP BETWEEN DATESTART AND DATECOMP)"
Hi Rick. I am currently adding the code like this
"definitionExpression": (obstime < DATESTART AND obstime + 14 > DATESTART) OR (obstime BETWEEN DATESTART AND DATECOMP)"
and I am getting an error function in AGOL.
Where does the the obstime field name fit into this, or did you enter the code as above?
My fields are DATESTART and DATECOMP. CURRENT_TIMESTAMP expression to get the current date time. So if you have observedtime and do not have a date start/completed, you will need to adjust accordingly.
So for yours if you just want to see a station within the last hour you just be able to use the filter in webmap to get obstime 'in last' hour .. you shouldn't have to do that in agol assistant. If for some reason you do here is the sql functions:
Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS
Anyone have luck filtering down to the past x hours/minutes rather than just days? I have edited the JSON with the following equation:
"definitionExpression": "(GPSFixTime BETWEEN (current_timestamp -0.01041667) AND (current_timestamp +1)"
My intention here is to view the past 15 minutes of data. Interesting thing is when I open up the filter in AGOL, it says it is filtering the last 15 minutes, yet the data being displayed is beyond 15 minutes. I tried another filter that showed the past 1 hour (-0.04166667) and got the exact same number of records as filtering the past 15 minutes. The filter window said "GPSFixTime in the last hour".
Jason Ehrig-Page it looks like you have an extra parenthesis in your query:
Does the same occur when you have the following:
"definitionExpression": "GPSFixTime BETWEEN (current_timestamp -0.01041667) AND (current_timestamp +1)"
I tested this and it worked successfully for me:
I have tested without the extra parenthesis with the same results as well with a number of other similar methods. I was trying to filter other parameters, thus the reason for the extra parenthesis. If you are able to get the filter to work, then it tells me that at least AGOL does support this and this is possibly something with our settings or data.
I think I got the wrong screen grab on the last comment. That was another method I was trying out.
Jason,
Are you sure the definitionExpression is in the correct location. - may I advise apply a simple filter and then going to arcgis assistant to edit the actual query. At least that way you know it's in the proper location.
Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
