How to extend _SQLITE_FUNCTIONS list?

[shenzhuxi]

I found that there are many common functions like MakeLine, MakePoint and etc. don't have ST_ prefix in spatialite.
https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

I wonder if there is any easy way to extend the _SQLITE_FUNCTIONS list?

geoalchemy2/geoalchemy2/functions.py

Line 235 in b107df9

_SQLITE_FUNCTIONS = {

[adrien-berchet]

Hi @shenzhuxi
Thank you for your report, I don't use SQLite so I never noticed that.
I would say the easiest way would be to make a PR :-)
I don't know how many functions would need a new mapping entry? If there are only a few we can just update the _SQLITE_FUNCTIONS list but there are a lot we could move the list to a new file, like the other functions.

[shenzhuxi]

Hi @adrien-berchet
There are quite a few functions like this and also functions like ST_Buffer with different parameters. I hope to find a easy way for users to create wrapper functions like ST_AsGeoJSON

geoalchemy2/geoalchemy2/functions.py

Line 89 in b107df9

class ST_AsGeoJSON(with_metaclass(_GenericMeta, functions.GenericFunction)):

So we won't need to constantly make PR for all the different functions.

[adrien-berchet]

Yeah we could provide an API so people can register whatever they need.
We could change this in geoalchemy2.functions for example:

_SQLITE_FUNCTIONS = {
    "ST_GeomFromEWKT": "GeomFromEWKT",
    "ST_GeomFromEWKB": "GeomFromEWKB",
    "ST_AsBinary": "AsBinary",
    "ST_AsEWKB": "AsEWKB",
    "ST_AsGeoJSON": "AsGeoJSON",
}


# Default handlers are required for SQLAlchemy < 1.1
# See more details in https://github.com/geoalchemy/geoalchemy2/issues/213
def _compiles_default(cls):
    def _compile_default(element, compiler, **kw):
        return "{}({})".format(cls, compiler.process(element.clauses, **kw))
    compiles(globals()[cls])(_compile_default)


def _compiles_sqlite(cls, fn):
    def _compile_sqlite(element, compiler, **kw):
        return "{}({})".format(fn, compiler.process(element.clauses, **kw))
    compiles(globals()[cls], "sqlite")(_compile_sqlite)


def register_sqlite_mapping(mapping):
    """Register compilation mappings for the given functions.

    ``mapping`` should have the following form::

        {
            "function_name_1": "sqlite_function_name_1",
            "function_name_2": "sqlite_function_name_2",
            ...
        }
    """
    for cls, fn in mapping.items():
        _compiles_default(cls)
        _compiles_sqlite(cls, fn)


register_sqlite_mapping(_SQLITE_FUNCTIONS)

But adding entries in the _SQLITE_FUNCTIONS dict would be nice too, at least for the most common functions.

[adrien-berchet]

#360 should allow you to register new mappings.

I hope to find a easy way for users to create wrapper functions like ST_AsGeoJSON.

I am not sure to understand this part of the issue. The ST_AsGeoJSON function is very specific because it can accept a selectable as argument (like SELECT ST_AsGeoJSON(t.*)), which needs specific processing. But usually the functions only care about their arguments when they are WKBElement/WKTElement objects, otherwise they are just sent to the DB as is. So the number of parameters should not be an issue, except if the orders of the parameters are different in PostGIS and Spatialite. Does this use case actually exist?

[shenzhuxi]

For example, in PostGIS, ST_Buffer can take options like quad_segs, endcap, join, mitre_limit and side from the third parameter.
https://www.postgis.net/docs/ST_Buffer.html
In Spatialite, two separated functions ST_SingleSidedBuffer and ST_Buffer will deal with the sides and can only take quad_segs as a parameter. Other options will need to be set through other functions in Spatialite 5. https://www.gaia-gis.it/fossil/libspatialite/wiki?name=BufferOptions

In the PostGIS statistics functions, for example, ST_Histogram will return a set of records. https://postgis.net/docs/RT_ST_Histogram.html
Seems there is not way to access the column with the names "min, max, count, percent" if just use func.ST_Histogram without a wrapper in sqlalchemy/geoalchemy. Please correct me if I missed the right way.

[adrien-berchet]

Hmmm I see. There are several issues here:

1. for your example with ST_Buffer, there are two things:
   1. you would like to map the ST_Buffer function to either ST_SingleSidedBuffer or ST_Buffer, depending on the given parameters. I think this could be achieved with some specific compilation procedure like this:

   def _compiles_buffer_sqlite():
       def _compile_sqlite(element, compiler, **kw):
           if kw.get("side", "both") != "both":
               return "{}({})".format("Buffer", compiler.process(element.clauses, **kw))
           else:
               return "{}({})".format("SingleSidedBuffer", compiler.process(element.clauses, **kw))
       compiles(globals()["ST_Buffer"], "sqlite")(_compile_sqlite)

   Could you try this? If it works we could add some doc about it but I think I will never get time to create such specific functions for all possible cases.
   2. you would like to change a function into several others. For example, if you want to change the endcap you have to call BufferOptions_SetEndCapStyle before the actual query but I am not sure this is possible. Or maybe with a specific event so we execute the query to set the cap style before any other query containing a ST_Buffer statement? That looks very hard to do.
2. for your example with ST_Histogram, it is indeed not possible by default because SQLAlchemy does not know the type returned by the function. If you want to access it you have to declare a UserDefinedType, as shown in the Use CompositeType gallery example.

[adrien-berchet]

Ah and please tell me if #360 is relevant to you?

[adrien-berchet]

Hi @shenzhuxi
I gave a try for your ST_Buffer example in #361 , is it what you were looking for?

[shenzhuxi]

Hi @adrien-berchet
Yes, that's what I'm talking about. Thanks for the quick reply.
I'm sorry that I haven't got time to checkout the changes yet. I will do it as soon as possible.

[shenzhuxi]

Ah and please tell me if #360 is relevant to you?

Hi @adrien-berchet
I just gave it a try.

mapping = {
    "ST_MakeLine": "MakeLine",
}
register_sqlite_mapping(mapping)

It works well.

Hi @shenzhuxi I gave a try for your ST_Buffer example in #361 , is it what you were looking for?
I tried the example and it also works.

Thanks!

[adrien-berchet]

Thanks for your feedback @shenzhuxi !
I merged so these examples and features will be part of the next release (I just want to merge #362 before releasing a new version).