I am trying to access the GEOM column from a MSSQL 2012 Database using FME 2017. I have tried with the ADO, JDBC_Nonspatial und Spatial drivers. In table-view the column GEOM is never exposed. Do I have to manually expose the geometry column? If so, how?
Question
Why is the column GEOM not displayed when connecting to MSSQL database?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
I just did a couple of tests and can confirm that the geometry column doesn't show in any of the readers. I suspect the "geometry" data type isn't supported as a regular column by FME.
However, you can use a SQLExecutor as a workaround:
select *, [geom].STAsText() as [geom_wkt]
from [my_user].[my_table]
This will include the WKT (OGC Well-Known Text) representation of the "geom" column in a new column called "geom_wkt", which will be visible in FME.
In FME 2018 I can get FME to recognize the GEOM column using the SQL Server Non-Spatial ADO reader, but only as binary. SQL Server JDBC non-spatial reader does not seem to recognize the GEOM column. So I think David's suggestion to use SQLExecutor and keep the geometry as WKT using STAsText() is the way to go.
+26- Contributor
I agree with the SQL statement by @david_r, but I would use the SCLCreator transformer instead of a SQLExecutor.
I agree with the SQL statement by @david_r, but I would use the SCLCreator transformer instead of a SQLExecutor.