Filter the lowest / highest featureID in Geoserver CSS style rule

Question

I am using GeoServer's CSS extension to style a Points layer which I publish as WMS. In my style, I would like to specify a rule that filters for the first and last features in my data set. Something like min(featureid) and max(featureid).

The documentation suggests how to filter for specific featureIDs or ranges of IDs. Consequently, I can filter for the first feature in my data set (called Points) and style it with

#Points.1 {
  stroke: #d73027;
  stroke-width: 6;
  stroke-linecap: round;
}

because the first feature will always have featureid=1. However, how can I determine the last featureID in my data set without hard-coding it since it might change over time?

Answer 1

Meanwhile I found a solution to this problem (there might be others of course), using variable substitution in the WMS getMap request.

At first, create the static elements of my style as desired. I prefer using CSS Styling over writing the sld file directly but this is up to you.

Then, open the style in the Style Editor in GeoServer's Web Interface and add the following at the bottom of the SLD xml definitions, below the last <sld:FeatureTypeStyle>...</sld:FeatureTypeStyle> entry but still above the closing </sld:UserStyle> tag.

<sld:FeatureTypeStyle>

    <sld:Rule>
      <sld:Title>Start</sld:Title>

      <ogc:Filter>
        <ogc:PropertyIsEqualTo>
          <ogc:PropertyName>id</ogc:PropertyName>
            <ogc:Function name="env">
              <ogc:Literal>lowestID</ogc:Literal>
            </ogc:Function>
        </ogc:PropertyIsEqualTo>
      </ogc:Filter>

      <sld:PointSymbolizer>
        <Graphic>
            <Mark>
                <WellKnownName>square</WellKnownName>
                <Fill>
                    <CssParameter name="fill">#FF0000</CssParameter>
                </Fill>
            </Mark>
            <Size>6</Size>
        </Graphic>
      </sld:PointSymbolizer>

    </sld:Rule>
 </sld:FeatureTypeStyle>

Note the new <ogc:Filter> in the upper part. It defines a new rule which would be in plain text kind of like Only style features where the attribute "id" equals the value that is passed in the environment variable that is called "lowestID".

To apply the same filter also for the highest ID, just copy and paste it, changing lowestID to highestID and modifying the styling to your needs.

The lower part, between <sld:PointSymbolizer>...</sld:PointSymbolizer> defines the styling that is to be applied to the features that pass the filter. In this example a simple black square is used but it can be replaced with any valid styling description of course.

Then, the lowest ID has to be found out somehow before sending the GetMap request to GeoServer. As my instance of GeoServer pulls its geodata from a connected PostGIS database for which I have access, fortunately, I use the following SQL commands to find out the features with the lowest and highest IDs. Of course, the lowest ID will probably always be 0 or 1 but the number of features (and hence the highest ID) is not known in my use-case and maybe you want to filter for something completely different anyway.

SELECT
  id
FROM
  tableName
WHERE
 id = (SELECT min(id) FROM tableName)
;

Use max(id) instead of min(id) in the second last row to get the highest ID instead of the lowest one. The SQL command can be executed with JavaScript, Python or whatever you subsequently use to send you GetMap request to GeoServer, saving the result in a variable for later use.

Then, insert this variable in your GetMap request to GeoServer (e.g. in Leaflet, OpenLayers, ...) as follows (line breaks are inserted for the sake of readability):

http://yourGeoServer.com:8080/geoserver/wms?
request=GetMap
&service=WMS
&version=1.1.1
&layers=yourLayer
&styles=yourStyle
&srs=yourSRS
&bbox=yourBoundingBox(normally determined by Leaflet/OpenLayers)
&width=256
&height=256
&format=image%2Fpng
&env=lowestID:yourVariableHere;highestID=yourOtherVariableHere

The relevant part of the request is the last line. Make sure to insert the results of your SQL query here, e.g. via variable substitution, string formatting or whatever the preferred way of your language may be.

This will return a png image from GeoServer with your style applied as defined above, dynamically styling the features differently that you passed via the variables in the URL to the GetMap request.