3

I have created a SQL view from a large MultiPolygon table, and am attempting to serve it as a WFS layer via Geoserver.

While testing the layer, I've noticed that some of the features are missing. This is evident when loading the WFS layer, or also doing a SQL query on the view for the missing features (the query returns no results).

I've added 2 screenshots showing the original layer (grey polygons), and the view layer with missing features (orange polygons). original layer

view

Has anyone come across a similar issue, and a fix?

UPDATE

The source table table_a has a geom field as geometry(MULTIPOLYGON, 4326). It contains about 3 million records

The view queries table_a, and does a spatial join on a couple of other tables as well, like so:

SELECT
    a.feature_id,
    a.geom,
    a.name,
    b.label,
    c.name
FROM table_a AS a
    JOIN admin_boundaries AS b ON st_intersects(a.geom, b.geom)
    JOIN places AS c ON st_intersects(a.geom, c.geom);

I'm not using maxFeatures in my query, and Geoserver is set to maxFeatures = 0

SOLVED

As suggested by @alpha-beta-soup I was using a series of INNER JOINs, and some of my features weren't intersecting, so they were missing. Switching to LEFT JOINs solved the issue.

  • 1
    Please edit the question to provide more information about the table and the view definition. – Vince Dec 13 '15 at 21:32
  • 2
    Is the serving as WFS aspect relevant here? If an SQL query cannot find the features then I am thinking that your WFS is just respecting the SQL result. – PolyGeo Dec 13 '15 at 21:53
  • Are you using maxFeatures in your WFS request? (count in WFS 2.0.0) – Richard Law Dec 14 '15 at 0:42
  • @PolyGeo yes you may be onto something, about it not being Geoserver's fault. I've been considering that as well, though it's confusing that a completely-standard view would drop features (implying that PostGIS views themselves are buggy) – timmy Dec 14 '15 at 2:04
  • 1
    There's no way a routine SQL query is "buggy". Are you sure your features intersect? Try pull one of the offending features out individually and test the spatial intersection. – Richard Law Dec 14 '15 at 2:10
5

Your features do not properly intersect; using a series of JOIN statements in the definition of your view meant that not all features of your table_a table were retained in the final view. Using LEFT JOIN ensures that non-matching (i.e. non-intersecting) records are retained.

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.