Hi, "offset 0" in CTE worked. I later used CASE-CLAUSE to avoid using
CTE and worked as well. Cheers!
SELECT ST_GeometryType(geom) gtype, geom FROMtmp_test_geoms WHERECASE WHEN ST_GeometryType(geom) = 'ST_Point' THEN ST_X(geom) < 1 ELSE
FALSE END;
On 9/5/20 11:14 AM, Regina Obe wrote:
Or I forgot to mention, I think the old hack of OFFSET like you have
in 3 still works and for backward compatibility would be preferable
over using the MATERIALIZED keyword, but less clear in intent
So something
WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' OFFSET 0)
SELECT * FROM foo WHERE ST_X(geom) > 1;
*From:*Regina Obe [mailto:[email protected]]
*Sent:* Saturday, September 5, 2020 2:12 PM
*To:* 'PostGIS Users Discussion' <[email protected]>
*Subject:* RE: [postgis-users] Bizarre behavior with postgis function
as part of where clause!
Have you tried adding a MATERIALIZED to your WITH? In PostgreSQL 12
and above, CTEs are no longer always materialized.
e.g.
WITH foo AS MATERIALIZED (SELECT ST_GeometryType(geom) gtype, geom
FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point') SELECT *
FROM foo WHERE ST_X(geom) > 1;
This article describes it a bit
https://paquier.xyz/postgresql-2/postgres-12-with-materialize/
*From:*postgis-users [mailto:[email protected]]
*On Behalf Of *Bo Guo
*Sent:* Friday, September 4, 2020 11:28 PM
*To:* [email protected] <mailto:[email protected]>
*Subject:* [postgis-users] Bizarre behavior with postgis function as
part of where clause!
Hi List,
I just ran into a pretty bizarre query behavior involving simple
queries involving PostGIS functions as part of where clause condition.
I have two geometries in table tmp_test_geoms , one point and a line:
ST_Point "0101000000806FE29BC53B2241800FDDE3E9C53341"
ST_LineString
"010200000005000000E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341"
Both Query 1 and Query 2 fails w/ message "Argument to ST_X() must
have type POINT," Query 3 works however
1. SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms
WHERE ST_GeometryType(geom) = 'ST_Point' AND ST_X(geom) > 1;
2. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point') SELECT * FROM
foo WHERE ST_X(geom) > 1;
3. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' LIMIT 10)
SELECT * FROM foo WHERE ST_X(geom) > 1;
It seems that the query engine evaluates ST_X first before applying
the GeometryType filter, even when the filter is in a CTE.
It further seems that by adding LIMIT with an arbitrary value at the
end of the CTE, the filter is applied first!
Wonder if there is a way to give some type of query hint to force the
evaluation sequences.
Thanks for any suggestions!
Bo
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Regards,
Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com
www.youtube.com/linearbench
Office: 480-656-9962
Cell: 602-570-4697
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
