1

I created a view of a table containing line segments, where many lines may be part of the same project (and not necessarily adjacent) in PostgresSQL/PostGIS. The segments are joined to another table containing text attributes for that project.
I dissolved these lines in a 2nd view, so that all lines in a project would be one row, using st_union and group by (see below). Both the source table and the view are published as layers in GeoServer. There are many less rows in this view that the original table (700 from 17,000).

The problem is, the dissolved view layer loads only marginally faster (in GeoServer preview and in the webapp), and when I click on this new layer (to see a popup with attribute info) in my webapp using OpenLayers 3, the popup response is much slower than the original table layer - that is, it takes several seconds for the popup to appear when clicking on the dissolve view layer, while it appears quickly when clicking on the original view layer.

Is my dissolved view created correctly?

dissolved view:

CREATE OR REPLACE VIEW projects_dissolve AS 
  SELECT projects_geo.id,
  st_union(projects_geo.geom) AS singlegeom
FROM projects_geo
    LEFT JOIN projects ON projects_geo.id = projects.id
GROUP BY projects_geo.id,

2 Answers 2

4

When using views with GeoServer you should be cautious about performing any expensive queries since each time the layer is requested the SQL for that view is executed. In your case it is likely that whenever you move the map (or click a feature for the attribute information) it runs your union query which is not really practical.

If your data doesn't change too often you can use materialized views which effectively store the view statically, only updating when you tell it to. These materialized views are actually recommended in cases where your views are expensive e.g. large joins etc.

2
  • ok thanks I will try that..but in my case, I am not having any trouble when I move the map. The slow response occurs when I click on the dissolved vie layer, as I noted, to see a popup window. When I click on the un-dissolved view layer with the same data, the popup appears nearly instantly.
    – JasonBK
    Jan 3, 2020 at 18:31
  • ps- i looked into materialized views, since our data does in fact change very often, I don't think that will work, but thanks for suggestion.
    – JasonBK
    Jan 8, 2020 at 21:15
3

When the map is displayed, the view will fetch the rows, group them by a (likely) indexed field and return the merged geometries. Since there isn't too many geometries, it is fast.

When clicking on the map, the request is basically to identify which geometry is at a given location. To do so, the complete view is computed, then a spatial comparison is done between the click coordinates and the not-indexed merged geometry.

The two bottlenecks here are 1) there is no spatial index and 2) the spatial comparison is done on possibly large and complex geometries.

It is expected to be faster on the original geometries since they are (likely) indexed and simple, even though there are more rows.

--> use the original geometries or
--> use a materialized view as suggested by @Ali, with an index on the geometries.

1
  • I don't think a materialized view will work in this case since the data changes frequently. I don't want to use the original geometries, because that layers loads slowly (although the dissolved layer isn't really that much faster to load for some reason, as I noted above). I suppose I could add a 'ghost' undissovled layer of the same data to use for the click, but that seems like a weird workaround.
    – JasonBK
    Jan 8, 2020 at 21:14

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.