1

I have published a table with @ 6 million polygons to geoserver, and have built a tool using the OpenLayers filter class to query the data. The performance is abysmally slow, often times not ever completing the query. I have indexed the columns I am filtering on and have also partitioned the table. The biggest issue is with the LIKE queries, and most recently I have applied the gin index as referenced in this post:

 https://blog.ianturton.com/postgis/2018/04/04/speeding-up-likes.html

But the fetch request with the query just spins forever when a LIKE filter is applied. On the desktop, the same queries run and fairly fast. When applying equivocal filters the query does execute, though still slower than in the desktop. What could be causing the slow down?

Here is the code I am using to generate the request, the filter_val variable is set via a form:

var featureRequest = new ol.format.WFS().writeGetFeature({
        srsName: 'EPSG:3857',
        featureNS: 'http://geoserver.sf.net',
        featurePrefix: 'sde',
        featureTypes: ['parcels_slim_part_view'],
        outputFormat: 'application/json',
        filter: filter_val
        // count: 100
      });

    controller = new AbortController();
    signal = controller.signal;

    var throbberCounter = 0;
    var increaseThrobberCounter = function() {
        document.getElementById('throbber').style.display = 'block';
        ++throbberCounter;
    };
    var decreaseThrobberCounter = function() {
        --throbberCounter;
        if (throbberCounter <= 0) {
            throbberCounter = 0;
            document.getElementById('throbber').style.display = 'none';
        }
    };


      // then post the request and add the received features to a layer

      increaseThrobberCounter();
      this.throbberVisible = true;
      fetch('http://localhost:8080/geoserver/wfs', {
        method: 'POST',
        signal: signal,
        body: new XMLSerializer().serializeToString(featureRequest)
      }).then(function(response) {
          console.log(response);
          console.log(response.url);
        return response.json();
      }).then(function(json) {
        var features = new ol.format.GeoJSON().readFeatures(json);
        if (features.length == 0){
            decreaseThrobberCounter();
            this.throbberVisible = false;
            alert('no records found, try again');
        }
        else{
        filterSource.addFeatures(features);
        map.getView().fit(filterSource.getExtent());
        decreaseThrobberCounter();
        this.throbberVisible = false;
        map.addLayer(filter_lyr);
        filter_lyr.setVisible(true);
        }
      }); 
  • When you say 'on the desktop', do you mean when you query the database directly with SQL? I'm wondering if instead of using the OpenLayers query capabilities, you instead use requests to the server against WMS/WFS using CQL filters instead? I wonder if the OpenLayers query capabilities were meant to handle such large datasets especially with LIKE queries... – DPSSpatial Aug 13 '18 at 20:19
  • @DPSSpatial yes when I query using SQL. I was wondering the same about using CQL filters instead of the openlayers filters. Will test this.. – kflaw Aug 13 '18 at 21:45
  • Great. Do report back, as we're testing this as well, and so far CQL queries on GeoJSON requests have been working great - though our data isn't anywhere near yours in terms of size! – DPSSpatial Aug 13 '18 at 22:02
  • @DPSSpatial, i will do that. Do you mind sharing a code snippet to show how are you implementing the cql filter? Currently i'm generating a feature request with a format filter applied and then using fetch to retrieve the features, trying to figure out how I can modify what I've got to use CQL instead. – kflaw Aug 13 '18 at 22:47
  • 1
    Please add an example of a slow LIKE query and the EXPLAIN ANALYZE output of this query. Also, you can track slow queries in your log: Add log_min_duration_statement = 1000 to your postgresql.conf. – pLumo Aug 14 '18 at 8:18
1

We use CQL filters applied to the Geoserver GeoJSON service, which take the LAT/LON result of a geocode to return the intersecting school boundary feature:

https://servername/geoserver/dpspgisprod/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=dpspgisprod:dps_boundaries_projected&maxFeatures=1000&outputFormat=application%2Fjson&CQL_FILTER=intersects(geom, POINT(-105.04675 39.755135))

Trying to go through our developers code, this seems to be the place the GeoJSON is called/handled:

// add colored polygon for district boundary that point is in
// params: geoJSON boundary object returned from getSchoolDistrict
const addDistrictBoundary = (districtBoundary) => {
    const existingLayerIndex = coloredBoundaryLayer.getLayers().findIndex(layer => layer.feature.id === districtBoundary.id);

    if(existingLayerIndex < 0) {
        coloredBoundaryLayer.addData(districtBoundary);
        coloredBoundaryLayer.setStyle({color: '#b20000', opacity: 1, fillColor: '#b20000', fillOpacity: .5});
    }
};

// get point-in-polygon analysis from GIS server to determine which district the address is in
// params: array containing lat/long coordinates of the address
const getSchoolDistrict = async (coords) => {
    let schools = {};
    await fetch(`https://servername/geoserver/dpspgisprod/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=dpspgisprod:dps_boundaries_projected&maxFeatures=1000&outputFormat=application%2Fjson&CQL_FILTER=intersects(geom,%20POINT(${coords[0]}%20${coords[1]}))`)
        .then(results => results.json())
        .then(JSONresults => {
            const polygons = JSONresults.features;
            if(polygons.length > 0) {
                schools = polygons[0];
            } 
        })
        .catch(error => console.log(error));

    return schools;
};
  • Thanks for this. I understand how to construct the url but am unsure of the appropriate code to make the request. Was thinking I could replace featureRequest in the serialize to string line with the url including cql filter but it is giving me an error. – kflaw Aug 14 '18 at 17:55
  • thanks for posting the code I ended up figuring out how to modify my fetch request, looks similar to what you posted – kflaw Aug 15 '18 at 2:14
1

As you do not provide an example query yet, this might or might not be your problem, but even if not it might help others.


LIKE queries use indexes only up to the first wildcard (% or _).


I once had a similar problem, querying file paths including underscores:

WHERE path LIKE '/the_path/to/the/files/000001.tif'

I had an index and optimized everything similar to you, but still the index was not being used.

The issue was that the beginning up to the first wildcard (/the) was the same for every row. So the index was not used as it was not able to filter anything.


Escaping the wildcards fixed the issue:

WHERE path LIKE '/the\_path/to/the/files/000001.tif'
1

An openlayers filter will require all of the data to be sent to the client to be filtered and then drawn. The blog link will only be useful if your filter is passed on to the database, so you need to make a WMS or WFS query with a filter which allows geoserver to pass the filter to the database.

  • thanks for your reply, so are you saying that the query should be contructed in the url, like using a CQL filter? – kflaw Aug 14 '18 at 15:20

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.