5

Hoping someone can help me with this issue. I've got a map implemented in my web application using OpenLayers that makes WMS queries to GeoServer that in turn queries a Postgres (PostGIS) database to produce WMS tiles. It also makes use of WMS GetFeatureInfo to get information about features being moused over.

Because of the amount of data that is being queried in the initial load for the tiles can take anywhere between 1-10+ minutes depending on the data that is being queried (query can be adjusted by date/time ranges and a few other properties), which is acceptable because it's a lot of data. But I've got a couple of performance related issues that I'm trying to get around.

  1. Every time the zoom level is changed the database has to be re-queried. Is there a way the data can be cached so that it doesn’t have to be re-queried for each change in zoom level? The data doesn’t change depending on zoom level so the query being run is returning the exact same result each time.
  2. When a user mouses over a feature, a GetFeatureInfo request is made and a popup is shown over the map detailing that feature. It once again has to run the whole query again and then it narrows it down to the feature that has been moused over.

Previously before changing to using WMS we generated a single KML file from the data we got from our database query and then this would be used for loading the map at all zoom levels and getting the feature info. We had to change from this approach to deal with bigger and more complex polygons.

So simply, is there anyway to get GeoServer to use a cache result of the query data or is there any way I could perhaps generate the KML file how I was before but then pass it to GeoServer to render it as WMS tiles?

Would much appreciate any input on the matter or any suggestions to get around the problems I've stated.

| improve this question | |
  • 1
    But why is your query so slow? Optimized query with correct use of indexes should be really fast, despite of data amount. – user1702401 Dec 10 '12 at 11:59
  • Performing an ST_Intersects JOIN on ~3 million points of data in 2.5 minutes isn't exactly slow. Yes of course it could be faster but I've got advise on improving the performance on here and its as fast as it can be for now. – Mark Davidson Dec 10 '12 at 12:21
5

Given that you are requesting WMS tiles, you can use a cache service like WMS-C (WMS Cached), Tile Map Service (TMS) or Web Map Tile Service (WMTS); the latter is an OGC standard. There are several implementations of these standards; to mention some, there are GeoWebCache, TileCache or MapProxy.

Because you are using GeoServer, I think that the best solution in your case would be GeoWebCache, that has a very good integration with GeoServer. Among the standards mentioned before, perhaps WMTS is the one that suits your needs the most, because WMTS can cache the results of GetFeatureInfo requests. Currently GeoWebCache 'fully implements WMTS using KVP'.

| improve this answer | |
  • Thanks for your reply. Just a note your link to TileCache is wrong missed out the second 'c'. Looking into your suggestions at the moment. – Mark Davidson Dec 10 '12 at 10:41
  • Thanks for your suggestions I've had a good look through them, but unfortunately none of them seem to be suitable unless I've missed something. Implementing tile caching might help a little but the data is updated quite regularly and because the tiles are generated from user queries each query will generate quite different results and just changing the temporal part of a query to add another month or whatever would mean caching an entire other set of tiles. – Mark Davidson Dec 10 '12 at 11:40
  • Perhaps you are missing two things. First, the WMS cache can be seed, by using the cache seeder application, before users use the service. Second, there is no need to 'refresh' the whole WMS cache after there is change on the geographical informations. If the changes are limited to certain geoghaphical areas, updating just the tiles corresponding to them is enough; the TileCache seeder application allows limiting the portion of WMS cache updated to a given bounding box (see goo.gl/rS1dG). – dariapra Dec 10 '12 at 12:21
4
+50

For caching postgres queries you should take a look ad pgPool II. pgPool is a middleware for postgres which, among other useful things, has an in-memory cache for queries. Please note that if even a single parameter changes the results will not be fetched from the cache (for obvious reasons).

Depending on how the queries are made if the BBOX changes the cache will be bypassed, but perhaps, depending on the client software (which you did not specify), you can work around that.

As for caching GetFeatureInfo requests you should first see if pgPool can handle those for you as well. If it doesn't you can front GeoServer with a caching reverse proxy like Apache mod_cache or Varnish.

| improve this answer | |
  • Now this I like the sound of :) Going to have a look into it. – Mark Davidson Dec 10 '12 at 12:23
  • The client is using OpenLayers. – Mark Davidson Dec 10 '12 at 12:24
2

Assuming your dataset doesn't change on a regular basis I think @dariapra's approach is the best for rendering images, but unfortunately it won't change the performance of your GetFeatureInfo requests - the database still needs to be queried each time.

You might consider using GDAL's ogr2ogr (or pg2shp but I've never used this) to export your dataset into a separate file store overnight. If it's acceptable for the data to be up to 24 hours out of date this approach could significantly improve rendering and querying performance. I've done this to export data from a busy Oracle database to a local File GeoDatabase and the improvement was huge.

Depending on the data volume .shp might not be suitable due to its 4GB limit, but you could look at other file formats that GeoServer supports.

If it's not acceptable for the data to be up to 24 hours out of date then neither this approach or the WMS tile cache will work for you. In this case your best option might be revisiting the data model to look at denormalisation or other performance tweaks.

| improve this answer | |
  • thanks for your input. How up to date the data is isn't cripplingly important. But because the user can select date ranges to query from and from many datasets to use in aggregate there isn't really anyway I could do what you suggest. Although if I can export a query to a shape file do you know if there is anyway I can get geoserver to just read that file passing its path as part of the query? – Mark Davidson Dec 10 '12 at 11:44
  • @MarkDavidson I don't understand how changing query parameters is the problem - you said you used to serve up a KML export of the data (and presumably this was rendered on the client - you didn't specify). KML was abandoned because of the data volume. Exporting to Shp or FGDB is the same approach as the KML, but maps are rendered on the server instead. If you export the entire (joined) dataset to file the client can still pass parameters that restrict rendering of that file dataset in whatever way they want (using CQL or ogc:filter). – tomfumb Dec 10 '12 at 17:35
1

Well there is lots of options you can do to improve speed. It depends on your skills.

If you have long queries you may be in need to tune Postgresql memory resources.

If you have javascript skills you can override the WMSGetFeatureInfo control from Openlayers and cache results into control to avoid repeated queries.

If you know a little about server languages like PHP, Python etc then you can load mapscript into server language and make a WMS service to reply to your complex query avoiding a run to geoserver then postgres. In mapserver they call this Mapscript Wrappers http://mapserver.org/ogc/mapscript.html

  • request -> target language -> postgres

You going to need to discover where is the bottleneck first.

| improve this answer | |

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.