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 <mike...@zzzcomputing.com> 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 <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 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/ef6eae5a-4c1e-4b43-82ab-854c89924938%40www.fastmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/ef6eae5a-4c1e-4b43-82ab-854c89924938%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/CAKuTeydSZwR-aUt0k9sXVtkGPhwd3uidbLc97SNKF1cw%2BJiAsA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAKuTeydSZwR-aUt0k9sXVtkGPhwd3uidbLc97SNKF1cw%2BJiAsA%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/7067863f-c502-4d9d-91e7-3e58e9122339%40www.fastmail.com.
