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.

timmy
  • 193
  • 1
  • 6
  • 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) – alphabetasoup Dec 14 '15 at 00: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 02: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. – alphabetasoup Dec 14 '15 at 02:10
  • Please consider adding your edit as an answer so this question can be considered resolved. – Aaron Dec 14 '15 at 02:39

1 Answers1

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.

alphabetasoup
  • 8,718
  • 4
  • 38
  • 78