[postgis-users] Bizarre behavior with postgis function as part of where clause!

[Martin Davis]

What this suggests to me is the utility of making spatial functions have as
broad a domain of geometry input types as possible (ideally, accepting ALL
geometry types as input, and returning reasonable values, whatever those
might be.  That would mean this query would just work, without
requiring any complicated SQL circumlocutions to tweak the query
plan.  Even if the return value is perhaps not that useful in itself, at
least it lets the user execute their own logic to filter the results (as in
this case).

Here the generalization is obvious - ST_X can simply return the X ordinate
of the first coordinate in the geometry, of any type.


On Fri, Sep 4, 2020 at 8:27 PM Bo Guo <bo.guo at gisticinc.com> wrote:

> 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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200905/db5cf523/attachment.html>