Skip to main content
Question

Spatial query wont fetch geometric data


Hi all

 

 

I'm about to make a spatial query from two tables. One is a SQL-Creater with a MSSQL Spatial driver. The other is a WFS with polygons. (I have to read from MSSQL by SQLCreater because of a complex nested query that won't fit into the MSSQLSpatial Reader. I've tested the query in MSSQL Server Management Studio and it works just fine with geometry attached).

 

 

I've loaded the PointOnAreaOverlayer, however it doesn't seem to fetch the GEOMETRI-column in which the geographic data is.

 

Is it a setting in the SQLCreater or in the POAO that has to be made for it in order to make a spatial join?

 

 

5 replies

itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • April 20, 2020

Hi @chemichael,

When you view the data in de Data Inspector, do both inputs have the same coordinate system and are aligned (e.i you see them both on top of each other)?

If not you probably have data in different coordinate systems and there fore the overlay will fail.

Hope this helps.


david_r
Celebrity
  • April 20, 2020

In the screenshot above there are no features exiting the SQLCreator...?


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • April 20, 2020

I'm not familiar with how geometry is stored in MSSQL. Have you tried using a GeometryReplacer with Geometry Encoding set to Microsoft SQL Server Binary?


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • April 20, 2020
david_r wrote:

In the screenshot above there are no features exiting the SQLCreator...?

I'm assuming the 30,641 features exiting the Rejected port came from the SQL query originally...


david_r
Celebrity
  • April 20, 2020

One way to get the geometry out of SQL Server Spatial and into FME using the SQLExecutor, is to cast the geometry column to binary using the STAsBinary method, e.g.

select my_column1, my_geometry_column.STAsBinary() as geom_wkb
from my_table

You should then expose "geom_wkb" in the SQLExecutor, then follow with a GeometryReplacer set to OGC Well-Known Binary to extract the geometry before proceeding as usual.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings