Skip to main content
  • EN

Hello,

I'm trying to create geometry from Sql Server geometry Blob to shapefile. I'm trying geometryextractor but it is not working.

(I can not create geometry in Sql server; I can only read data).

Thanks for help

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?

 

 

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
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'?

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

 

 

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?

 

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

 

 

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

 

 

You mean the GeometryExtractor just extracts an empty geometry?

 

 

Is it possible to send some data? Just a couple BLOB's in a text file might be enough to see what is going on.

 

 

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.
@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 lBlobColumn] 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

 

 

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.

I tried it but I got error from geometrreplacer (rejected)

 

 

example of attribte of blob column

C2FFD20FBC8CCF11ABDE08003601B76904000000C08C935DE4181D41375658B7481303410000000000000000FA4A2959AA181D41EF9560B9BB13034100000000000000006324DE380E181D41A9F7511D3116034100000000000000004131146754181D41350713DC7A1603410000000000000000

@miladahmad, Please try "Encoded Polyline". I was able to get geometry from your example.
@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.

 

 

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?

 

I do not know if you get sopmething from that (I attached an example). thanks

 

example.txt

 

 

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.

 

 

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.

 

 

@dustin​ This is not available in SQLExecutor because of the custom geometry. You have to use the Intergraph GeoMedia SQL Server Warehouse reader.

Reply


Community Stats

30,351
Posts
115,342
Replies
38,085
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.