Skip to main content
Question

Microsoft SQL Server geometry issues

  • June 5, 2020
  • 6 replies
  • 491 views
A
Forum|alt.badge.img+1

Hi,

I am trying to read point geometry from a Microsoft SQL Server database and the geometry is displaying in the 'Geometry' attribute like the following example: 'C0FFD20FBC8CCF11ABDE08003601B769D9CEF75331C9F0405EBA490C1EABE44015AE47E17A746140'.

I would like this geometry to display in the inspector but am having trouble working out how to do this.

I have tried using the 'GeometryExtractor' transformer, first setting the 'Geometry Encoding' to 'FME Binary'. But this results in '3160A883220000000000' being output for every single record in the _geometry attribute and no geometry in the inspector.

When I tried setting 'Geometry Encoding' to 'Microsoft SQL Server Binary' I got 'FFFFFFFF' for very record in the _geometry attribute and again no geometry in the inspector.

Does anyone know what else I could try? It's my first time reading from Microsoft SQL Server in this way..

Thanks!

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.

6 replies

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • June 5, 2020

The easiest is probably to use the SQL Server Spatial reader, which should automatically convert the geometry BLOB to an FME geometry for you.

If that is not an option, you can cast the BLOB to WKB (well-known binary):

select geometry.STAsBinary() as geometry from my_table
Then use the GeometryReplacer on the 'geometry' attribute, configured for OGC Well-Known Binary. That should return the proper FME geometry.
A
Forum|alt.badge.img+1
  • aquamarine
  • Author
  • 271 replies
  • June 5, 2020

Hi @david_r Thank you for responding so quickly. I tried to use the SQL Server Spatial Reader but no geometry came out of it...

So l I have done the following as per your advice for casting the BLOB to WKB. However I am getting the following error. I probably don't have the syntax right...

 

 

Thank you!

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • June 5, 2020

Hi @david_r Thank you for responding so quickly. I tried to use the SQL Server Spatial Reader but no geometry came out of it...

So l I have done the following as per your advice for casting the BLOB to WKB. However I am getting the following error. I probably don't have the syntax right...

 

 

Thank you!

Exactly what is the datatype of the geometry column?

A
Forum|alt.badge.img+1
  • aquamarine
  • Author
  • 271 replies
  • June 5, 2020

Exactly what is the datatype of the geometry column?

The datatype is 'image'

david_r
Celebrity
  • david_rCelebrity
  • 8392 replies
  • June 5, 2020

Aha, if the 'geometry' column's datatype is image, then it's not native to SQL Server Spatial and you cannot use those functions on that column.

It seems that the image datatype is an older (deprecated) BLOB datatype, and you need to know the contents before you can decode the contents properly. Do you have any documentation or colleagues with knowledge about the contents? In principle, the column could contain any binary object, including rasters.

One thing to try is to read the column as-is, then use the GeometryReplacer set to 'Encoded polyline' and see if that works.

A
Forum|alt.badge.img+1
  • aquamarine
  • Author
  • 271 replies
  • June 8, 2020

Aha, if the 'geometry' column's datatype is image, then it's not native to SQL Server Spatial and you cannot use those functions on that column.

It seems that the image datatype is an older (deprecated) BLOB datatype, and you need to know the contents before you can decode the contents properly. Do you have any documentation or colleagues with knowledge about the contents? In principle, the column could contain any binary object, including rasters.

One thing to try is to read the column as-is, then use the GeometryReplacer set to 'Encoded polyline' and see if that works.

HI @david_r . Thanks for your reply! I have tried the 'GeometryReplacer' set to 'Encoded polyline' but the features were sent to the rejected port. However I have spoken to my colleague and he gave me the following information:

"Polylines:

20 byte binary header(0xC0FFD20FBC8CCF11ABDE08003601B769 plus 8 bytes of length details then 64bit binary (8Byte). Coordinates one after the other in sequence X, Y, Z . Using Little ending.

 

Point / Nodes:

16 byte binary header (0xC0FFD20FBC8CCF11ABDE08003601B769) then 64bit binary (8Byte). Coordinates one after the other in sequence X, Y, Z . Using Little ending."

 

I haven't really used binary format so I'm not sure how to use this information to extract the geometry...

Thanks,

 

Community Stats

32,074
Posts
121,790
Replies
39,702
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.