Hello,
I'm querying a SQL spatial table using a native spatial query. I seem to fail to output the geometry - when I run the query and connect inspector, I can only see the attribute table but no geometries ("There is no geometry in this View" message).
Geometry column in SQL DB is called SHAPE, my query states SELECT * FROM...
I'm quite new to this topic. Any help is much appreciated, thanks Moni
Is this MS SQL Server Spatial, perhaps?
If so, you might try something like this:
select SHAPE.STAsBinary() as WKB, *
from ...After, expose the attribute "WKB" and use a GeometryReplacer (encoding = "OGC Well Known Binary") to convert it into an FME geometry.
Hi @honkovam, I suspect that you have left 'Initiator Geometry Only' (default) in the 'Combine Geometry' parameter. If so, change it to 'Result Geometry Only'.
Hi @david_r,
yes - it's MS SQL Server Spatial. I followed the PostGIS DB example here:
https://knowledge.safe.com/articles/19649/performi...
SELECT * FROM public."PostalAddress" as ap WHERE ST_Within(ap.geom,ST_GeomFromText('@Value(_geometry)',26910))=TRUE
There is no GeometryReplacer in the example so I thought I was doing something wrong.
I've added this transformer into my workflow - it's using geometry from SHAPE column in my MS SQL Spatial DB and converts it into an FME Binary (Help reads "The most efficient and truest representation of the geometry is FME Binary, and this should be used in most cases.")
capture.jpg
Thank you very much for your help.
Hi @honkovam, I suspect that you have left 'Initiator Geometry Only' (default) in the 'Combine Geometry' parameter. If so, change it to 'Result Geometry Only'.
Yes, Takashi - I have changed the default setting to Result Geometry Only. No geometry returned, only a "flat table"
Yes, Takashi - I have changed the default setting to Result Geometry Only. No geometry returned, only a "flat table"
If you were able to create geometries directly from the SHAPE column values using the GeometryReplacer with 'FME Binary' option, the SHAPE column might not be a geometry type column conforming to the native Microsoft SQL Server specification.
Check if the MS SQL Server (Spacial) Reader can read geometries from the table. Or, if you have 'SQL Server Management Studio' (a Microsoft software for SQL Server database management), have a look at the column definition with the tool.