<div dir="ltr">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).<div><br></div><div>Here the generalization is obvious - ST_X can simply return the X ordinate of the first coordinate in the geometry, of any type. </div><div><br></div><div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Sep 4, 2020 at 8:27 PM Bo Guo <<a href="mailto:bo.guo@gisticinc.com">bo.guo@gisticinc.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>Hi List,</p>
<p>I just ran into a pretty bizarre query behavior involving simple
queries involving PostGIS functions as part of where clause
condition. <br>
</p>
<p>I have two geometries in table tmp_test_geoms , one point and a
line: <br>
</p>
<tt> ST_Point "0101000000806FE29BC53B2241800FDDE3E9C53341"</tt><tt><br>
</tt><tt> ST_LineString
"010200000005000000E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341"</tt><br>
<p>Both Query 1 and Query 2 fails w/ message "Argument to ST_X()
must have type POINT," Query 3 works however<br>
</p>
<p><tt> 1. SELECT ST_GeometryType(geom) gtype, geom FROM
tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' AND
ST_X(geom) > 1;</tt><tt><br>
</tt><tt><br>
</tt><tt> 2. WITH foo AS (SELECT ST_GeometryType(geom) gtype,
geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) =
'ST_Point')</tt><tt> SELECT * FROM foo WHERE ST_X(geom) > 1;</tt><tt><br>
</tt><tt> 3. WITH foo AS (SELECT ST_GeometryType(geom) gtype,
geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) =
'ST_Point' </tt><font size="+2"><tt>LIMIT 10</tt></font><tt>)</tt><tt>
SELECT * FROM foo WHERE ST_X(geom) > 1;</tt><br>
</p>
<p>It seems that the query engine evaluates ST_X first before
applying the GeometryType filter, even when the filter is in a
CTE. <br>
</p>
<p>It further seems that by adding LIMIT with an arbitrary value at
the end of the CTE, the filter is applied first!</p>
<p>Wonder if there is a way to give some type of query hint to force
the evaluation sequences.</p>
<p>Thanks for any suggestions!<br>
</p>
<p>Bo<br>
</p>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>