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 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 )

SQLCreator only reading geometry column as geometry sometimes

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.

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).

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?

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.

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

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

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

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

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

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

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded