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

[rmrodriguez at carto.com]

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