Unimplemented type for cast (MULTIPOLYGON WKB_BLOB -> POLYGON_2D)

[marklit]

Is there a timeline for when casts like the following will be supported?

SELECT ST_GEOMFROMWKB(a.geom) geom2, b.*
FROM st_read('geom_xaa.gpkg') a
LEFT JOIN us_geom b
    ON ST_WITHIN(ST_Centroid(ST_GEOMFROMWKB(a.geom))::POINT_2D,
                 b.geom::POLYGON_2D)
limit 1;

Error: Conversion Error: Unimplemented type for cast (WKB_BLOB -> POLYGON_2D)

SELECT ST_GEOMFROMWKB(a.geom) geom2, b.*
FROM st_read('geom_xaa.gpkg') a
LEFT JOIN us_geom b ON ST_WITHIN(ST_Centroid(ST_GEOMFROMWKB(a.geom)), 
                                 ST_GEOMFROMWKB(b.geom))
limit 1;

Error: Not implemented Error: Geometry type not supported

[Maxxen]

You should be able to already, are you sure the WKB actually contains a 2D polygon? If it is a multipolygon or contains z/m values it won't work however.

[marklit]

I had a look and its a MULTIPOLYGON.

$ git clone https://github.com/scdoshi/us-geojson/
$ ogr2ogr -f GPKG US.gpkg us-geojson/geojson/nation/US.geojson

$ ~/duckdb_spatial/build/release/duckdb -unsigned

The geom column is a MULTIPOLYGON wkb_blob.

select * FROM st_read('US.gpkg');

┌─────────┬─────────┬───────────────┬─────────────────────────────────────────────────────────────────────────────────────┐
│   id    │  GEOID  │     NAME      │                                        geom                                         │
│ varchar │ varchar │    varchar    │                                      wkb_blob                                       │
├─────────┼─────────┼───────────────┼─────────────────────────────────────────────────────────────────────────────────────┤
│ US      │ US      │ United States │ MULTIPOLYGON (((-135.69512 57.363077, -135.687696 57.367477, -135.659614 57.37390…  │
└─────────┴─────────┴───────────────┴─────────────────────────────────────────────────────────────────────────────────────┘

If I try and convert it into a Geometry type during import I get this:

CREATE OR REPLACE TABLE us_geom AS
    SELECT ST_GEOMFROMWKB(geom) geom
    FROM st_read('US.gpkg');

Error: Not implemented Error: Geometry type not supported

So I can only bring it in as a wkb_blob but from there I don't know how to get it converted into the Geometry data type which I believe is the best data type to be using (correct me if I'm wrong).

CREATE OR REPLACE TABLE us_geom AS
    SELECT geom
    FROM st_read('US.gpkg');

select * from us_geom;

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                          geom                                                           │
│                                                        wkb_blob                                                         │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ MULTIPOLYGON (((-135.69512 57.363077, -135.687696 57.367477, -135.659614 57.373906999999996, -135.639556 57.371883, -…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[aborruso]

Hi @marklit , running ogrinfo -summary us-geojson/geojson/nation/US.geojson you get

INFO: Open of `geo.geojson'
      using driver `GeoJSON' successful.
1: geo (3D Multi Polygon)

I think that currently 3D Multi Polygon geom is not supported. Only 2D

[DanCranford]

Hey there. I'm running into a similar issue with polygons that I'm fairly sure are not 3D. I'm getting the same error as @marklit. I'm reading from a File Geodatabase though.

Here's the code I'm using.

SELECT 
    ST_GeomFromWKB(target.SHAPE)
FROM
    st_read('PATH TO FILE GEODATABASE', layer = 'LAYER_NAME') target
LIMIT 10

The error I get is the same as above.

SQL Error: Not implemented Error: Geometry type not supported

This info about the feature class leads me to believe that it's not 3D. It says it has no Z-values

Any thoughts on this? I'm able to read the wkb but can't do anything with it.

Edit.... If I copy the same data to a geopackage, I can retrieve and convert the geometry without issue.