1

I have a setup where I have a SQL Server database with two tables: one table storing the geometry of the shapes I want to display, another table with some attribute value that is used to color and label the shapes. Then I use this data to generate WMS tiles that are displayed in a web front-end to the end user. In my data, the geometry does not change, but the attributes change very often.

In SQL Server, I configured a view joining the two tables, then used this view as a data source for Geoserver. Then I configured a SLD using these attributes to get the style I want.

It works. However, I have some performance issues, which seems to be linked to the necessity of reloading the geometry from the database for each query. My first idea was tile caching, but I can't use it because there are so many possible values for the attributes, and they change very often.

Ideally, what I would like to do is to have Geoserver cache the geometry data and for each new WMS query only look up the attributes to use and generate a style based on that, instead of retrieving both geometries and attributes at the same time. Is it possible to achieve this?

I am currently using Geoserver, but if it is possible to do it with a different WMS server, I would be interested as well.

1

TL,DR; No, there is no way to do this in GeoServer.

There are several possible issues as to why your queries are slow:

  1. You are using SqlServer, consider switching to PostGIS
  2. You are using a view, consider using a table or a materialized view if SqlServer supports them
  3. You may not have a spatial index on your view (again, I don't know if SqlServer can do that).
  4. You may not have an attribute index on your view for the attributes used in your style.
  • Thank you for your answer. No, we can't ditch SQL Server, this is only a small part of a much larger application. I think we have the correct indexes in place, but I will check again. – Leyan Feb 10 '18 at 16:31

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.