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 sqlalchemy+unsubscr...@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/44d065c7-0512-4039-8941-b2cfac313fd3%40www.fastmail.com.
