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

[Felix Kunde]

Thanks Raúl!:D

Would be super great to also backport to v3.0. There might be even more people out there who did store their WKBs in jsonb.
But, if this means even more pain for you, I'm fine with waiting for 3.1. For my audit trail it only affects restoring, not logging.

lg


Gesendet: Montag, 30. März 2020 um 19:37 Uhr
Von: rmrodriguez at carto.com
An: "PostGIS Development Discussion" <postgis-devel at lists.osgeo.org>
Betreff: Re: [postgis-devel] Cast GeoJSON to geometry like with WKT
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-[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-[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[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[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[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[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[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[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[https://lists.osgeo.org/mailman/listinfo/postgis-devel]