0

I have a GeoServer running, using a Postgis database as storage for a workspace with a few different layers. These layers are accessible through WMS or WFS. One of the variables in this database is stored as a Date.

How should I create a ogc:filter that would allow me to select all variables for a specific month (An example could be: "select all data from June-August in 1990-1995"). I'd like this to work on both the WMS and the WFS services, but in general I think most solutions would?

I tried to use the PropertyIsLike-operator as if date was a string, but that did not work. Here is a case where I try to get all data from the month of May:

http://localhost:8090/geoserver/testWorkspace/wfs?REQUEST=GetFeature&SERVICE=WFS&VERSION=1.1.0&OUTPUTFORMAT=json&TYPENAME=elTestLayero&PROPERTYNAME=date&FILTER=
<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
    <PropertyIsLike wildCard='*' singleChar='.' escapeChar='!'>
        <PropertyName>date</PropertyName>
        <Literal>....-05-..</Literal>
    </PropertyIsLike>
</ogc:Filter>

That would return this exception:

<ows:Exception exceptionCode="NoApplicableCode">
<ows:ExceptionText>
    java.lang.RuntimeException: java.io.IOException java.io.IOException null ERROR: operator does not exist: 
        date ~~ unknown Hint: No operator matches the given name and argument type(s). 
        You might need to add explicit type casts. Position: 83
</ows:ExceptionText>
</ows:Exception>

It suggests that I add explicit type cast. How can I do that?

Other possible solutions I thought of:

  • Make a String variable which is just the same as the date variable (or I could simply add the month as a variable) in the database, and use this the way I tried to use date. This would however make the layer(s)/database slightly bigger, could it decrease the performance?
  • Write an more advanced sql-view for the layer with a where clause where the month(s) come from SQL view parameters. I wouldn't prefer this solution because it would require that the layer was set up in a certain way, and I'd like my software to handle different layers in a quite dynamic way.
2

If you know it'll always be by month, then I think your second suggested solution is the nicest, with the view column using *EXTRACT(MONTH FROM TIMESTAMP myTimestamp) AS month_of_the_year* allowing you to use use a GreaterThan/LessThan ogc filter on the month number (see below). Although the string match solution probably should work, it's not going to be as efficient as between filter.

<ogc:And>
 <!-- Between 1990 and 1996 -->
 <ogc:And>
  <ogc:PropertyIsGreaterThanOrEqualTo>
   <ogc:PropertyName>datetime</ogc:PropertyName>
   <ogc:Literal>1990-01-01 00:00:00</ogc:Literal>
  </ogc:PropertyIsGreaterThanOrEqualTo>
  <ogc:PropertyIsLessThan>
   <ogc:PropertyName>datetime</ogc:PropertyName>
   <ogc:Literal>1996-01-01 00:00:00</ogc:Literal>
  </ogc:PropertyIsLessThan>
 </ogc:And>
 <!-- Between June and August -->
 <ogc:And>
  <ogc:PropertyIsGreaterThanOrEqualTo>
  <ogc:PropertyName>month_of_the_year</ogc:PropertyName>
  <ogc:Literal>6</ogc:Literal>
 </ogc:PropertyIsGreaterThanOrEqualTo>
 <ogc:PropertyIsLessThanOrEqualTo>
  <ogc:PropertyName>month_of_the_year</ogc:PropertyName>
  <ogc:Literal>8</ogc:Literal>
 </ogc:PropertyIsLessThanOrEqualTo>
</ogc:And>

Alternatively, you could just loop through each year with the full start and end dates with:

<ogc:Or>
 <ogc:And>
  <ogc:PropertyIsGreaterThanOrEqualTo>
   <ogc:PropertyName>datetime</ogc:PropertyName>
   <ogc:Literal>*start_date_first_year*</ogc:Literal>
  </ogc:PropertyIsGreaterThanOrEqualTo>
  <ogc:PropertyIsLessThanOrEqualTo>
   <ogc:PropertyName>datetime</ogc:PropertyName>
   <ogc:Literal>*end_date_first_year*</ogc:Literal>
  </ogc:PropertyIsLessThanOrEqualTo>
 </ogc:And>
 ...
  repeat for each year
 ...
</ogc:Or>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.