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
Question
How to convert from SQL Server geometry BLOB to shapefile?
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.
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.
+6Hi @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?
+64
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
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
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
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.
+21- Safer
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.
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
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.
example of attribte of blob column
C2FFD20FBC8CCF11ABDE08003601B76904000000C08C935DE4181D41375658B7481303410000000000000000FA4A2959AA181D41EF9560B9BB13034100000000000000006324DE380E181D41A9F7511D3116034100000000000000004131146754181D41350713DC7A1603410000000000000000
+21- Safer
@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.
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?
example.txt
+31- Influencer
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.