# Special spatial data types in database writer

**URL:** <https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992>\
**Category:** KNIME Analytics Platform\
**Created:** [July 12, 2017, 4:06pm UTC](https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992 "2017-07-12T16:06:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TKarge](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/eada6e/32.png) [@TKarge](https://forum.knime.com/u/TKarge)\
**Post date:** [July 12, 2017, 4:06pm UTC](https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992/1 "2017-07-12T16:06:39Z")

</div>

```
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.&nbsp;That should be possible with a parameterized query. Attempting that results in an error from PSQL, that not all parameters were initialized&nbsp;(Execute failed: org.postgresql.util.PSQLException: Für den Parameter 1&nbsp;wurde kein Wert angegeben.)

It even fails with fixed values&nbsp;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?

---

<div class="post-metadata">

**Author:** ![tobias.koetter](https://forum-cdn.knime.com/user_avatar/forum.knime.com/tobias.koetter/32/56844_2.png) [@tobias.koetter](https://forum.knime.com/u/tobias.koetter)\
**Post date:** [July 27, 2017, 9:47am UTC](https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992/2 "2017-07-27T09:47:56Z")

</div>

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_&nbsp;node. Attached you can find an example workflow that updates a column in a database via flow variables.

Bye

Tobias

- [parameterizedupdate.knwf](https://www.knime.com/sites/default/files/parameterizedupdate.knwf)

---

<div class="post-metadata">

**Author:** ![99idea](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/9/f05b48/32.png) [@99idea](https://forum.knime.com/u/99idea)\
**Post date:** [July 27, 2017, 11:11am UTC](https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992/3 "2017-07-27T11:11:44Z")

</div>

Gods, Thanks.

[**ซ่อมไอโฟน**](http://xn--c3c1abxjj6eb4b9kua9bj.com/)

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [June 23, 2023, 8:26pm UTC](https://forum.knime.com/t/special-spatial-data-types-in-database-writer/8992/4 "2023-06-23T20:26:55Z")

</div>