Skip to main content
  • EN

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!

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.

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!

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?

Exactly what is the datatype of the geometry column?

The datatype is 'image'

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.

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,

 

Reply


Community Stats

30,194
Posts
114,613
Replies
37,855
Members
Terms & ConditionsCookie settings
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.