How to convert from SQL Server geometry BLOB to shapefile?

Hi @miladahmad,

 

Typically, when BLOB data is stored in a database, it is for storing large files like images and multimedia. May I ask if you know what the BLOB data represents in this case?

 

 

it should be polyline ( I have no idea why they do it like that)

 

 

Equal to this problem?

 

https://gis.stackexchange.com/questions/160629/how-to-convert-from-sql-server-geometry-blob-to-something-else

thanks, I saw it. I'm searching a solution with FME (As I mentionned above, I can only read data from SQL server and I can not create table in in Sql Server data base).

 

 

Have you tried using the GeometryExtractor transformer with the geometry encoding as 'OGC Well Known Binary'?

I did not explained it correctly, sorry. There is no generated geometry in the sql sevrer. I'm tying to get the blob column with FME to generate the geometry (the geometry information is in the blob column). thanks any way for your help

 

 

miladahmad , I noticed that there is even an option 'Microsoft SQL Server Binary' you can select. If the BLOB is only the geometry, than that should generate a geometry.

 

 

So the geometry definition is just a part of the BLOB? In that case, I would recommend to try filtering it with regex and/or a StringSearcher transformer. If you are unfamiliar with regex, you could give me an example and I can try to look at it?

 

thanks for your time. I tried geometry extractor with Microsoft SQL Server Binary then I use GeometryReplacer (I do not if this is the correct one to use after extractor) but I got the table does not include a spatial column Geometry will not be read

 

 

Hi @miladahmad, how is the geometry stored? Is it WKT or SQL Server Binary? You can try using the GeometryReplacer which allows you to set the geometry encoding and select which attribute stores the geometry.

@miladahmad It might help if you could identify the application that created the table with the 'blob' field in your SQl Server database. That might help the community determine how to unscramble the blob for you.

they said that is blob gemedia. I tried to connect with Intergraph GeoMedia SQL Server Warehouse but it did not work. So I tried to convert blolb to binary by sql executor

 

select cast(geomconvert.geombinary as geometry) as geom from

 

(select

 

cast(temp.wkb as varbinary(max)) as geombinary

 

from

 

(select [BlobColumn] as wkb from dbo.[MyTable]

 

) as temp

 

) as geomconvert

 

and I got as error

 

com.microsoft.sqlserver.jdbc.SQLServerException: A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.ArgumentException: 24100: The spatial reference identifier (SRID) is not valid. SRIDs must be between 0 and 999999.

 

System.ArgumentException:

 

at Microsoft.SqlServer.Types.SqlGeometry.set_Srid(Int32 value)

 

at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r)

 

at SqlGeometry::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )

 

 

may be that will indicate the problem

 

 

example of attribte of blob column

C2FFD20FBC8CCF11ABDE08003601B76904000000C08C935DE4181D41375658B7481303410000000000000000FA4A2959AA181D41EF9560B9BB13034100000000000000006324DE380E181D41A9F7511D3116034100000000000000004131146754181D41350713DC7A1603410000000000000000

@miladahmad, Please try "Encoded Polyline". I was able to get geometry from your example.

can you please write what you did? I already tested it and it did not work.

 

 

Sorry to bring this up from the past, but was there a resolution to this issue? I'm trying to read data including geometry stored in an Intergraph/Geomedia SQL database using SQLExecutor Microsoft SQL Server Spatial format. Intergraph SQL is not an option in the SQLExecutor for some reason. I have tried the 'Encoded Polyline' method in the GeometryReplacer, but the geometry is not correct.