1

I've been creating views of clipped and intersected tables with PostGIS and QGIS. The views display correctly only when loaded from the QGIS Browser but not from the QGIS Database Manager. In fact the tables are displayed differently in both data trees. The Database Manager can't seem to distingish the geometry of the table even though the geometry column is there. The QGIS Browser on the other hand seems to have no problem displaying and loading the correct geometry. I've posted screen captures of the differences in how the two applications display the same data. I've also posted the code I'm using to intersect and clip the source tables. I am using QGIS 3.10 Coruna.

QGIS Browser - works well

QGIS Database Manager errors on same data

> CREATE VIEW "Modelling_3D"."Archeologie_REG_vw" AS
SELECT
a.id,
st_setsrid(st_intersection(a.geom, b.geom), 2959) AS geom,
a.numero_site,
a.code_borden,
a.easting,
a.northing,
a.emplacement_information,
a.superficie,
a.elevation,
a.identite_culturelle,
b.layer
FROM ("Environnement"."rag_Archeologie" a
JOIN "Modelling_3D"."AOI_Voxet_REG_ext" b ON (st_intersects(b.geom, a.geom)));
2
  • ST_Intersection returns GEOMETRYCOLLECTIONs if the process results in different geometry types, or any combination of simple and multi geometries if they're of the same type. QGIS ever only allows a single geometry type per layer, and fails to display invalid (empty) sets or geometry collections. It also scans the distinct geometry types, and offers a layer for each type that is no collection. The best you can do is homogenizing (and assigning a correct column definition) in the query.
    – geozelot
    Jun 25, 2021 at 16:05
  • @geozelot thanks for the explanation. Could you expand on how you "homogenize and assign a correct column definition" in a query? That would be appreciated.
    – Frederic
    Jun 25, 2021 at 19:41

1 Answer 1

1

A generic example on extracting simple geometries of distinct type <SIMPLE_TYPE> from (dumped) sets of arbitrary geometry types:

SELECT a.<col1>, [ a.<col2>, ..., ]
       b.<col1>, [ b.<col2>, ..., ]
       ST_SetSRID(dmp.geom, <SRID>)::GEOMETRY(<SIMPLE_TYPE>, <SRID>) AS geom
FROM   <table1> AS a
JOIN   <table2> AS b
   ON  ST_Intersects(a.geom, b.geom)
CROSS JOIN LATERAL
       ST_Dump(ST_Intersection(a.geom, b.geom)) AS dmp
WHERE  GeometryType(dmp.geom) = '<SIMPLE_TYPE>'
;

where the explicit cast (::GEOMETRY(<SIMPLE_TYPE>, <SRID>)) registers the (Views) geometry column in the PostgreSQL typemod system (making its definition easily available to QGIS via the geometry_columns View).

To extract multi types use ST_Multi(ST_SetSRID(dmp.geom, <SRID>)) to force any remaining simple type to its multi pendant.

2
  • Thanks that workes well when the "a.geom" is a simple point. I get the same error though when I apply the ST_Multi(ST_SetSRID(dmp.geom, <SRID>)) to the script.
    – Frederic
    Jun 28, 2021 at 17:21
  • This is the code I tried to use for a multipolygon but it returns no data and adds multiple geoms to the Browser tree again : <br/> SELECT a.id, a.objectid, a.classe, a.objet, a.source, a.no_mh, a.secteur, a.valeur, a.remarque, b.layer, ST_Multi(ST_SetSRID(dmp.geom, 2959)) AS geom FROM "Environnement"."rag_MilieuHumide" a JOIN "Modelling_3D"."AOI_Voxet_RAG_ext" b ON st_intersects(b.geom, a.geom) CROSS JOIN LATERAL ST_Dump(ST_Intersection(a.geom, b.geom)) AS dmp WHERE GeometryType(dmp.geom) = 'MULTIPOLYGON' ;
    – Frederic
    Jun 28, 2021 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.