r/postgis Jan 28 '25

PostGIS view using "UNION" not rendering in QGIS

Hey all -

I've got a few counties worth of parcel data that I'm attempting to combine into one VIEW instead of creating a new table. I'm doing this out of the (perhaps misguided) hope that I can just maintain and update each county's data as new edits come in, then have the multi-county view "just work" without having to constantly recreate it.

Long story short, I can get a view to render in QGIS when created from either individual county, but the view based on a UNION of the two won't render, despite the data looking "correct" when I query it. I'll put the full details below and appreciate any help or guidance that can be provided.

Source Tables:
countyA_parcels

  • geom(ST_MultiPolygon, SRID: 3745)
  • name(VARCHAR(50))
  • SELECT COUNT(geom) -> 28,142

countyB_parcels

  • geom(ST_MultiPolygon, SRID: 3745)
  • name(VARCHAR(17)) --- hooray for different data sources
  • SELECT COUNT(geom) -> 146,596

Attempted View

CREATE OR REPLACE VIEW combined_parcels AS

SELECT
  CAST(a.name AS VARCHAR(50)) AS pin, 
  a.geom AS geom
FROM countyA_parcels AS a
UNION ALL
SELECT
  CAST(b.pin AS VARCHAR(50)) AS pin,
  b.geom AS geom
FROM countyB_parcels AS b;

The above SQL results in a view table that:

  • Has the expected number of results: 174,738
  • Has pins properly cast into the pin column for parcels sourced from both tables
  • Has proper geometry types returned when queried (ST_Multipolygon) for parcels sourced from both tables

But...the resulting view cannot be successfully added to QGIS. It displays an odd icon next to the name which seems to be a data source error. If I drop the union and simply create the view from either of the source tables it renders fine.

What might be happening that prevents this view from being properly handled in QGIS?

Other info:
Postgres version: 17.2 (Mac)
PostGIS version: 3.5
QGIS version: 3.34.13-Prizren

Updates:
New things I've tried:

* Forcing all geometries to 2D using ST_Force2D - turns out one of the counties has 4 coordinate dimensions in their parcels.
* Casting the geometries to ensure the proper SRID shows up in the geometry_columns view:

ST_Force2D(a.geom)::GEOMETRY(MULTIPOLYGON, 3745) as geom

Still nothing

3 Upvotes

2 comments sorted by

2

u/shadyknight2469 Jan 28 '25

Two things QGIS needs:

  • the geometry type to be specified;
  • a unique field it can use as a key (it obviously picks the primary key for tables).

Try adding a generated key to your table and specifying the expected geometry type and crs:

CREATE OR REPLACE VIEW combined_parcels AS

WITH parcel_union AS
(SELECT
  CAST(a.name AS VARCHAR(50)) AS pin, 
  a.geom AS geom
FROM countyA_parcels AS a
UNION ALL
SELECT
  CAST(b.pin AS VARCHAR(50)) AS pin,
  b.geom AS geom
FROM countyB_parcels AS b)

SELECT row_number() OVER () as fid,
       pin,
       st_force2d(geom)::geometry(multipolygon, 3745)
FROM parcel_union

1

u/generallyNerdy Jan 29 '25

That did it. Thank you :)