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 <mike...@zzzcomputing.com> 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,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 a topic in the 
>> Google Groups "sqlalchemy" group.
>>  To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/sqlalchemy/owT52zKYNVw/unsubscribe.
>>  To unsubscribe from this group and all its topics, send an email to 
>> sqlalchemy+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
>>  
>> <https://groups.google.com/d/msgid/sqlalchemy/0d878447-57c6-414b-9785-a41ebb9486e4%40www.fastmail.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/CAKuTeydbAbY9VLJFAbK42gLPO6maeugyX0ciJjrwotArabtASQ%40mail.gmail.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/CAKuTeydbAbY9VLJFAbK42gLPO6maeugyX0ciJjrwotArabtASQ%40mail.gmail.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/ef6eae5a-4c1e-4b43-82ab-854c89924938%40www.fastmail.com.

Reply via email to