Best practice to get geometry column from MS SQL DB

KNIME Analytics Platform
1

Hi there
I’m looking for best practices for reading geometry data from MS SQL DB.
I connected with JDBC, and the geometry column was read in BLOB format. I can’t figure out how to transform it into a geometry column.
There is properly a smart and easy way to do it.
Best regards Oskar

2

Hello @Oskar_Mule,

What format is your incoming data as? If you can get it to type WKT, you can use node below:

Hope this helps,
TL

3

Hi Thor
Thanks for the suggestion.
I use “Binary Objects to Strings” but than gives me a result that can’t be translated by “WKT to Geomatry”.
Maybe it’s wrong to use “Binary Objects to Strings” Is there a better/correct way to do it?

image
image1033×584 64.2 KB

Kind regards, Oskar

4

@Oskar_Mule,

Yes, you would not want to use the Binary Objects to Strings. You want to maintain the WKT output. But, you may need to do a certain select statement in order to properly output as a WKT.

Since you are using Microsoft SQL Server, try using this function below on the column holding your geometry instance (It will output it as WKT):

Here is a sample of how it may look:

SELECT Geometri.STAsText() AS Geometri
FROM YourTable;

Hope this helps,
TL