[postgis-devel] Cast GeoJSON to geometry like with WKT

[rmrodriguez at carto.com]

Alright, since having this would have saved me hours of pain, I've
created a ticket to track it:
https://trac.osgeo.org/postgis/ticket/4656

If nobody does, I'll try to get it for 3.1.

On Mon, Mar 30, 2020 at 7:10 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>
> Aha! Yes, this is it. The existence of the geometry::json cast in PostGIS 3+ allows (in modern PgSQL versions) the row_as_json function to convert the geometry element into json, so you get the geojson inside the row json.
>
> P
>
> > On Mar 30, 2020, at 10:08 AM, rmrodriguez at carto.com wrote:
> >
> > After looking into it I think I know what happened (still pending a
> > smaller repro), and the problem is not in that cast but somewhere
> > previously. The function that feeds this (creating data) returns:
> > ```
> > [{"geomref" : "48061", "value" : "0106000020E61000009A000000010...
> > ```
> > In Postgis 2.x; but in 3.0 it returns:
> > ```
> > WARNING: [{"geomref" : "48061", "value" :
> > {"type":"MultiPolygon","coordinates":[[[[-97.167369,26.413108],[-97.156496,26.375348],[-97.126121,26.250351]...
> > ```
> >
> > Then casting the text binary works (2.x), but casting the text JSON
> > doesn't. The function that generates the output is a huge mess nobody
> > should touch with a ten foot pole so I might just add a ST_AsBinary at
> > some point there and forget I've ever looked into it.
> >
> > On Mon, Mar 30, 2020 at 6:52 PM Regina Obe <lr at pcorp.us> wrote:
> >>
> >> Curious does this work
> >>
> >> SELECT (data->0->'value')::Geometry
> >>
> >>> -----Original Message-----
> >>> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> >>> Behalf Of rmrodriguez at carto.com
> >>> Sent: Monday, March 30, 2020 12:40 PM
> >>> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> >>> Subject: Re: [postgis-devel] Cast GeoJSON to geometry like with WKT
> >>>
> >>> It contains a big polygon:
> >>> ```
> >>> [{"geomref" : "48061", "value" :
> >>> {"type":"MultiPolygon","coordinates":[[[[-97.167369,26.413108], ... , [-
> >>> 97.223544,26.411478],[-97.167369,26.413108]]]]}}]
> >>> ```
> >>>
> >>> It comes from a series of functions calls
> >>> (https://github.com/CartoDB/observatory-
> >>> extension/blob/6a063ca0eb33043f22e47171fc328aa50a6efc39/src/pg/sql/4
> >>> 4_observatory_geometries.sql#L127)
> >>> so I've tried to extract a smaller example that works with Postgis 2.5 and it
> >>> doesn't in 3.0 but I still haven't been able to.
> >>>
> >>> Note that both 2.5 and 3.0 and running under PG12
> >>> (https://travis-ci.org/github/CartoDB/observatory-
> >>> extension/builds/668806964)
> >>> so I'm discarding a change there. I'll share a smaller testable repro once I get
> >>> it.
> >>>
> >>> On Mon, Mar 30, 2020 at 5:58 PM Paul Ramsey
> >>> <pramsey at cleverelephant.ca> wrote:
> >>>>
> >>>> Wait, what? what is in 'value'?
> >>>>
> >>>>> On Mar 30, 2020, at 8:56 AM, rmrodriguez at carto.com wrote:
> >>>>>
> >>>>> I've been bitten by this today. This code used to work in Postgis 2.x:
> >>>>> ```
> >>>>> SELECT (data->0->>'value')::Geometry ``` But it now requires to be
> >>>>> called as:
> >>>>> ```
> >>>>> ST_GeomFromGeoJSON(data->0->>'value')
> >>>>> ```
> >>>>>
> >>>>> So I wouldn't be opposed to backporting this to 3.0 if it were to be
> >>>>> implemented.
> >>>>>
> >>>>> --
> >>>>> Ra l Mar n Rodr guez
> >>>>> carto.com
> >>>>> _______________________________________________
> >>>>> postgis-devel mailing list
> >>>>> postgis-devel at lists.osgeo.org
> >>>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >>>>
> >>>> _______________________________________________
> >>>> postgis-devel mailing list
> >>>> postgis-devel at lists.osgeo.org
> >>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >>>
> >>>
> >>>
> >>> --
> >>> Ra l Mar n Rodr guez
> >>> carto.com
> >>> _______________________________________________
> >>> postgis-devel mailing list
> >>> postgis-devel at lists.osgeo.org
> >>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >>
> >> _______________________________________________
> >> postgis-devel mailing list
> >> postgis-devel at lists.osgeo.org
> >> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >
> >
> >
> > --
> > Raúl Marín Rodríguez
> > carto.com
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel



-- 
Raúl Marín Rodríguez
carto.com