Show me features from ‘last x number of days’ is a very common workflow especially with datasets that keep updating frequently. Earthquakes, sales, traffic volumes, crimes etc. are some examples where data gets updated daily or hourly or at an irregular frequency.
For this exercise, let’s assume we have a global earthquakes data that keeps growing. And to keep it simple, the feature layer points to a dataset that have earthquakes that are recorded for the entire year. Viewing one year worth of data on a map is too much information. Instead we usually want to see only earthquakes recorded in last 24 hours or last 7 days or last month without needing to modify the query/filter every time.
How can we do that?
This part is actually pretty simple:
- Add the feature layer in a map in ArcGIS Pro.
- Open the layer’s Properties page and switch to the Definition Query page.
- Click New definition query.
- Pick the date field from the fields drop down list.
- Select is after from the operator drop down box.
- Click SQL from the upper right corner to switch to the sql mode.
- An incomplete clause like the one below will be shown.
- Pick an expression from the table below that is appropriate for your data source and enter that in the text box right after the ‘>’ sign. Make sure to have a space in between.
Database platform Expression File geodatabase CURRENT_DATE ‐ 7PostgreSQL CURRENT_TIMESTAMP ‐ INTERVAL '7 days'SQL Server DATEADD(dd, ‐ 7, getdate())Oracle CURRENT_DATE ‐ INTERVAL '7' DAYNote: if you copy/paste from this above table, you might need to delete the dash and single quotes characters, and retype in.
- It should look something like this
- Click Apply followed by OK button to apply this dynamic definition query to your feature layer.
Now we have got a layer that will always show earthquakes that occurred in last 7 days.
Looking back in the past
Let’s make it a bit more interesting. The feature layer has data for the entire year. Because I have the ‘last 7 days’ definition query set to the layer, I can’t view/query any feature outside that filter. Of course, one option is to remove (or temporary turn off the definition query), and apply another definition query for the time period that I need. But that won’t give good user experience, and when I will share, I might need to create multiple layers (see the very last section below).
Good news is that there is a better option available now. Only gotcha is that you must have your data copied in an enterprise database. This will allow me to:
- View earthquakes from last 7 days by default, and
- Whenever I need to view/query earthquakes from any other time window, I can simply enable the time slider on the map and navigate to that time period.
Let’s go over the steps:
- Choose Add Data | Query Layer command from the Map ribbon.
- Select a database connection.
- Provide a name.
- Enter a SQL like below (here the name of my table is earthquakes).
select * from earthquakes where ::r:TimeRange
Note: the name after ::r: can be anything e.g. ::r:XYZ
- Click the pencil icon that appears next to ::r:TimeRange.
- a) enter the date field name in Field or Expression text box.
- b) choose Date from the Data Type drop down box.
- c) check Default Value on.
- d) copy/paste the appropriate expression from the table above.
- e) enter current_timestamp in the textbox right after to label.
- f) click Done.
- Click Validate.
- Click Next.
- Pick Unique Identifier Field(s), Geometry Type and Spatial Reference if not get chosen automatically on the next page.
- Click Finish.
- Now you should see your layer showing 7 day earthquakes (starting from now).
- This layer is a time aware query layer.
- The time slider should appear on the top of the map view.
- Enable the time slider and navigate to any time window to see the map showing earthquakes from that specific time window.
- This data can be queried or used in a geoprocessing tool for further analysis with this subset of data.
- Disable the time slider and the layer will only display earthquakes from the last 7 days.
Can this be shared as a service?
Yes, I can share this out as service with enterprise environment. This map can be shared out as as a map service/map image layer on a standalone server or on an enterprise portal respectively. Then it can be consumed in a portal (or ArcGIS Online) map viewer, and the same user experience can be observed there too. You can add this as a map image layer, or a feature layer. When you need to use a feature layer on your web app, use the url of the sublayer off that map service (the url ends with /MapServer/<id>) instead of the map service root url (that ends with /MapServer).
Any other benefits?
With traditional approach, I need to create multiple layers. See screenshot below for an existing map service where for each pre-authored time period, they needed to create one layer – one for last past week, another for past months and a third one for the entire time period.
With this approach, all I need is one single layer and achieve what the traditional approach allows, and then some more.
Important links:
Just a note, this blog is based on an Esri community forum discussion we had sometime back. I had a similar discussion recently that prompted to write this as a blog for a wider audience.
Commenting is not enabled for this article.