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.