Connecting PostGIS to KNIME

[gisplanet]

Hi, I’m trying to connect PostGIS to KNIME. I have a PostgreSQL table with a geometry column I’m trying to visualise using Geospatial View. But the geometry column is not detected. A workaround I found is to first convert it into GeoJSON in the DB Query Reader using SQL: select ST_AsGeoJSON(geom) from mytable;
This however is slow when using large datasets. See the logic here below.

Is there a way to use the PostGIS geometry column directly?

Cheers, Jelmer

[rrousselot]

Try ST_asText(ST_transform(“geom” ,4326)) as geom in your Query then convert that using WKT to Geometry Node. (I also, convert the CRS there you can take it out)

I suspect that might be faster. I have not found a way to use the PostGIS geometry directly.

I made a Postgres WKT Writer node, I can post if you need to write back.

[gisplanet]

Thanks @rrousselot. Do you know if there is a suggestion box somewhere? Directly connecting to PostGIS geometries would speed up things as you don’t need to convert all the geometries to ASCII first.

[rrousselot]

FWIW, most GIS work is translating file types. Knime uses available libraries in its tools to do those translations. The world has moved on to WKT and GeoJSON for in table spatial data because libraries like GeoPandas deal with spatial data in that way. That said, I don’t think anyone would make a tool to directly work with a PostGIS/Postgres data structure.

If you really want/need speed when working with PostGIS spatial data the Postgres server can do any spatial manipulation you can do in Knime, probably more. Just set up queries on the postgres server.

I know not the answer you wanted but I hope it helps.

P.S., did doing what I said speed anything up?