Think for a moment about how many ways we talk about dates and time. In regular language, we can say things like “last Tuesday” or “week after the week after next” or “Friday the 12th” and people generally can figure out when we’re talking about. Add time into the mix and phrases start getting strange: “noon-thirty” and “half-past midnight” are my favorite oddities.
The actual date-time standard is published as ISO 8601 and talks about what date-time data should look like, but programming languages, databases and other programs implement this standard with sometimes vastly different interfaces and rules.
PostgreSQL’s handling of date-time data takes the form of 6 different field types, all suited to different data needs. For the OpenTreeMap project, we’re using the timestamp data type which allows us to store both dates and times for events down to the microsecond.
Geoserver accepts a single date-time type (also called timestamp) that stores dates and times at the same resolution as PostgreSQL’s data type, so it reads PostgreSQL’s date-time data easily. Adding a date filter to Geoserver’s SLD files is also fairly easy so long as you know how Geoserver wants date-time data to be formatted. The various formats that Geoserver knows how to interpret are located here. So, if we have a field in a database table called “last_updated”, then an example SLD filter might look like this:
<Filter>
<PropertyIsLessThan>
<PropertyName>last_updated</PropertyName>
<Literal>2012-01-01 00:00:00</Literal>
</PropertyIsLessThan>
</Filter>
This filter would display any updates made before midnight on January 01, 2012. Another example (from the OGC 1.0 encoding specification’s examples) catches updates between certain dates:
<ogc:Filter>
<ogc:PropertyIsBetween>
<ogc:PropertyName>last_updated</ogc:PropertyName>
<ogc:LowerBoundary>
<ogc:Literal>2011-12-01 00:00:00</ogc:Literal>
</ogc:LowerBoundary>
<ogc:UpperBoundary>
<ogc:Literal>2011-12-31 23:59:59</ogc:Literal>
</ogc:UpperBoundary>
</ogc:PropertyIsBetween>
</ogc:Filter>
This filter would display any updates made during the month of December, 2011. Anything that was updated outside the specified date and time parameters would bypass these filters and go on to check any other rules in the SLD file. This works great if we’re only interested in static date comparisons. But what if we want to see updates less than a week old? Or objects that haven’t been updated for more than three months? This kind of dynamic filtering is a little harder to do.
After digging through the Geoserver documentation and a lot of googling, we decided to shift the dynamic part of the filter into a PostgreSQL view. Our test view included an id field, a geometry field and a field that calculates the number of days since the last update to that object. The view sql looks like this:
CREATE OR REPLACE VIEW timestamp_test AS
SELECT
treemap_tree.id,
date_part('days'::text, now() - treemap_tree.last_updated) AS days,
treemap_tree.geometry
FROM treemap_tree;
So now we can add this view to Geoserver as a source layer and it will see the new days field as a static number field. We can use any of the property filters on this field and style recently added data in a dynamic fashion.
