INTERSECTS cql2-text with WKT polygon

[grigoroffbenj]

Hi,
I try to make a filter with:

• INTERSECTS(geometry,POLYGON((xx, yy.....)))
  or
• or geometry INTERSECTS POLYGON((xx, yy...))

API respond:
{ "detail": "Unexpected token Token('$END', '') at line 1, column 131.\nExpected one of: \n\t* LESSTHAN\n\t* BETWEEN\n\t* T_CONTAINS\n\t* T_DURING\n\t* T_METBY\n\t* EQ\n\t* __ANON_2\n\t* LTE\n\t* T_ENDS\n\t* GTE\n\t* T_EQUALS\n\t* T_INTERSECTS\n\t* EQUAL\n\t* __ANON_0\n\t* IN\n\t* GT\n\t* IS\n\t* T_OVERLAPS\n\t* T_BEFORE\n\t* LIKE\n\t* T_BEGUNBY\n\t* T_ENDEDBY\n\t* T_AFTER\n\t* T_BEGINS\n\t* T_MEETS\n\t* LT\n\t* T_OVERLAPPEDBY\n\t* __ANON_3\n\t* MORETHAN\n\t* NE\n\t* __ANON_1\n" }

so it's not working.
In pygeofilter it says : "Soon: [CQL Text as defined in OGC API - Features - Part 3: Filtering and the Common Query Language (CQL)]"

So we can't for the moment filter our query by ST_INTERSECT function ?

thanks in advance for your response

[grigoroffbenj]

My bad ! I find exemple in test_items.py.

"/collections/public.landsat_wrs/items?filter-lang=cql2-text&filter=S_INTERSECTS(geom, POLYGON((-22.2153 79.6888,-22.2153 81.8555,-8.97407 81.8555,-8.97407 79.6888,-22.2153 79.6888)))"

but now if i but:

S_INTERSECTS(geography, POLYGON((XX, XX....)))

I have API response:

{ "detail": "function st_transform(unknown, integer) is not unique\nHINT: Could not choose a best candidate function. You might need to add explicit type casts." }

But my geography column have cast "geography":
geography: Any = Field( default=None, sa_column=Column(Geometry(geometry_type="POINT", srid=4326)) )

all my data is in 4326.

anyone can help me ?

[giorgiobasile]

I'm running into the same issue, and I believe the reason is that old versions of PostGIS - i.e., the 3.3 I'm currently using - for some reasons are unable to do implicit cast of EWKT strings to geometries, passed as argument to ST_Transform(geometry, integer) in any query resulting from a CQL intersects filter.
Whereas, the CI of the project runs on PostGIS 3.5 and that's why tests don't fail.
Basically:

• PostGIS 3.5:

test_db=# select ST_Transform('SRID=4326;POLYGON ((-22.2153 79.6888, -22.2153 81.8555, -8.97407 81.8555, -8.97407 79.6888, -22.2153 79.6888))', 4326);
             st_transform
------------------------------------------
 0103000020E6100000010000000500000....
(1 row)

• PostGIS 3.3:

neondb=> select ST_Transform('SRID=4326;POLYGON ((-22.2153 79.6888, -22.2153 81.8555, -8.97407 81.8555, -8.97407 79.6888, -22.2153 79.6888))', 4326);
ERROR:  function st_transform(unknown, integer) is not unique
LINE 1: select ST_Transform('SRID=4326;POLYGON ((-22.2153 79.6888, -...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

neondb=> select ST_Transform('SRID=4326;POLYGON ((-22.2153 79.6888, -22.2153 81.8555, -8.97407 81.8555, -8.97407 79.6888, -22.2153 79.6888))'::geometry, 4326);
             st_transform
------------------------------------------
 0103000020E6100000010000000500000....
(1 row)

So the solution seems to be to either upgrade PostGIS, if possible, or modify TiPG's filtering operators to cast to geometry explicitly through ::geometry or ST_GeomFromEWKT. You can work this around by writing in your app something like:

from tipg.filter.filters import Operator

Operator.OPERATORS["INTERSECTS"] = lambda f, a: Func(
    "st_intersects",
    f,
    Func("st_transform", Func("st_geomfromewkt", a), Func("st_srid", f)),
)