Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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!

Le mer. 15 avr. 2020 à 16:59, Mike Bayer  a
écrit :

> 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.
>
>
>
> On Wed, Apr 15, 2020, at 10:33 AM, Adrien Berchet wrote:
>
>
> 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!
>
> Le mer. 15 avr. 2020 à 16:11, Mike Bayer  a
> écrit :
>
>
> 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?
>
>
>
> On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
>
> 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', 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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.



On Wed, Apr 15, 2020, at 10:33 AM, Adrien Berchet wrote:
> 
> 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!
> 
> Le mer. 15 avr. 2020 à 16:11, Mike Bayer  a écrit :
>> __
>> 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?
>> 
>> 
>> 
>> On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
>>> 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?
>>> 
>>> 
>>> Le mar. 14 avr. 2020 à 18:33, Mike Bayer  a écrit 
>>> :
 __
 OK so use the "t" form with the "geom" name sent as a string, it wants 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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!

Le mer. 15 avr. 2020 à 16:11, Mike Bayer  a
écrit :

> 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?
>
>
>
> On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
>
> 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?
>
>
>
> Le mar. 14 avr. 2020 à 18:33, Mike Bayer  a
> écrit :
>
>
> 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.
>
>
>
>
>
>
> On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
>
> This function is defined here:
>
> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
> And its C implementation is here:
>
> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
>
> 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
>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
>FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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?



On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
> 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?
> 
> 
> Le mar. 14 avr. 2020 à 18:33, Mike Bayer  a écrit :
>> __
>> 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.
>> 
>> 
>> 
>> 
>> 
>> 
>> On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
>>> This function is defined here:
>>> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
>>> And its C implementation is here:
>>> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
>>> 
>>> 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
>>> 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
>>> 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', ...).
>>> 
>>> Le mar. 14 avr. 2020 à 15:57, Mike Bayer  a écrit 
>>> :
 __
 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.
 
 
 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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?


Le mar. 14 avr. 2020 à 18:33, Mike Bayer  a
écrit :

> 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.
>
>
>
>
>
>
> On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
>
> This function is defined here:
>
> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
> And its C implementation is here:
>
> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
>
> 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
>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
>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', ...).
>
> Le mar. 14 avr. 2020 à 15:57, Mike Bayer  a
> écrit :
>
>
> 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.
>
>
>
> On Tue, Apr 14, 2020, at 9:45 AM, Adrien Berchet wrote:
>
> 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.
>
> Le mar. 14 avr. 2020 à 14:23, Mike Bayer  a
> écrit :
>
>
> and you can't 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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.






On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
> This function is defined here:
> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
> And its C implementation is here:
> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
> 
> 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
> 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
> 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', ...).
> 
> Le mar. 14 avr. 2020 à 15:57, Mike Bayer  a écrit :
>> __
>> 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.
>> 
>> 
>> 
>> On Tue, Apr 14, 2020, at 9:45 AM, Adrien Berchet wrote:
>>> 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.
>>> 
>>> Le mar. 14 avr. 2020 à 14:23, Mike Bayer  a écrit 
>>> :
 __
 and you can't say "SELECT t.d, t.geom" ? There really should be no 
 difference between "t.*" and "t.id, t.geom". 
 
 
 
 On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
> 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,010100F03FF03F)
> 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
> 
> 
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>> 
>> 
>> 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:
>>> https://github.com/geoalchemy/geoalchemy2/pull/258
>>> 
>>> 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...
>> 
>> 
>> 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

This function is defined here:
https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
And its C implementation is here:
https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79

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
   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
   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', ...).

Le mar. 14 avr. 2020 à 15:57, Mike Bayer  a
écrit :

> 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.
>
>
>
> On Tue, Apr 14, 2020, at 9:45 AM, Adrien Berchet wrote:
>
> 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.
>
> Le mar. 14 avr. 2020 à 14:23, Mike Bayer  a
> écrit :
>
>
> and you can't say "SELECT t.d, t.geom" ?   There really should be no
> difference between "t.*" and "t.id, t.geom".
>
>
>
> On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
>
> 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,010100F03FF03F)
>
> 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
>
>
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> 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:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> 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.
>
>
>
>
>
>
> Thanks :-)
>
>
>
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> 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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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.



On Tue, Apr 14, 2020, at 9:45 AM, Adrien Berchet wrote:
> 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.
> 
> Le mar. 14 avr. 2020 à 14:23, Mike Bayer  a écrit :
>> __
>> and you can't say "SELECT t.d, t.geom" ? There really should be no 
>> difference between "t.*" and "t.id, t.geom". 
>> 
>> 
>> 
>> On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
>>> 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,010100F03FF03F)
>>> 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
>>> 
>>> 
>>> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
 
 
 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:
> https://github.com/geoalchemy/geoalchemy2/pull/258
> 
> 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.
 
 
 
 
 
> 
> Thanks :-)
> 
> ``
> 
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>> 
>> 
>> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>>> 
>>> 
>>> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
 
 
 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:
>> 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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.

Le mar. 14 avr. 2020 à 14:23, Mike Bayer  a
écrit :

> and you can't say "SELECT t.d, t.geom" ?   There really should be no
> difference between "t.*" and "t.id, t.geom".
>
>
>
> On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
>
> 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,010100F03FF03F)
>
> 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
>
>
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> 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:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> 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.
>
>
>
>
>
>
> Thanks :-)
>
>
>
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> 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  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".
>
>
>
>
> for the column 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

and you can't say "SELECT t.d, t.geom" ? There really should be no difference 
between "t.*" and "t.id, t.geom". 



On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
> 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,010100F03FF03F)
> 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
> 
> 
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>> 
>> 
>> 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:
>>> https://github.com/geoalchemy/geoalchemy2/pull/258
>>> 
>>> 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.
>> 
>> 
>> 
>> 
>> 
>>> 
>>> Thanks :-)
>>> 
>>> ``
>>> 
>>> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
 
 
 On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
> 
> 
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>> 
>> 
>> 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  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: 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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,010100F03FF03F)
>
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


Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> 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:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> 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.
>
>
>
>
>
>
> Thanks :-)
>
>
>
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> 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  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".
>
>
>
>
> 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 = 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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:
> https://github.com/geoalchemy/geoalchemy2/pull/258
> 
> 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.





> 
> Thanks :-)
> 
> ``
> 
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>> 
>> 
>> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>>> 
>>> 
>>> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
 
 
 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  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".
 
 
 
> 
> 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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

Hello there

I tried to integrate your POC in GeoAlchemy2 in the following PR:
https://github.com/geoalchemy/geoalchemy2/pull/258

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 :-)


Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> 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  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".
>
>
>
>
> 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
>  
> http://www.sqlalchemy.org/
>  
> 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 sqlal...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com
>  
> 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
> 
> 
> On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>> 
>> 
>> 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  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".
>> 
>> 
>> 
>>> 
>>> 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
 
 http://www.sqlalchemy.org/
 
 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 sqlal...@googlegroups.com.
 To view this discussion on the web visit 
 https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.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  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"


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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Stephan Hügel]

On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote:
>
>
>
> 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  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".
>
>
>
>
> 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
>  
> http://www.sqlalchemy.org/
>  
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.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  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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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  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".



> 
> 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
>> 
>> http://www.sqlalchemy.org/
>> 
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com
>>  
>> .
> 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f611d0bd-ff93-46e2-802f-b5f097c5723c%40www.fastmail.com.

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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  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.

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
> 
> http://www.sqlalchemy.org/
> 
>  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+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/541d726a-4652-4186-bd70-9f76241e6a20%40www.fastmail.com.

[sqlalchemy] Define a GenericFunction that passes all columns

[Stephan Hügel]

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  is not legal as a SQL literal value



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com.