SQL Spatial database - using the Spatial queries in SQLExecutor

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.

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.