Sunday, 3 January 2016

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



Answer



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.



No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...