Intersects error on filtered and casted geometry type

[PELOU]

Je ne pense pas être le destinataire de ce message

Le ven. 7 févr. 2025, 19:16, Regina Obe <lr at pcorp.us> a écrit :

> > Hello,
> >
> > Bellow my case and the steps to reproduce :
> >
> > /* About my database */
> > SELECT VERSION();
> > -- PostgreSQL 16.4, compiled by Visual C++ build 1941, 64-bit SELECT
> > POSTGIS_VERSION();
> > -- 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> >
> > /* Create a table with a generic geometry column with french CC48 3948
> SRID */
> > CREATE TEMP TABLE temp_geometry_tab (
> >     id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
> >     geom GEOMETRY(GEOMETRY, 3948)
> > );
> > -- CREATE TABLE
> > -- Query returned successfully in 117 msec.
> >
> > /* Add two rows, one with a point geometry and another with multipolygon
> */
> > INSERT INTO temp_geometry_tab (geom) VALUES ('POINT (2049346
> > 7276066)'), ('MULTIPOLYGON (((2049302 7274650,2050712
> > 7276342,2051981 7276013,2050865 7275954,2049795 7274791,2049948
> > 7274050,2049302 7274650)))') ;
> > -- INSERT 0 2
> > -- Query returned successfully in 71 msec.
> >
> > /* Create a view to filter the point geometries and cast them into points
> */
> > CREATE TEMP VIEW temp_geometry_view AS SELECT
> >     id,
> >     geom::GEOMETRY(POINT, 3948)
> > FROM temp_geometry_tab
> > WHERE ST_GeometryType(geom) IN ('ST_Point') ;
> > -- CREATE VIEW
> > -- Query returned successfully in 83 msec.
> >
> > /* When I try to intersect the view geometry, an error occurs as if I
> query the
> > base table */ SELECT id FROM temp_geometry_view WHERE geom &&
> > ST_MakeEnvelope(2041534,7271489,2059551,7281479,3948)
> > ;
> > -- ERROR:  Geometry type (MultiPolygon) does not match column type
> (Point)
> > -- SQL state: 22023
> >
> > /* When I try to query the first view with and additional filter it works
> */ SELECT
> >     id,
> >     geom::GEOMETRY(POINT, 3948)
> > FROM temp_geometry_tab
> > WHERE
> >     ST_GeometryType(geom) IN ('ST_Point')
> >     AND geom &&
> > ST_MakeEnvelope(2041534,7271489,2059551,7281479,3948)
> > ;
> > -- id: 1 | geom:
> > 01010000206C0F00000000000042453F410000008088C15B41
> >
> >
> > Is-it normal that the filter consider the whole base table with
> multipolygon
> > geometries while only point type geometries are filtered in the queried
> view?
> >
> > Regards,
> > Julien Monticolo
> >
> I think in the past it might not have been possible but I don't think there
> is a guarantee that the WHERE clause always gets checked before the SELECT
> part is applied.
>
> I suppose you could get around it, not nice by doing this
>
>  CREATE TEMP VIEW temp_geometry_view AS SELECT
>      id,
>     CASE WHEN  ST_GeometryType(geom) IN ('ST_Point') THEN geom ELSE NULL
> END::GEOMETRY(POINT, 3948)
>  FROM temp_geometry_tab
>  WHERE ST_GeometryType(geom) IN ('ST_Point') ;
>
> I think adding in a pointless OFFSET also forces WHERE clause to be
> processed first, though that is a very oldish hack too
>
> CREATE TEMP VIEW temp_geometry_view AS SELECT
>      id,
>     geom::GEOMETRY(POINT, 3948)
>  FROM temp_geometry_tab
>  WHERE ST_GeometryType(geom) IN ('ST_Point')
> OFFSET 0;
>
> I think also using a CHECK option on the view might work though that
> designed to prevent updates that would prevent a record from being filtered
> out of the view by updating the condition that makes it a member and not
> for
> this situation:
>
> Something like
>
>
>  CREATE TEMP VIEW temp_geometry_view AS SELECT
>      id,
>    geom::GEOMETRY(POINT, 3948)
>  FROM temp_geometry_tab
>  WHERE ST_GeometryType(geom) IN ('ST_Point')
> WITH CHECK OPTION ;
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250208/040da116/attachment.htm>