Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
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 Mar 7 at 16:09
2  
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 Mar 7 at 16:18
up vote 3 down vote accepted

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.

share|improve this answer
    
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 Mar 7 at 16:43
1  
What makes you think SLD filtering implies loading everything in memory and then filter there? ;-) – Andrea Aime Mar 7 at 18:29
    
Sorry I think I missed that :) – TobsenB Mar 8 at 7:12

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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