Question

SQLCreator only reading geometry column as geometry sometimes

ebygomm
Rank
Userlevel 1
Badge +10

I'm using an SQL Creator to return data from three tables (Microsoft SQL Server), one of which contains a geometry column.

 

Initially, I was just joining two tables and this all worked as expected with the geometry being correctly displayed within FME. When i amended the SQL to include a third table, the geometry column seems to have stopped being interpreted as geometry and is now displayed as an attribute containing binary data.

 

A bit more investigation, if I limit the fields being returned by the SQL creator, the geometry is interpreted correctly (bottom SQL creator). If i attempt to return all fields (explicitly naming or using *), the geometry field is returned as an attribute

 

image 

Additional information, only the table I'm attempting to get the geometry from has a geometry column.

 

Am I missing something obvious?

 

FME(R) 2020.2.2.0 (20210111 - Build 20817 )

10 replies

david_r
Rank
Userlevel 4

Hopefully someone else can shed some light on how exactly the SQLCreator detects geometry columns, but I've always found it a bit like a black box where I'm not fully in control. I therefore usually cast the geometry to e.g. WKB in the SQL and use the GeometryReplacer to decode it in the workspace. The upside is that it is also very transparent what is going on, especially if there are several geometry columns involved in the SQL. The downside is that it can sometimes be fairly slow to convert e.g. large and complex areas back and forth. But for simpler geometries it's usually not really noticeable.

virtualcitymatt
Rank
Userlevel 4
Badge +26

I can't comment for SQL Server but this method works for PostGIS (doesn't work if PostgreSQL)

What I find work for me is the following.

 

In the SELECT statement be sure to cast to WKB:

and if the column name is not 'geometry' also make sure to select as, something like this works well for me: 

SELECT ST_asBinary(<geom_column>) as geometry FROM <table>

If the cast does not happen OR the column name isn't 'geometry' then it doesn't work. This works both in the SQLCreator and the SQLExcecutor as long as you're using the spatial version of the database format (e.g., PostGIS vs PostgreSQL).

ebygomm
Rank
Userlevel 1
Badge +10

Is this a known issue that's logged somewhere then?

 

Because it makes no sense to me that returning extra attributes in an SQL query would suddenly mean that the geometry is not interpreted correctly. Not working at all would be a better outcome, than only working sometimes. I've never encountered any issues like this when using SQLCreators and SQLExecutors with Oracle DB.

 

For this particular workflow, it's probably going to make more sense for me to read the geometries with a native reader and then do the join within FME, rather than cast to WKB and rebuild.

 

@virtualcitymatt are you saying that your method with PostGIS doesn't require the geometry replacement step afterwards?

virtualcitymatt
Rank
Userlevel 4
Badge +26

Is this a known issue that's logged somewhere then? 

 

Because it makes no sense to me that returning extra attributes in an SQL query would suddenly mean that the geometry is not interpreted correctly. Not working at all would be a better outcome, than only working sometimes. I've never encountered any issues like this when using SQLCreators and SQLExecutors with Oracle DB.

 

For this particular workflow, it's probably going to make more sense for me to read the geometries with a native reader and then do the join within FME, rather than cast to WKB and rebuild.

 

@virtualcitymatt are you saying that your method with PostGIS doesn't require the geometry replacement step afterwards?

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

ebygomm
Rank
Userlevel 1
Badge +10

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

I've tried using .STAsBinary() which I think is the correct syntax for SQL Server but it still requires the geometry replacement step. The geometry column does have a non-standard name (imo) which I guess might have some bearing, but wouldn't really explain why it works some of the time

ebygomm
Rank
Userlevel 1
Badge +10

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

I've just discovered that using .STAsBinary() is not possible as it drops the z values

virtualcitymatt
Rank
Userlevel 4
Badge +26

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

oof, that's not nice - I don't see that issue with PostGIS - super strange

ebygomm
Rank
Userlevel 1
Badge +10

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

I think it's a mssql thing, .STAsText() info says it won't return z or m values. No mention about .STAsBinary() but it appears to not return z either.

 

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stastext-geometry-data-type?view=sql-server-ver15

david_r
Rank
Userlevel 4

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

You'll want to use the extended method AsBinaryZM:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/asbinaryzm-geometry-datatype?view=sql-server-ver15

There's also the AsTextZM:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/astextzm-geometry-data-type?view=sql-server-ver15

ebygomm
Rank
Userlevel 1
Badge +10

Right - no replacement needed in the workspace, geometries are built directly, you can also use 

SELECT *, ST_asBinary(<geom_column>) as geometry FROM <table>

What bothers me about this though is that you need to know the name of the geometry column and you also get the geometry column coming in as an attribute as well here. Omitting the * will not result in the extra column though which is nice. 

 

But now that I'm retesting I'm finding that it seems to be working all the time now with PostGIS. But i was getting unexpected results in the past with the SQLExecutor. 

 

 

. But it does indeed seem like a bug what you've found, or at least bad behaviour. Perhaps @steveatsafe​ can confirm this. It should be consistent. 

 

 

Grand, thanks. I'd only done some light googling

Reply


Community Stats

28,666
Posts
109,343
Replies
36,535
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.