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

[Paul Ramsey]

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