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.
