search

duckdb / duckdb_spatial

MIT License
486 stars 39 forks source link

Make GEOMETRY type recognizable by DBeaver #142

Open ttomasz opened 1 year ago

ttomasz commented 1 year ago

It would be nice if GEOMETRY type was recognized by DBeaver which has a spatial viewer.

Right now creating geometry by using e.g. ST_Point results in having a geometry column but there is no option to display it on a map like it would be with a Postgis database.

Maxxen commented 1 year ago

Hi! Thanks for reporting this issue. This seems to be a DBeaver issue? Although I don't expect them to be able to read our internal geometry format (nor do I think that would be a good idea). I don't use DBeaver personally, but perhaps if you transform the GEOMETRY to WKB or WKT DBeaver can pick it up and display it properly?

ttomasz commented 1 year ago

I assume there is some standard Geometry type defined by SQL/MM or Simple Features spec and DBeaver knows how to recognize that.

I tried with WKB_BLOB but it's not recognized. Interestingly it's represented as WKT not WKB but column type is marked as WKB_BLOB. image

Maxxen commented 1 year ago

Seems like this is implemented on a case-by-case basis, at least there's special handler code for PostGIS

I'd definitely like to investigate how hard it would be to add something similar for DuckDB, my only concern is that DBeaver should not have to depend on the internal binary format of DuckDB geometries (as thats still evolving), but maybe its possible for DBeaver to intercept and convert the result-set to WKB/WKT before display.

ttomasz commented 1 year ago

Interesting, for reference this is the switch statement which leads to the class that conditionally builds the geometry.

In DuckDBs case it seems that some types get mapped to struct and some to blob: https://github.com/dbeaver/dbeaver/blob/devel/plugins/org.jkiss.dbeaver.ext.duckdb/src/org/jkiss/dbeaver/ext/duckdb/model/DuckDataTypeCache.java#L67C11-L67C11