Los usuarios necesitan aplicar un filtro de fecha dinámico a una capa de entidades dentro de su mapa web. Por ejemplo, pueden necesitar mostrar todas las entidades que fueron editadas en el día actual. En lugar de tener que actualizar manualmente el filtro en el mapa web cada día, los siguientes pasos le guiarán sobre cómo hacer que esta fecha se actualice automáticamente cada día.<\/P>
<\/P>
Nota: Antes de continuar, querrá asegurarse de que 'Allow only standard SQL queries' esté desmarcado dentro de Mi Organización > Editar Configuraciones > pestaña Seguridad:<\/STRONG><\/P><\/P><\/P><\/P>Pasos<\/SPAN>:<\/STRONG><\/P>1. Dentro de su mapa web, aplique un filtro a su capa:<\/P><\/P>2. Guarde su mapa web y use una utilidad como GeoJobe Admin Tools<\/A> o AGOL Assistant<\/A> para editar el JSON. En el siguiente ejemplo, se usa AGOL Assistant. Después de iniciar sesión en AGOL Assistant con sus credenciales AGOL, haga clic en 'I want to'<\/STRONG> y elija 'View an Item's JSON':<\/STRONG><\/P><\/STRONG><\/P><\/P>3. Navegue y seleccione el mapa web que guardó previamente. A la derecha, desplácese hasta ver Data <\/STRONG> y encuentre la capa que tiene aplicado el filtro (definitionExpression):<\/P><\/P><\/P>4. Haga clic en el lápiz en la parte superior derecha para comenzar a editar. Luego querrá actualizar la definitionExpression usando una función SQL (es decir, GETDATE()<\/STRONG>). En el siguiente ejemplo, al restar 1 y sumar 1 a la función GETDATE<\/STRONG> se restará/sumará un día. Además, se usa la función CONVERT<\/STRONG> para truncar la hora de la fecha. Esto es para asegurar que está obteniendo todo el día en lugar de un período de 24 horas. Así, la expresión filtrará todas las entidades entre ayer y mañana (es decir, hoy).<\/P><\/P><\/P><\/P>NOTA: <\/STRONG>La expresión de definición será relevante para la geodatabase que esté usando. Lo anterior funcionará para un servicio de entidades alojado en ArcGIS Online. Sin embargo, si está usando un servicio alojado en ArcGIS Datastore (que almacena los datos en una base de datos PostgreSQL) o un servicio de entidades que consume datos desde Oracle, la expresión de definición sería:<\/P><\/P>"last_edited_date BETWEEN (CURRENT_DATE - 1) and (CURRENT_DATE + 1)"<\/STRONG><\/P><\/P>Para SQL Server, la expresión de definición sería:<\/P><\/P>"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 1) and (CURRENT_TIMESTAMP + 1)"<\/STRONG><\/P><\/P>5. Guarde sus cambios haciendo clic en la opción guardar en la parte superior derecha.<\/P><\/P>6. Regrese a ArcGIS Online y vuelva a abrir su mapa web volviendo a 'My Content' y seleccionando abrir el mapa web en el Visor de ArcGIS Online. Si hace clic en la opción Filtro, la expresión debería estar vacía tanto en las pestañas Ver como Editar:<\/P><\/P><\/P>El filtro será dinámico, por lo que a medida que pase cada día, el filtro se actualizará sin interacción del usuario.<\/P><\BODY>
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.
Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
