Can't query geometry column in postgres db

[monkseal]

What happens?

I have a table in PostgreSQL with a geometry field, but I’m unable to query that field using the DuckDB Postgres extension. I’m not sure if this compatibility is supported. I haven’t found any documentation addressing it but would like to create a bug to see if this is supported.

To Reproduce

This is the DDL for my postgres table:

create table if not exists public.buildings
(
    id                             varchar                        not null,
    geometry                       geometry(Geometry, 4326)

);

create index if not exists buildings_geometry_idx
    on public.buildings using gist (geometry);

When I connect to this using DuckDB and attempt to query it via this setup:

           INSTALL spatial;LOAD spatial;INSTALL postgres;LOAD postgres;
            ATTACH '' AS geoservice_pg_db (TYPE POSTGRES);

Then query with:

            SELECT
              id,
              geometry::geometry AS geometry
            FROM geoservice_pg_db.buildings
            WHERE ST_Intersects(
                   geometry::geometry,
                  ST_MakeEnvelope(-98.87893372552189::double,29.053189036509874::double,-98.64509585098368::double, 29.248781625789892::double)
);
            limit 100

This is error I'm seeing:

DuckDB::Error: Conversion Error: WKT Parser: Unknown geometry type '0103000020E610000001000000140000000C0D1F6CCCEC64C0581D39D299E954C08917FA16CCEC64C0520141CA99E954C0F709FBD1CBEC64C03AA869BC99E954C033816CA3CBEC64C0A5D189A999E954C027497C93CBEC64C01D1C919499E954C0FDE0D7A3CBEC64C09466987F99E954C0A7199CD2CBEC64C0FF8FB86C99E954C08C25F61ACCEC64C01DD7755E99E954C011291774CCEC64C017BB7D5699E954C0EE388FD4CCEC64C082FBA65599E954C0C73A0B31CDEC64C09438865B99E954C0A3337A7FCDEC64C04C721B6899E954C0D8182AB5CDEC64C0ED69E27899E954C0730B02D0CDEC64C01700998C99E954C005BDDCCCCDEC64C0D55526A199E954C03D3D5BACCDEC64C0348C71B499E954C08BDAA271CDEC64C040C461C499E954C0AFE13323CDEC64C0CF7E49CF99E954C0CABED0C8CCEC64C0829CE6D399E954C00C0D1F6CCCEC64C0581D39D299E954C0' at position 0 near: '0'|<--- (DuckDB::Error)

OS:

ios

PostgreSQL Version:

16.4

DuckDB Version:

1.1.3

DuckDB Client:

ruby

Full Name:

Kevin English

Affiliation:

Dataplor

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[nikmarch]

To solve the issue we convert PG Geom => WKT => DuckDB geom => insert:

INSERT  
INTO buildings (id, geometry)  
SELECT id, ST_GeomFromText(geometry) as geometry  
FROM postgres_query(  
        'pg_db',  
        'SELECT id,  
          ST_AsText(geometry::geometry) AS geometry          FROM buildings      WHERE ST_Intersects(            geometry::geometry,            ST_MakeEnvelope(-98.87893372552189,29.053189036509874, -98.64509585098368, 29.248781625789892,4326)          )          limit 10');

[monkseal]

@nikmarch Yes that works

[rcoup]

Using WKB will probably be a lot quicker, and potentially avoid some floating-point<->text issues

INSERT INTO buildings (id, geom)  
SELECT id, ST_GeomFromWKB(wkb_geom) AS geom  
  FROM postgres_query(  
    'pg_db',  
    'SELECT id, ST_AsBinary(geom) AS wkb_geom FROM buildings'
  );