4

I have a table with column "geom" defined as type geometry.

Table "public.permanent_feature"
     Column     |           Type           |
----------------+--------------------------+
 id             | bigint                   |
 attribute      | integer                  |
 label          | text                     |
 notes          | text                     |
 geom           | geometry(Geometry,4258)  |
 last_edited    | timestamp with time zone |
 last_edited_by | text                     |

I then have 3 views that split this table up into points, lines and polygons for loading into QGIS. Here's one:

View "public.permanent_feature_point"
     Column     |         Type         |
----------------+----------------------+
 id             | bigint               |
 category       | text                 |
 feat_type      | text                 |
 attribute_name | text                 |
 notes          | text                 |
 geom           | geometry(Point,4258) |
 attribute      | integer              |
View definition:
 SELECT permanent_feature.id,
    lft2.name AS category,
    lft.name AS feat_type,
    lf.name AS attribute_name,
    permanent_feature.notes,
    permanent_feature.geom::geometry(Point,4258) AS geom,
    permanent_feature.attribute
   FROM permanent_feature
     JOIN lookup_feature lf ON permanent_feature.attribute = lf.id
     JOIN lookup_feature_type lft ON lf.feature_type = lft.id
     JOIN lookup_feature_type lft2 ON lft.parent = lft2.id
  WHERE st_geometrytype(permanent_feature.geom) = 'ST_Point'::text;

These views query perfectly from psql

If I don't cast the geom column to point/line/polygon then QGIS doesn't know what type an empty table is. If I do cast the geom column then QGIS produces an error when trying to query the table, empty or otherwise.

I've tracked this down to QGIS using "&&" to query points in the extent of the view window, and indeed when you use "&&" in a query in PSQL I get the same error.

gis_live=# SELECT * FROM "public"."permanent_feature_point" WHERE "geom" && st_makeenvelope(-31.89768008516934827,-11.68848783403789149,36.48516392420116716,16.36601227237052569,4258);
ERROR:  Geometry type (LineString) does not match column type (Point)

It's producing this error because the table does indeed contain LineStrings, but they should be filtered out by the view.

The issue appears to be the due to the order of operations. If I use st_within rather than &&, it works properly - of course I can't change QGIS to use st_within.

"&&" (doesn't work):

gis_live=# EXPLAIN SELECT * FROM "public"."permanent_feature_point" WHERE "geom" && st_makeenvelope(-31.89768008516934827,-11.68848783403789149,36.48516392420116716,16.36601227237052569,4258);
 -- QUERY PLAN
-> Nested Loop  (cost=0.45..10.26 rows=1 width=172)
   ->  Nested Loop  (cost=0.30..10.02 rows=1 width=161)
         ->  Nested Loop  (cost=0.15..9.79 rows=1 width=129)
               ->  Seq Scan on permanent_feature  (cost=0.00..1.60 rows=1 width=93)
                     Filter: (((geom)::geometry(Point,4258) && '0106...027C0'::geometry) AND (st_geometrytype(geom) = 'ST_Point'::text)) 
               ->  Index Scan using lookup_feature_pkey on lookup_feature lf  (cost=0.15..8.17 rows=1 width=40)
                     Index Cond: (id = permanent_feature.attribute)
         ->  Index Scan using lookup_feature_type_pkey on lookup_feature_type lft  (cost=0.15..0.23 rows=1 width=40)
               Index Cond: (id = lf.feature_type)
   ->  Index Scan using lookup_feature_type_pkey on lookup_feature_type lft2  (cost=0.15..0.23 rows=1 width=36)
         Index Cond: (id = lft.parent)
(11 rows)

"st_within" (which works):

gis_live=# EXPLAIN SELECT * FROM "public"."permanent_feature_point" WHERE st_within("geom",st_makeenvelope(-31.89768008516934827,-11.68848783403789149,36.48516392420116716,16.36601227237052569,4258));

-> Nested Loop  (cost=0.45..760.18 rows=1 width=172)
   ->  Nested Loop  (cost=0.30..759.95 rows=1 width=161)
         ->  Nested Loop  (cost=0.15..759.71 rows=1 width=129)
               ->  Seq Scan on permanent_feature  (cost=0.00..751.53 rows=1 width=93)
                     Filter: ((st_geometrytype(geom) = 'ST_Point'::text) AND st_within((geom)::geometry(Point,4258), '0103...6027C0'::geometry))
               ->  Index Scan using lookup_feature_pkey on lookup_feature lf  (cost=0.15..8.17 rows=1 width=40)
                     Index Cond: (id = permanent_feature.attribute)
         ->  Index Scan using lookup_feature_type_pkey on lookup_feature_type lft  (cost=0.15..0.23 rows=1 width=40)
               Index Cond: (id = lf.feature_type)
   ->  Index Scan using lookup_feature_type_pkey on lookup_feature_type lft2  (cost=0.15..0.23 rows=1 width=36)
         Index Cond: (id = lft.parent)
(11 rows)

Is there any way to change this order of operations?

A CTE doesn't work, CASE rather than "=" in the view doesn't work.

Any ideas?

Update:

I'd call the following query broadly similar using no PostGIS. Postgres figures out the correct order to apply the filters.

EXPLAIN ANALYZE 
    WITH test_table AS (
        SELECT * FROM (values ('123','number'), ('abc','not')) AS t(num,type) 
    ) 
    SELECT num::int 
    FROM test_table 
    WHERE num::int > 10 AND type = 'number';
 
 Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
   Filter: ((column2 = 'number'::text) AND ((column1)::integer > 10))
   Rows Removed by Filter: 1

Update:

I'm loading these views into QGIS using PyQGIS and so, I've solved this for my use case by removing the type definition from the VIEW and specifying the geometry type in the URI.

Like so:

uri = QgsDataSourceUri()
uri.setConnection(<details>)
uri.setWkbType(QgsWkbTypes.Point)
vlayer = QgsVectorLayer(uri.uri(False), "Layer Name", "postgres")
QgsProject.instance().addMapLayer(vlayer)

This doesn't help if you want to load the layer using the QGIS GUI though.

5
  • 1
    You could add an Aggregate function to the View - PG won't propagate a filter condition into a sub-query/*View* then. But personally, I'd generally prefer three physical tables if my application is geometry type dependent like QGIS, and whenever I need a plain union of them, have a View for that in place.
    – geozelot
    Commented Mar 24, 2022 at 21:01
  • I was trying to keep it to one physical table for ease of management of permissions, and triggers etc. - perhaps that's an exercise in futility though. Commented Mar 25, 2022 at 8:16
  • Well, it absolutely is an anti-pattern to split tables by attribute - but with geometries there do surface these edge cases at times. This here is an interesting one, too.
    – geozelot
    Commented Mar 25, 2022 at 8:55
  • 1
    I see two options: 1) have a geometry column for each geometry type, a GIST index WHERE <col> IS NOT NULL on each, and a respective reference to those columns in the Views 2) have three geometry only tables, related to the main table by PK/FK, and a respective JOIN in the Views - both suffer from slightly more annoying maintenance, with the second option being more RDB idiomatic.
    – geozelot
    Commented Mar 25, 2022 at 12:06
  • Thanks for the ideas. I've managed to work around the issue for my use case (in my update). Commented Mar 25, 2022 at 14:29

1 Answer 1

3

If your table is rather small and you don't mind loosing the spatial index on the view, you can force the type filtering to occur first by materializing the query

CREATE OR REPLACE VIEW permanent_feature_point AS
    WITH filtered AS MATERIALIZED (   
      SELECT permanent_feature.id,
        lft2.name AS category,
        lft.name AS feat_type,
        lf.name AS attribute_name,
        permanent_feature.notes,
        permanent_feature.geom::geometry(Point,4258) AS geom,
        permanent_feature.attribute    
      FROM permanent_feature
         JOIN lookup_feature lf ON permanent_feature.attribute = lf.id
         JOIN lookup_feature_type lft ON lf.feature_type = lft.id
         JOIN lookup_feature_type lft2 ON lft.parent = lft2.id   
      WHERE st_geometrytype(permanent_feature.geom) = 'ST_Point'::text 
    ) 
SELECT * FROM filtered;

PS: you may have to reapply the cast instead of the last select *

3
  • It probably will become large so the spatial index will be important, and the data will be live so refreshing the materialized view would be burdensome. Commented Mar 25, 2022 at 8:17
  • 1
    @AlanAinsworth just to clarify, it is not a materialized view (that needs to be refreshed) but a regular view containing a materialized CTE (that won't be inlined, guaranteeing that it will be evaluated first and that other part of the query referencing it will access only the in-memory result of the CTE). There is no refresh involved and the data is always read from the underlying tables
    – JGH
    Commented Mar 25, 2022 at 12:08
  • Thanks for the clarification, maybe this is the way to go if you don't mind losing the spatial index, but I've managed to work around the issue for my use case (see my update). Commented Mar 25, 2022 at 14:31

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.