Special spatial data types in database writer

KNIME Analytics Platform
#1 
Today I tried inserting some geographic data into a PostGIS-PostgreSQL database using KNIME. I have x- and y-column in my KNIME workflow and I want to combine them into one "coordinate" column of the PostGIS Type "Point" in my database.

That column accepts only spatial data (geometry data) of type "Point". This is a special PostGIS datatype which can be constructed from an x- and y- coordinate using the function "ST_Point(x,y)". A respective query would look like

UPDATE geotable SET coordinate = ST_Point(1,2) WHERE id = 1

or with parameters from my KNIME input table, respectively:

UPDATE geotable SET coordinate = ST_Point($x$, $y$) WHERE id = $id$

Now of course, that query should be parameterized with the values from my table in KNIME. That should be possible with a parameterized query. Attempting that results in an error from PSQL, that not all parameters were initialized (Execute failed: org.postgresql.util.PSQLException: Für den Parameter 1 wurde kein Wert angegeben.)

It even fails with fixed values like in the first example, due to an empty result (fails with "org.postgresql.util.PSQLException: Die Abfrage lieferte kein Ergebnis.").

If I add a "dummy select" like suggested elsewhere (as soon as I find it I will add the link):

SELECT * FROM geotable;
UPDATE geotable SET coordinate = ST_Point(1,2) WHERE id = 1;

The sql query jields multiple result sets (org.postgresql.util.PSQLException: Die Abfrage ergab mehrere ResultSets).

Actually it would be nice if writing such datatypes would be possible on the Database Writer node, for example by allowing a custom data type instead of string, where the user is responsible for adding the type decorations. Is there any option to accomplish what I am looking for, which I have overlooked so far?

#2

Hi,

have oyu tried to reverse the order of the statements in the Parameterized Query node? E.g.

UPDATE geotable SET coordinate = ST_Point(1,2) WHERE id = 1;

SELECT * FROM geotable where 1=0;

I can not say if it works or not since it depends on the driver implementation if it supports a DML statement within a query statement.

Another option would be to use flow variables to change the coordinates and the id like your query:

UPDATE geotable SET coordinate = ST_Point($x$, $y$) WHERE id = $id$

The flow variables would be created by using the Table Row to Variable Loop Start node. Attached you can find an example workflow that updates a column in a database via flow variables.

Bye

Tobias

#3

Gods, Thanks.

ซ่อมไอโฟน