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,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
>
>
> 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 <https://postgis.net/docs/ST_AsGeoJSON.html>).
>>>>>>>> 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".
>>>>>>
>>>>>>
>>>>>>
>>>>>>>
>>>>>>> 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
>>>>>>>>
>>>>>>>> <https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>>>
>>>>>>
>>>>>
>>>>> 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"
>>>>
>>>> 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
>>>>>
>>>>> 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/795a8d14-17da-4c47-a917-78f355e88951%40googlegroups.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/sqlalchemy/795a8d14-17da-4c47-a917-78f355e88951%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>
>>>
>>> --
>>> 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/fedacdfa-5bd6-4f6c-9c0a-e2f8287585b7%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/fedacdfa-5bd6-4f6c-9c0a-e2f8287585b7%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> 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/8410c4e7-bf98-45b7-9b43-ae9157152aef%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/8410c4e7-bf98-45b7-9b43-ae9157152aef%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/0d878447-57c6-414b-9785-a41ebb9486e4%40www.fastmail.com.
