5

I'm working on a PostgreSQL database with PostGIS. I need to filter an 8 million records table in order to publish a view through geoserver.

I know two ways to do that, one is that I create view in PostgreSQL to filter the data and publish on geoserver, the other one is let the geoserver making the filtering using sld.

My question is that which one is the better solution regarding to gain better performance.

Improve this question
2
  • It depends on the number of featues in the result set. Using a spatial index to return 7999956 rows would be slower than filtering in the application but if the result set is restricted to less than 5k-10k rows, there's no question what would be faster.
    – Vince
    Commented Mar 7, 2016 at 16:09
  • 3
    It depends on how the PostGIS datastore decides to do the filtering, If it simply converts the SLD filter into SQL WHERE... there should be not much difference if any. Turn on the GeoTools developer logging from the admin utility of your GeoServer and have a look at the SQL it is generating. Naturally you must make sure that you have an index on the fields which you need for filtering.
    – user30184
    Commented Mar 7, 2016 at 16:18

2 Answers 2

Reset to default
4

GeoServer turns the filters available in SLD into an equivalent SQL query (by or-ing the conditions of the various rules, if there is more than one), so as long as the SLD is simple enough, the performances should be good.

There are limits however, by default GeoServer does not send down the filters into the DB if there are more than 20 rules, to avoid issues with the SQL growing too large (some commercial databases simply cannot handle well queries that become too large)

Improve this answer
3

I would strongly recommend to do tasks like the one you mentioned (at least for large datasets) in the database. The DB is designed to work with large amounts of data and will be much more performant than the geoserver since it can perform the operations within its internal data representation.

If you would take the other approach the Geoserver instance first would have to get all 8 million records from the DB and filter them (which would definitely work for smaller datasets) but I guess in your case the amount of records is way too high to perform the task with the methods provided by the Geoserver.

Improve this answer
3
  • Thanks all for your answers and comments. The result of the filtering is around 35 thousand rows, so that is not so big. Actually I tested both version, and on the final screen the displaying of the geometries were faster when I used the way filtering directly in the database.
    – Zsolt Stegena
    Commented Mar 7, 2016 at 16:43
  • 2
    What makes you think SLD filtering implies loading everything in memory and then filter there? ;-)
    – Andrea Aime
    Commented Mar 7, 2016 at 18:29
  • Sorry I think I missed that :)
    – TobsenB
    Commented Mar 8, 2016 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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