[Geoserver-devel] Custom Database-backed Filter Functions

GeoServer GeoServer Developer
1

Hi there,

I'd like to use some custom PostgreSQL functions as filters in standard WFS 2.0.0 requests. The goal is to request features filtered by e.g. Full Text Search or the word_similarity function provided by the pg_trgm extension (Trigram/Trigraph support).

Since WFS only provides a quite limited set of operators (PropertyIs[Not]EqualTo, PropertyIsLessThan, PropertyIsLike, etc.), my idea is to use my own filter functions in order to access those custom database functions.

In GeoTools, there is a Function tutorial explaining how to create my own Function Factory and function implementations. However, all samples and functions I've seen so far are implemented in Java. Is it possible for these Filter Functions to contribute to the WHERE clause of the actual SQL statement executed in the database?

For a GeoServer (Data)Store, there is an option "encode functions", causing all/most filters to be implemented in SQL for performance reasons. Does this affect Filter Functions from those Filter Factories as well?

Looking at the Function's interfaces (Expression, ExpressionVisitor, Function), I don't see any point at which SQL could come into play. Am I on the right track?

Any advice is appreciated :slight_smile:

Carsten

2

You are running alongside the right track :slight_smile:

To work you implement in java (for any non PostGIS data) and then adjust the PostGIS DataStore code to map those functions to SQL.

You can see the mapping being done from the GeoTools function data structure (which is why you need a java implementation) to the SQL.

···

–
Jody Garnett

3

Hi Jody,

thanks for your response :slight_smile:

Since I will likely not be able to modify/extend FilterToSqlHelper from a GeoTools Filter Function Plugin, it will be best to focus on PostgreSQL functions that can be encoded as

functionName(p1, p2, … pN)

In that case, PostgisFilterToSQL will translate my Java implemented custom functions to SQL without any further action required? (Given that “encode functions” is enabled.)

I guess, the actual Java implementation is not required to return/evaluate any meaningful results based on the arguments, right? (I won’t be able to re-implement e.g. Full Text Search in Java, of course)

Still on track?

Regards,
Carsten

···

Am 26.09.2023 um 08:31 schrieb Jody Garnett:

You are running alongside the right track :slight_smile:

To work you implement in java (for any non PostGIS data) and then adjust the PostGIS DataStore code to map those functions to SQL.

You can see the mapping being done from the GeoTools function data structure (which is why you need a java implementation) to the SQL.

–
Jody Garnett

On Sep 25, 2023 at 11:23:00 PM, Carsten Klein <c.klein@anonymised.com> wrote:

Hi there,

I’d like to use some custom PostgreSQL functions as filters in standard
WFS 2.0.0 requests. The goal is to request features filtered by e.g.
Full Text Search or the word_similarity function provided by the pg_trgm
extension (Trigram/Trigraph support).

Since WFS only provides a quite limited set of operators
(PropertyIs[Not]EqualTo, PropertyIsLessThan, PropertyIsLike, etc.), my
idea is to use my own filter functions in order to access those custom
database functions.

In GeoTools, there is a Function tutorial explaining how to create my
own Function Factory and function implementations. However, all samples
and functions I’ve seen so far are implemented in Java. Is it possible
for these Filter Functions to contribute to the WHERE clause of the
actual SQL statement executed in the database?

For a GeoServer (Data)Store, there is an option “encode functions”,
causing all/most filters to be implemented in SQL for performance
reasons. Does this affect Filter Functions from those Filter Factories
as well?

Looking at the Function’s interfaces (Expression, ExpressionVisitor,
Function), I don’t see any point at which SQL could come into play. Am I
on the right track?

Any advice is appreciated :slight_smile:

Carsten

Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

4

Sort of on track. First class functions have both a java implementation and a way to be delegated to
the database.

There are however a few functions that have no equivalent and can be run only against a particular database,
like pgNearest:
https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/filter/FilterFunction_pgNearest.java

These functions still have to be recognized as part of the filter capabilities and have custom encoding code:
https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L196

The downside of these is that they make for a broken system, the moment there is more than one backend data source,
or just by the fact of being there.
E.g., pgNearest is always there as postgresql store is always included, but installations might be using Oracle, sql server,
shapefiles, all sources where attempting usage of such function will fail.

So it’s not clean, but also normally not a big deal because the description of functions in WFS capabilities is so poor that clients cannot automatically
use them anyways, clients using functions are usually written by hand anyways.

Cheers
Andrea

···

–
Jody Garnett

Regards,

Andrea Aime

==
GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

5

I managed to add a simple function “add”, which returns the sum of both its Double arguments. The add function follows the GeoTools Function tutorial: it directly implements the Function interface and gets created by my own FunctionFactory, which is registered via META-INF/services/org.geotools.filter.FunctionFactory.

However, only the Java implementation is actually used. GeoTools/GeoServer makes no attempt to encode the “add” function in SQL when using a PostgreSQL store. What am I missing?

Another question: most of the built-in functions do not implement the Function interface directly but extend FunctionExpressionImpl (which implements FunctionExpression). According to the docs, these functions must be registered via META-INF/services/org.geotools.api.filter.Function. So, I added a “sub” function, returning a - b, which is based on the FunctionExpression approach (which seems to be quite a bit more comfortable and newer). Unfortunately, GeoServer/GeoTools seems not to know the new “sub” function and throws an error when I try to use that function through a WFS GetFeature request.

BTW, interface FunctionExpression states:

[…]

  • All implements should be registered for service provider interface
···

Am 26.09.2023 um 09:43 schrieb Andrea Aime:

Sort of on track. First class functions have both a java implementation and a way to be delegated to
the database.

There are however a few functions that have no equivalent and can be run only against a particular database,
like pgNearest:
https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/filter/FilterFunction_pgNearest.java

These functions still have to be recognized as part of the filter capabilities and have custom encoding code:
https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L196

The downside of these is that they make for a broken system, the moment there is more than one backend data source,
or just by the fact of being there.
E.g., pgNearest is always there as postgresql store is always included, but installations might be using Oracle, sql server,
shapefiles, all sources where attempting usage of such function will fail.

So it’s not clean, but also normally not a big deal because the description of functions in WFS capabilities is so poor that clients cannot automatically
use them anyways, clients using functions are usually written by hand anyways.

Cheers
Andrea

On Tue, Sep 26, 2023 at 9:01 AM Carsten Klein <c.klein@anonymised.com> wrote:

Hi Jody,

thanks for your response :slight_smile:

Since I will likely not be able to modify/extend FilterToSqlHelper from a GeoTools Filter Function Plugin, it will be best to focus on PostgreSQL functions that can be encoded as

functionName(p1, p2, … pN)

In that case, PostgisFilterToSQL will translate my Java implemented custom functions to SQL without any further action required? (Given that “encode functions” is enabled.)

I guess, the actual Java implementation is not required to return/evaluate any meaningful results based on the arguments, right? (I won’t be able to re-implement e.g. Full Text Search in Java, of course)

Still on track?

Regards,
Carsten

Am 26.09.2023 um 08:31 schrieb Jody Garnett:

You are running alongside the right track :slight_smile:

To work you implement in java (for any non PostGIS data) and then adjust the PostGIS DataStore code to map those functions to SQL.

You can see the mapping being done from the GeoTools function data structure (which is why you need a java implementation) to the SQL.

–
Jody Garnett

On Sep 25, 2023 at 11:23:00 PM, Carsten Klein <c.klein@anonymised.com> wrote:

Hi there,

I’d like to use some custom PostgreSQL functions as filters in standard
WFS 2.0.0 requests. The goal is to request features filtered by e.g.
Full Text Search or the word_similarity function provided by the pg_trgm
extension (Trigram/Trigraph support).

Since WFS only provides a quite limited set of operators
(PropertyIs[Not]EqualTo, PropertyIsLessThan, PropertyIsLike, etc.), my
idea is to use my own filter functions in order to access those custom
database functions.

In GeoTools, there is a Function tutorial explaining how to create my
own Function Factory and function implementations. However, all samples
and functions I’ve seen so far are implemented in Java. Is it possible
for these Filter Functions to contribute to the WHERE clause of the
actual SQL statement executed in the database?

For a GeoServer (Data)Store, there is an option “encode functions”,
causing all/most filters to be implemented in SQL for performance
reasons. Does this affect Filter Functions from those Filter Factories
as well?

Looking at the Function’s interfaces (Expression, ExpressionVisitor,
Function), I don’t see any point at which SQL could come into play. Am I
on the right track?

Any advice is appreciated :slight_smile:

Carsten

Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–

Regards,

Andrea Aime

== GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead

GeoSolutions Group phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

6

I managed to add a simple function “add”, which returns the sum of both its Double arguments. The add function follows the GeoTools Function tutorial: it directly implements the Function interface and gets created by my own FunctionFactory, which is registered via META-INF/services/org.geotools.filter.FunctionFactory.

However, only the Java implementation is actually used. GeoTools/GeoServer makes no attempt to encode the “add” function in SQL when using a PostgreSQL store. What am I missing?

Because you haven’t modified the database code to advertise it can encode the function, and add the code
to perform the actual encoding. E.g., in the case of PostGIS:

  1. Declaration that functions are supported
    https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L167

  2. Actual function encoding:
    https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L522

Another question: most of the built-in functions do not implement the Function interface directly but extend FunctionExpressionImpl (which implements FunctionExpression). According to the docs, these functions must be registered via META-INF/services/org.geotools.api.filter.Function. So, I added a “sub” function, returning a - b, which is based on the FunctionExpression approach (which seems to be quite a bit more comfortable and newer). Unfortunately, GeoServer/GeoTools seems not to know the new “sub” function and throws an error when I try to use that function through a WFS GetFeature request.

The docs are probably pointing you at the wrong registration file, the functions are registered in “org.geotools.api.filter.expression.Function”:
https://github.com/geotools/geotools/blob/main/modules/library/main/src/main/resources/META-INF/services/org.geotools.api.filter.expression.Function

If you spot issues in the docs please contribute fixes for it.

Cheers
Andrea

···

GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

7

Andrea,

  1. Actual function encoding seems only required if functions cannot be encoded like “functionName(a1, a2, …, aN)”, that is referred to “custom encoding”:
    https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L522

In PostgisFilterToSQL, visit only tries with FilterToSqlHelper.visitFunction and, if it did NOT encode the function in a custom manner, calls forwards to the super class org.geotools.data.jdbc.FilterToSQL, which supports generic function encoding:
https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostgisFilterToSQL.java#L126

Seems like the actual problem is, that my new function’s class is not registered in the PostgreSQL Store’s FilterCapabilities. However, I’m planing a private plugin-based (SPI) solution for this. How could I modify (extend) FilterToSqlHelper.createFilterCapabilities?

Couldn’t there be a more extensible way to declare a SPI provided function “SQL encodable” for a certain Store (e.g. PostGIS)? Maybe a tag interface “SqlEncodable” could do that job?

Cheers
Carsten

···

Am 02.10.2023 um 10:03 schrieb Andrea Aime:

On Mon, Oct 2, 2023 at 9:05 AM Carsten Klein <c.klein@anonymised.com> wrote:

I managed to add a simple function “add”, which returns the sum of both its Double arguments. The add function follows the GeoTools Function tutorial: it directly implements the Function interface and gets created by my own FunctionFactory, which is registered via META-INF/services/org.geotools.filter.FunctionFactory.

However, only the Java implementation is actually used. GeoTools/GeoServer makes no attempt to encode the “add” function in SQL when using a PostgreSQL store. What am I missing?

Because you haven’t modified the database code to advertise it can encode the function, and add the code
to perform the actual encoding. E.g., in the case of PostGIS:

  1. Declaration that functions are supported
    https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L167

  2. Actual function encoding:
    https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L522

Another question: most of the built-in functions do not implement the Function interface directly but extend FunctionExpressionImpl (which implements FunctionExpression). According to the docs, these functions must be registered via META-INF/services/org.geotools.api.filter.Function. So, I added a “sub” function, returning a - b, which is based on the FunctionExpression approach (which seems to be quite a bit more comfortable and newer). Unfortunately, GeoServer/GeoTools seems not to know the new “sub” function and throws an error when I try to use that function through a WFS GetFeature request.

The docs are probably pointing you at the wrong registration file, the functions are registered in “org.geotools.api.filter.expression.Function”:
https://github.com/geotools/geotools/blob/main/modules/library/main/src/main/resources/META-INF/services/org.geotools.api.filter.expression.Function

If you spot issues in the docs please contribute fixes for it.

Cheers
Andrea

==

GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead

GeoSolutions Group phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

8

Seems like the actual problem is, that my new function’s class is not registered in the PostgreSQL Store’s FilterCapabilities. However, I’m planing a private plugin-based (SPI) solution for this. How could I modify (extend) FilterToSqlHelper.createFilterCapabilities?

There is nothing planned to support this functionality. We are more interested in nudging people to contribute
open code to benefit everyone.
Unless you’re going to write functions that are very specific to your case, and would not be of interest to the larger community?

Couldn’t there be a more extensible way to declare a SPI provided function “SQL encodable” for a certain Store (e.g. PostGIS)? Maybe a tag interface “SqlEncodable” could do that job?

No, it does not make sense… a filter may be sql encodable in one database, but not in another, and a function is available
for the whole system. I doubt there are many functions that are sql encodable in all databases we support.

It seems you need something that is specific to your own use case, and also specific to one database: probably the most appropriate
path is for you to create and register a new data store factory, which ends up subclassing PostGISDialect and return a different
FilterToSQL implementation (likely a subclass of the existing one).

Or maybe we could have some plugin (a new SPI) that can add entries to the capabilities document, given a specific database.

Jody, any other ideas?

Cheers
Andrea

···

GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

9

Andrea,

There is nothing planned to support this functionality. We are more interested in nudging people to contribute
open code to benefit everyone.
Unless you’re going to write functions that are very specific to your case, and would not be of interest to the larger community?

Maybe I didn’t explain that correctly. Basically, I’m keen to contribute open code so that everyone could benefit from it. However, in this case, I only need use two project-specific PostgreSQL functions in WFS queries. Likely I’m the only person who needs these. Saying:

“However, I’m planing a private plugin-based (SPI) solution for this.”

just means, that I’d like to add a private (my own) GeoTools plugin jar to GeoServer’s lib dir, that contains these two extra functions. GeoTools’ SPI approach makes this quite easy. However, it turns out, that these functions get not SQL encoded, since these functions’ classes are not contained in the functions Set of the Store’s FilterCapabilities. (Actually, the query is split into a pre- and and post-filter in the JDBCDataStore.)

Indeed, it would be cool if one can mark a function being “SQL encodable” with the same easiness than publishing this function. That would be something, others could benefit from as well…

[…]

Or maybe we could have some plugin (a new SPI) that can add entries to the capabilities document, given a specific database.

You mean the FilterCapabilies of a specific store? That would be quite helpful.

Cheers
Carsten

10

Another idea:

Couldn’t there be a more extensible way to declare a SPI provided function “SQL encodable” for a certain Store (e.g. PostGIS)? Maybe a tag interface “SqlEncodable” could do that job?

No, it does not make sense… a filter may be sql encodable in one database, but not in another, and a function is available
for the whole system. I doubt there are many functions that are sql encodable in all databases we support.

Why not using N tag interfaces, one per DataStore?

You could define tag interface org.geotools.data.postgis.PostgisSQLEncodable in plugin jdbc-postgres. This type could unconditionally be added to FilterCapabilities in createFilterCapabilities in class FilterToSqlHelper:

caps.addType(PostgisSQLEncodable.class);

Implementors of plugin-defined filter functions could then implement the tag interface(s) that corresponds to the desired DataStore(s) (if available). Of course, that mechanism is not ideal for built-in functions, which aim to be generally available (aka work with DataStores). But, this is likely much simpler than implementing another SPI…

You can repeat the above steps e. g. for jdbc-oracle, jdbc-mysql, etc. But that’s not a MUST - such a tag interface is not required to be available for all DataStore implementations (for me, supporting PostGIS will be sufficient g). But for the sake of completeness, most (major) DataSores should get their tag interface, of course.

Cheers
Carsten

11

Hi Andrea,
hi Jody,

any progress here? For me, the interface-based approach seems quite simple and is likely easy to implement. What do you think about it? Would be great if we could get things moving…

Cheers
Carsten

12

Hi Carsten,
I’m skeptical, the idea would not be applicable to existing GeoTools functions as
it would end up introducing circularities in the maven module dependencies.
The PostGIS interface would be found in gt-jdbc-postigs, but most existing functions
are found in gt-main. And if we wanted to have a function translatable in multiple
databases, the code would have to depend on all the associated gt-jdbc- modules.
A mechanism that can only be used only downstreams, but not by the library itself,
does not seem very interesting to me.

Also, I’m not sure there is a simple way to check if a function implements a given interface,
what we can get from FunctionFinder is a list of FunctionName, and in order to instantiate
a particular function, one has to pass an actual list of arguments. Did you think about how you’d
like to actually implement the lookup?
I can see it working with CommonFactoryFinder.getFunctions, which would limit the mechanism
to the single function SPI, rather than being more comprehensive and cover the FunctionFactory
mechanism (which is the original one, the single Function one has been added afterward as a
convenience).

Gut feeling, maybe it would be better to use a common annotation instead, receiving multiple
strings, which would be the “dbtype” of the database where it can be encoded without any
translation. This would avoid the circularities. It would still be limited to standalone functions API,
but I don’t see much of a way around that, without somehow extending FunctionName to support
JDBC encoding (which sounds like it would be another bad idea).

Jody, any other suggestion?

Cheers
Andrea

···

Regards,

Andrea Aime

==
GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

13

Hi Andrea,

see my comments inline:

Cheers
Carsten

That was meant as an extension point for user-provided filter functions. Not as a replacement mechanism to make existing filter functions SQL-encodable. Such custom filter functions typically are implemented only on one certain database (e.g. PostGIS or Oracle, but not both) so, a provider of such a function only has to reference gt-main and e. g. gt-jdbc-postgis OR gt-jdbc-oracle (but not all of them). However, a common solution for both internal existing and user-provided filter functions is actually charming… Maybe the annotation approach is the best idea for that. On the other side, that is a lot of work to be done. Actually, that’s quite simple and works with the org.geotools.filter.FilterCapabilities (which already does an isAssignableFrom in its supports method). As you can see in class for PostGIS, each SQL-encodable function’s class is added to the created FilterCapabilities ‘caps’ through the addType method. In lines 207 and 208 you can see, that the NativeFilter interface is also added. If the newly (maybe dbytpe-specific) interface is added here as well, everything should work just out of the box. Putting all together: yes, this is just for downstreams and it’s not the answer to everything. But, besides the new interfaces (one per dbtype) it requires only one line of code (plus a comment line, if you like) per dbtype. For me, this seems quite cheap, even if it’s a feature for externally defined filter functions only.

···

Am 24.10.2023 um 15:30 schrieb Andrea Aime:

Hi Carsten,
I’m skeptical, the idea would not be applicable to existing GeoTools functions as
it would end up introducing circularities in the maven module dependencies.
The PostGIS interface would be found in gt-jdbc-postigs, but most existing functions
are found in gt-main. And if we wanted to have a function translatable in multiple
databases, the code would have to depend on all the associated gt-jdbc- modules.
A mechanism that can only be used only downstreams, but not by the library itself,
does not seem very interesting to me.

Also, I’m not sure there is a simple way to check if a function implements a given interface,
what we can get from FunctionFinder is a list of FunctionName, and in order to instantiate
a particular function, one has to pass an actual list of arguments. Did you think about how you’d
like to actually implement the lookup?

https://github.com/geotools/geotools/blob/main/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/FilterToSqlHelper.java#L134

I can see it working with CommonFactoryFinder.getFunctions, which would limit the mechanism
to the single function SPI, rather than being more comprehensive and cover the FunctionFactory
mechanism (which is the original one, the single Function one has been added afterward as a
convenience).

Gut feeling, maybe it would be better to use a common annotation instead, receiving multiple
strings, which would be the “dbtype” of the database where it can be encoded without any
translation. This would avoid the circularities. It would still be limited to standalone functions API,
but I don’t see much of a way around that, without somehow extending FunctionName to support
JDBC encoding (which sounds like it would be another bad idea).

Jody, any other suggestion?

Cheers
Andrea

On Tue, Oct 24, 2023 at 8:10 AM Carsten Klein <c.klein@anonymised.com> wrote:

Hi Andrea,
hi Jody,

any progress here? For me, the interface-based approach seems quite simple and is likely easy to implement. What do you think about it? Would be great if we could get things moving…

Cheers
Carsten

–

Regards,

Andrea Aime

== GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead

GeoSolutions Group phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

14

Yes, I remember NativeFilter, there was some discussion about whether to allow
that interface to exist at all, the final agreement was that, yes, ok, we can have it,
as long as it’s a low level programmer oriented tool, which cannot be seen by end users.

And we also have a similar interface for functions called InternalFunction, which has a similar
attitude, a function that is not meant to be seen outside of code
(cannot be parsed from CQL or Filter XML encoding for example)… although this one has
no usage in SQL encoding.

Perhaps a NativeFunction base interface could be used, to indicate that the function
is not meant to be included in capabilities documents (odd usage, not working cross store),
and per store sub-interfaces like PostgisNativeFunction could be used to indicate the
function can be written as is in PostGIS for example.

I still don’t like it much in general, as its usage would be limited to vendor extensions, but
I’m not going to cast a -1 either, as I see the convenience of the approach for custom
GeoServer extensions.
I’ll let the other core developers chime in and help with a decision.

Cheers
Andrea

···

Regards,

Andrea Aime

== GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead

GeoSolutions Group phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

Regards,

Andrea Aime

==
GeoServer Professional Services from the experts!

Visit http://bit.ly/gs-services-us for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

https://www.geosolutionsgroup.com/

http://twitter.com/geosolutions_it

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail

15

Hi Andrea,

what's next with the SQL-encodable filter functions. You said

I'll let the other core developers chime in and help with a decision.

Is there anything new? Do you expect me to issue a Jira CR? Or even a ready to use patch?

I'd much appreciate if someone of the core devs could make these (simple) changes, since I have not yet any experiences to hack GeoTools (and test with GeoServer). Setting up all this is perhaps much more effort than the patch itself (which a coder used to work with both projects will likely have finished in some minutes).

Cheers
Carsten

Am 25.10.2023 um 00:52 schrieb Andrea Aime:

Yes, I remember NativeFilter, there was some discussion about whether to allow
that interface to exist at all, the final agreement was that, yes, ok, we can have it,
as long as it's a low level programmer oriented tool, which cannot be seen by end users.

And we also have a similar interface for functions called InternalFunction, which has a similar
attitude, a function that is not meant to be seen outside of code
(cannot be parsed from CQL or Filter XML encoding for example)... although this one has
no usage in SQL encoding.

Perhaps a NativeFunction base interface could be used, to indicate that the function
is not meant to be included in capabilities documents (odd usage, not working cross store),
and per store sub-interfaces like PostgisNativeFunction could be used to indicate the
function can be written as is in PostGIS for example.

I still don't like it much in general, as its usage would be limited to vendor extensions, but
I'm not going to cast a -1 either, as I see the convenience of the approach for custom
GeoServer extensions.
I'll let the other core developers chime in and help with a decision.

Cheers
Andrea

16

I think this needs a geotools proposal to gather the results of this thread into a form for folks to review.

While I could review this thread in order to chime in, it would really benefit from a proposal describing proposed API / functionality change.

This is a geotools level change right?

···

–
Jody Garnett

17

Hi Jody,

yes, the change must be applied in GeoTools. Shall I write such a proposal as a New Feature (or Wish) in Jira for the GeoTools (GEOT) project?

···

Am 07.12.2023 um 17:38 schrieb Jody Garnett:

I think this needs a geotools proposal to gather the results of this thread into a form for folks to review.

While I could review this thread in order to chime in, it would really benefit from a proposal describing proposed API / functionality change.

This is a geotools level change right?

–
Jody Garnett

On Dec 4, 2023 at 10:06:08 PM, Carsten Klein <c.klein@anonymised.com> wrote:

Hi Andrea,

what’s next with the SQL-encodable filter functions. You said

I’ll let the other core developers chime in and help with a decision.

Is there anything new? Do you expect me to issue a Jira CR? Or even a
ready to use patch?

I’d much appreciate if someone of the core devs could make these
(simple) changes, since I have not yet any experiences to hack GeoTools
(and test with GeoServer). Setting up all this is perhaps much more
effort than the patch itself (which a coder used to work with both
projects will likely have finished in some minutes).

Cheers
Carsten

Am 25.10.2023 um 00:52 schrieb Andrea Aime:

Yes, I remember NativeFilter, there was some discussion about whether

to allow

that interface to exist at all, the final agreement was that, yes, ok,

we can have it,

as long as it’s a low level programmer oriented tool, which cannot be

seen by end users.

And we also have a similar interface for functions called

InternalFunction, which has a similar

attitude, a function that is not meant to be seen outside of code

(cannot be parsed from CQL or Filter XML encoding for example)…

although this one has

no usage in SQL encoding.

Perhaps a NativeFunction base interface could be used, to indicate

that the function

is not meant to be included in capabilities documents (odd usage, not

working cross store),

and per store sub-interfaces like PostgisNativeFunction could be used

to indicate the

function can be written as is in PostGIS for example.

I still don’t like it much in general, as its usage would be limited

to vendor extensions, but

I’m not going to cast a -1 either, as I see the convenience of the

approach for custom

GeoServer extensions.

I’ll let the other core developers chime in and help with a decision.

Cheers

Andrea

18

Proposals are in the wiki here:
https://github.com/geotools/geotools/wiki/Proposals

Idea is to get buy in before putting a bunch of work into a change. And collect alternatives is there is feedback.

Jody

···

–
Jody Garnett

19

OK, will place a proposal there. Give me some time. Maybe I’ll take the weekend to get it done…

Interestingly, I do not find these mails on the geotools-devel archive at https://sourceforge.net/p/geotools/mailman/geotools-devel/

Would have used those under “References”. Is there any other (mirror) archive available?

Carsten

···

Am 07.12.2023 um 23:51 schrieb Jody Garnett:

Proposals are in the wiki here:
https://github.com/geotools/geotools/wiki/Proposals

Idea is to get buy in before putting a bunch of work into a change. And collect alternatives is there is feedback.

Jody

On Thu, Dec 7, 2023 at 8:50 AM Carsten Klein <c.klein@anonymised.com> wrote:

Hi Jody,

yes, the change must be applied in GeoTools. Shall I write such a proposal as a New Feature (or Wish) in Jira for the GeoTools (GEOT) project?

Carsten Klein

Am 07.12.2023 um 17:38 schrieb Jody Garnett:

I think this needs a geotools proposal to gather the results of this thread into a form for folks to review.

While I could review this thread in order to chime in, it would really benefit from a proposal describing proposed API / functionality change.

This is a geotools level change right?

–
Jody Garnett

On Dec 4, 2023 at 10:06:08 PM, Carsten Klein <c.klein@anonymised.com> wrote:

Hi Andrea,

what’s next with the SQL-encodable filter functions. You said

I’ll let the other core developers chime in and help with a decision.

Is there anything new? Do you expect me to issue a Jira CR? Or even a
ready to use patch?

I’d much appreciate if someone of the core devs could make these
(simple) changes, since I have not yet any experiences to hack GeoTools
(and test with GeoServer). Setting up all this is perhaps much more
effort than the patch itself (which a coder used to work with both
projects will likely have finished in some minutes).

Cheers
Carsten

Am 25.10.2023 um 00:52 schrieb Andrea Aime:

Yes, I remember NativeFilter, there was some discussion about whether

to allow

that interface to exist at all, the final agreement was that, yes, ok,

we can have it,

as long as it’s a low level programmer oriented tool, which cannot be

seen by end users.

And we also have a similar interface for functions called

InternalFunction, which has a similar

attitude, a function that is not meant to be seen outside of code

(cannot be parsed from CQL or Filter XML encoding for example)…

although this one has

no usage in SQL encoding.

Perhaps a NativeFunction base interface could be used, to indicate

that the function

is not meant to be included in capabilities documents (odd usage, not

working cross store),

and per store sub-interfaces like PostgisNativeFunction could be used

to indicate the

function can be written as is in PostGIS for example.

I still don’t like it much in general, as its usage would be limited

to vendor extensions, but

I’m not going to cast a -1 either, as I see the convenience of the

approach for custom

GeoServer extensions.

I’ll let the other core developers chime in and help with a decision.

Cheers

Andrea

20

You can find the email on the geoserver-devel archive: https://sourceforge.net/p/geoserver/mailman/geoserver-devel/?viewmonth=202312&style=threaded

···

–
Jody Garnett

–
Jody Garnett