More

PostGIS view has missing features

PostGIS view has missing features


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).

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

UPDATE

The source tabletable_ahas ageomfield asgeometry(MULTIPOLYGON, 4326). It contains about 3 million records

The view queriestable_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 usingmaxFeaturesin my query, and Geoserver is set tomaxFeatures = 0

SOLVED

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


Your features do not properly intersect; using a series ofJOINstatements in the definition of your view meant that not all features of yourtable_atable were retained in the final view. UsingLEFT JOINensures that non-matching (i.e. non-intersecting) records are retained.


Watch the video: Geometry Creation and Manipulation Using PostGIS. PostGIS Baby Steps