Define a GenericFunction that passes all columns

225 views
Skip to first unread message

Stephan Hügel

unread,
Jan 19, 2020, 10:54:45 AM1/19/20
to sqlalchemy
I'm trying to define a GenericFunction that calls a PostGIS 3.0 function (ST_AsGeoJson). The latest version can be called in two different ways:

SELECT ST_AsGeoJSON(t.geom) FROM foo as t WHERE t.id = 1;
SELECT ST_AsGeoJSON(t.*) FROM foo as t WHERE t.id = 1;

where the first example returns a GeoJSON Geometry, and the second returns a GeoJSON Feature. I'm only interested in making use of the second type / am happy to define the two variants separately.

I should be able to subclass GenericFunction in order to do this, but I'm missing two details:

1. What should the return type of my subclass be?
2. How can I specify that I want to pass the record / row / all columns, as opposed to just the geom column to the underlying function? Ordinarily, attempting something like 

sess.query(func.MyGeojson(Foo)).all()

Will give me an error:

Object <class 'models.Foo'> is not legal as a SQL literal value



Mike Bayer

unread,
Jan 19, 2020, 11:11:01 AM1/19/20
to noreply-spamdigest via sqlalchemy
by "return type" I think you're referring to the "type" attribute, so in SQL if you were to make a database column that stores the result of the ST_AsGeoJSON function, a quick google shows it as "text", so for SQLAlchemy you could make the return type String, VARCHAR, TEXT, etc.

for the column expansion you override the constructor and inspect the object(s) given and do what you want with them.    SQLAlchemy has some built-in systems for "how to expand an ORM model or Selectable into Table columns" but only in 1.4 is there a new system for this that is organized and predictable, so for the moment you'd want to roll it yourself:

from sqlalchemy import inspect

class MyFunction(GenericFunction):
    def __init__(self, *args, **kw):
        pass_args = []
        for arg in args:
            insp = inspect(arg)
            if hasattr(insp, "selectable"):
                pass_args.extend(selectable.c.)
           else:
                pass_args.append(arg)
        super(MyFunction, self).__init__(*pass_args, **kw)

also note there's been a lot of call for Geo support lately (not sure if you were the person that filed an issue re: SQL Server) but we are really looking for help to get geoalchemy2 up to date and supporting more backends.






--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Mike Bayer

unread,
Jan 19, 2020, 11:13:40 AM1/19/20
to noreply-spamdigest via sqlalchemy


On Sun, Jan 19, 2020, at 11:10 AM, Mike Bayer wrote:


On Sun, Jan 19, 2020, at 10:54 AM, Stephan Hügel wrote:
I'm trying to define a GenericFunction that calls a PostGIS 3.0 function (ST_AsGeoJson). The latest version can be called in two different ways:

SELECT ST_AsGeoJSON(t.geom) FROM foo as t WHERE t.id = 1;
SELECT ST_AsGeoJSON(t.*) FROM foo as t WHERE t.id = 1;

where the first example returns a GeoJSON Geometry, and the second returns a GeoJSON Feature. I'm only interested in making use of the second type / am happy to define the two variants separately.

I should be able to subclass GenericFunction in order to do this, but I'm missing two details:

1. What should the return type of my subclass be?
2. How can I specify that I want to pass the record / row / all columns, as opposed to just the geom column to the underlying function? Ordinarily, attempting something like 

sess.query(func.MyGeojson(Foo)).all()

Will give me an error:

Object <class 'models.Foo'> is not legal as a SQL literal value



by "return type" I think you're referring to the "type" attribute, so in SQL if you were to make a database column that stores the result of the ST_AsGeoJSON function, a quick google shows it as "text", so for SQLAlchemy you could make the return type String, VARCHAR, TEXT, etc.

OK well looking at  https://postgis.net/docs/ST_AsGeoJSON.html  it says "Return the geometry as a GeoJSON "geometry" object, or the row as a GeoJSON "feature" object", even though the signatures say "text".     OK so I see you want to change the type based on the arguments.  It's safe to do that in your constructor as well;  "if this_looks_like_a_row(args):  self.type = Feature  else: self.type=Geometry".

Stephan Hügel

unread,
Jan 19, 2020, 12:23:31 PM1/19/20
to sqlalchemy
To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.



Thanks for the quick response Mike!

OK so far I've got: https://gist.github.com/urschrei/782989d0b3bee8e4208eb2bea945b65b. Requires PostGIS 3.0.x as an extension, and requires GeoAlchemy2.

On line 15, I fixed what I assume was a typo for selectable in your original suggestion. I still get the expected

Object <class '__main__.Hardinfra'> is not legal as a SQL literal value


error though. I don't follow your suggestion about


"if this_looks_like_a_row(args):  self.type = Feature  else: self.type=Geometry"


possibly because I'm not well-versed in SQLA's internals – is there an easy way to distinguish between a row and a column? That still leaves the issue of the "Geometry" vs "Feature" distinction in self.types: Geoalchemy2 defines a "Geometry" type, but not a "Feature" type, because "Feature" is only a part of the GeoJSON spec, but I doubt that either of these things are causing the problem. I've also tried executing the SQL query on my db to make sure it's working and both


SELECT ST_AsGeoJSON(t.*) FROM hardinfra as t WHERE t.id = 1;

SELECT ST_AsGeoJSON(t.geom) FROM hardinfra as t WHERE t.id = 1;


work, returning a GeoJSON Feature and Geometry respectively.


I didn't open the issue about SQL Server, but I use GeoAlchemy regularly, so if there are intro issues available I'm happy to help out when I can.


Mike Bayer

unread,
Jan 20, 2020, 12:05:31 PM1/20/20
to noreply-spamdigest via sqlalchemy
that was pseudocode.     If the incoming argument is a "selectable", that is, has a .c attribute, it's a set of columns, therefore, it's a row.  otherwise, it's not.

Here's complete POC

from sqlalchemy import Column
from sqlalchemy import func
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.functions import GenericFunction
from sqlalchemy.types import UserDefinedType


class Feature(UserDefinedType):
    pass


class Geometry(UserDefinedType):
    pass


class MyGj(GenericFunction):
    def __init__(self, *args, **kw):
        pass_args = []
        for arg in args:
            insp = inspect(arg)
            if hasattr(insp, "selectable"):
                pass_args.extend(insp.selectable.c)
                self.type = Feature
            else:
                self.type = Geometry
                pass_args.append(arg)
        super(MyGj, self).__init__(*pass_args, **kw)

    name = "ST_AsGeoJson"


Base = declarative_base()


class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String)


function = func.ST_AsGeoJson(A)

assert isinstance(function.type, Feature)


print(function)



possibly because I'm not well-versed in SQLA's internals – is there an easy way to distinguish between a row and a column? That still leaves the issue of the "Geometry" vs "Feature" distinction in self.types: Geoalchemy2 defines a "Geometry" type, but not a "Feature" type, because "Feature" is only a part of the GeoJSON spec, but I doubt that either of these things are causing the problem. I've also tried executing the SQL query on my db to make sure it's working and both


SELECT ST_AsGeoJSON(t.*) FROM hardinfra as t WHERE t.id = 1;

SELECT ST_AsGeoJSON(t.geom) FROM hardinfra as t WHERE t.id = 1;


work, returning a GeoJSON Feature and Geometry respectively.


I didn't open the issue about SQL Server, but I use GeoAlchemy regularly, so if there are intro issues available I'm happy to help out when I can.



--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Adrien Berchet

unread,
Apr 13, 2020, 6:25:15 PM4/13/20
to sqlalchemy
Hello there

I tried to integrate your POC in GeoAlchemy2 in the following PR:

It is almost working but the ST_AsGeoJSON() function needs a record, not a list of columns. So the query should be like:
SELECT ST_AsGeoJSON(t.*)
FROM t;
while the example you provided gives:
SELECT ST_AsGeoJSON(t.id, t.geom)
FROM t;
which is not accepted by PostGIS (Error: the function st_asgeojson(integer, geometry) does not exist).

I was able to make it work in the PR by adding a call to the ROW() function, which leads to the following query:
SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
FROM t;
This query is properly executed by PostGIS but the column names are lost. For example I will get the following result:
{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"f1": 1}} => "f1" property should be name "id"
instead of:
{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1}}

Do you have any idea to overcome this issue? I did not find how to produce a "t.*" with SQLAlchemy...

Thanks :-)

Mike Bayer

unread,
Apr 13, 2020, 6:47:28 PM4/13/20
to noreply-spamdigest via sqlalchemy


On Mon, Apr 13, 2020, at 6:25 PM, Adrien Berchet wrote:
Hello there

I tried to integrate your POC in GeoAlchemy2 in the following PR:

It is almost working but the ST_AsGeoJSON() function needs a record, not a list of columns. So the query should be like:
SELECT ST_AsGeoJSON(t.*)
FROM t;
while the example you provided gives:
SELECT ST_AsGeoJSON(t.id, t.geom)
FROM t;
which is not accepted by PostGIS (Error: the function st_asgeojson(integer, geometry) does not exist).

I was able to make it work in the PR by adding a call to the ROW() function, which leads to the following query:
SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
FROM t;
This query is properly executed by PostGIS but the column names are lost. For example I will get the following result:
{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"f1": 1}} => "f1" property should be name "id"
instead of:
{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1}}

Do you have any idea to overcome this issue? I did not find how to produce a "t.*" with SQLAlchemy...


There should definitely be some way for this to work without doing "*".  what if "t" didn't have the columns in that specific order?   the "ROW" function seems like what should be used but I don't understand the "column names" issue, is this regarding what goes into the JSON structure that the PG function returns?  there should be a way to affect that at the SQL level.





To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Adrien Berchet

unread,
Apr 14, 2020, 5:31:54 AM4/14/20
to sqlalchemy
The "column names" issue is that when we use ROW(), like in the following query:
SELECT ROW(t.id, t.geom)
FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
we obtain the following result:
                      row
------------------------------------------------
 (1,0101000000000000000000F03F000000000000F03F)
in which the initial column names ('id' and 'geom') are lost. So when we give this result to the ST_AsGeoJson() function, it can not retrieve these names for the property names so it just replaces them by 'f1', 'f2', ...

And I can't find any way to pass the names to the ROW() constructor: https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

Mike Bayer

unread,
Apr 14, 2020, 8:23:40 AM4/14/20
to noreply-spamdigest via sqlalchemy
and you can't say "SELECT t.d, t.geom" ?   There really should be no difference between "t.*" and "t.id, t.geom".    
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Adrien Berchet

unread,
Apr 14, 2020, 9:46:22 AM4/14/20
to sqlal...@googlegroups.com
I just found that in fact it is possible to just pass the table name to ST_AsGeoJson, so the following query works:
SELECT ST_AsGeoJSON(t)
FROM t;

I will try to use this writing in GeoAlchemy2, though I don't know yet how to translate it in SQLAlchemy.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/owT52zKYNVw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0d878447-57c6-414b-9785-a41ebb9486e4%40www.fastmail.com.

Mike Bayer

unread,
Apr 14, 2020, 9:57:56 AM4/14/20
to noreply-spamdigest via sqlalchemy
does this ST_AsGeoJSON function hardcode itself to look for column names "id" and "geom" ?   it's not going to be any easier to get SQLAlchemy to render "t" than it is "t.*".   it wants to name columns.

Adrien Berchet

unread,
Apr 14, 2020, 10:29:23 AM4/14/20
to sqlal...@googlegroups.com
This function is defined here:
And its C implementation is here:

Its first argument is a record and the second one (optional) is the name of the geometry column. If the second argument is not given, it iterates over all attributes of this record, pick the first geometry attribute to convert it to a GeoJSON Geometry and all over attributes are set as properties in the generated GeoJSON.
The only two ways I found to use this function are the following:
  1. SELECT ST_AsGeoJson(t.*, 'geom')  -- The 'geom' argument is optional here

  1. FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
  2. SELECT ST_AsGeoJson(t, 'geom')  -- The 'geom' argument is optional here

  1. FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
If we want to convert only a subset of columns into GeoJson properties we have to use a subquery to select this subset, it is not possible (as far as I can see) to pass the subset to the function. The only way I found to pass a subset is using the ROW() function but then the GeoJson properties have dummy names ('f1', 'f2', ...).

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ef6eae5a-4c1e-4b43-82ab-854c89924938%40www.fastmail.com.

Mike Bayer

unread,
Apr 14, 2020, 12:33:14 PM4/14/20
to noreply-spamdigest via sqlalchemy
OK so use the "t" form with the "geom" name sent as a string, it wants the whole row so this is a special Postgresql syntax.    There are many ways to make it output this and it depends on the specifics of how this is being rendered.   it may require a custom construct with a @compiles rule as I would assume it needs to respond to things like table alias names, subquery alias names, etc.

Adrien Berchet

unread,
Apr 15, 2020, 7:16:13 AM4/15/20
to sqlal...@googlegroups.com
Ok, thank you for your advice, following it I tried the following (in geoalchemy2.functions.py).
class ST_AsGeoJSON(functions.GenericFunction):

    def __init__(self, *args, **kwargs):
        args = list(args)
        self.feature_mode = False
        for idx, elem in enumerate(args):
            try:
                insp = inspect(elem)
                if hasattr(insp, "selectable"):
                    args[idx] = insp.selectable
                    self.feature_mode = True
            except Exception:
                continue
        functions.GenericFunction.__init__(self, *args, **kwargs)


def _compile_ST_AsGeoJSON(cls):
    def _compile_geojson_feature(cls_name, element, compiler, **kw):
        if not element.feature_mode:
            return "{}({})".format(cls_name, compiler.process(element.clauses, **kw))
        else:
            clauses = list(element.clauses)
            table = compiler.process(clauses[0], asfrom=True, **kw).split(".")[-1]  # This is quite dirty
            args = []
            if len(clauses) > 1:
                args = ", ".join([compiler.process(i, **kw) for i in clauses[1:]])
            return "{}({})".format(cls_name, ", ".join([table, args]))

    def _compile_geojson_default(element, compiler, **kw):
        return _compile_geojson_feature(cls, element, compiler, **kw)

    def _compile_geojson_sqlite(element, compiler, **kw):
        return _compile_geojson_feature(cls[3:], element, compiler, **kw)

    compiles(globals()[cls])(_compile_geojson_default)
    compiles(globals()[cls], "sqlite")(_compile_geojson_sqlite)


_compile_ST_AsGeoJSON("ST_AsGeoJSON")

I am not sure it is the right way to do it but using this it is possible to write the following queries:
  1. select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
  2. select([func.ST_AsGeoJSON(Lake, 'geom')]) 
But it does not work for subqueries:
sq = select([Lake, bindparam('dummy_val', 10).label('dummy_attr')]).alias()
select([func.ST_AsGeoJSON(sq, 'geom')])
because the generated query is:
[SELECT ST_AsGeoJSON(lake) AS anon_1, %(ST_AsGeoJSON_2)s) AS "ST_AsGeoJSON_1" 
FROM (SELECT gis.lake.id AS id, gis.lake.geom AS geom, %(dummy_val)s AS dummy_attr 
FROM gis.lake) AS anon_1] 
[parameters: {'dummy_val': 10, 'ST_AsGeoJSON_2': 'geom'}] 
How can I get the alias of an aliased selectable?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7067863f-c502-4d9d-91e7-3e58e9122339%40www.fastmail.com.

Mike Bayer

unread,
Apr 15, 2020, 10:11:08 AM4/15/20
to noreply-spamdigest via sqlalchemy
working on this now, just a quick question, is there an actual difference between

select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])

and

select([func.ST_AsGeoJSON(Lake, 'geom')])

?

that is, users definitely need this goofy "Table" syntax, right?

Adrien Berchet

unread,
Apr 15, 2020, 10:34:02 AM4/15/20
to sqlal...@googlegroups.com
Yes, the first query:
select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
returns only the geometry part of a GeoJson:
{
            "type": "LineString",
            "coordinates": [[0, 0], [1, 1]]
}

while the query:
select([func.ST_AsGeoJSON(Lake, 'geom')])
returns a complete GeoJson with properties:
{
            "type": "Feature",
            "geometry": {
                "type": "LineString",
                "coordinates": [[0, 0], [1, 1]]
            },
            "properties": {"id": 1}
}

Thanks for your help!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/84dde841-8b7c-4e2b-b47c-776048e231a1%40www.fastmail.com.

Mike Bayer

unread,
Apr 15, 2020, 10:59:04 AM4/15/20
to noreply-spamdigest via sqlalchemy
so the attached script includes what I was suggesting, which is that when this table or mapped class comes in, you get that into a ColumnElement right away, that way the function internals treat it like any other column; the code fails otherwise in current development SQLAlchemy versions that are more strict about passing the correct kinds of arguments to things.   the element itself is:

class TableRowThing(ColumnElement):
    def __init__(self, selectable):
        self.selectable = selectable

    @property
    def _from_objects(self):
        return [self.selectable]


then to get the name, SQLCompiler doesn't have public API to get just this name, so usually when that happens the best approach is to get a string from the compiler that you know has what you need and you know how to find it, in this case, compile a column and pull the table name out:

@compiles(TableRowThing)
def _compile_table_row_thing(element, compiler, **kw):
    compiled = compiler.process(list(element.selectable.columns)[0], **kw)

    # 1. check for exact name of the selectable is here, use that.
    # this way if it has dots and spaces and anything else in it, we
    # can get it w/ correct quoting
    m = re.match(r"(.?%s.?)\." % element.selectable.name, compiled)
    if m:
        return m.group(1)

    # 2. just split on the dot, assume anonymized name
    return compiled.split(".")[0]

a test suite is included in the attached along with a crazy table name test.
test3.py

Adrien Berchet

unread,
Apr 16, 2020, 4:02:29 AM4/16/20
to sqlal...@googlegroups.com
This works perfectly, thank you very much! With this example I understood much better how the compiles() function works.
I had to update your example to make it work in GeoAlchemy2 but everything seems ok now. See https://github.com/geoalchemy/geoalchemy2/pull/258
Thanks again!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d426e4ab-cb61-4f5a-a27e-6f6932663bf0%40www.fastmail.com.
Reply all
Reply to author
Forward
0 new messages