How to convert BLOB data stored in SQLite database into the SharpMap.Geometries.Geometry

Question

I have geometry data stored in the SQLite database, and datatype of column is BLOB.

I used the below method to convert the BLOB data into the sharp map geometry

SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse(< byte array >);

But it throws the exception "Byte order not recognized"

But if i use the sql server management studio database and save the geometry data in table using Image datatype then the i got the geometry without any error.

The only difference is of data type, in SQLite i have used data type "BLOB" and in sql server i have used the datatype "Image".

I also notice one more difference in size of byte array, if i use the SQLite database then i got the byte array size as "3812" and if i use the sql server database then i got the byte array size as "1902".

Does anybody knows the solution ?

I am working on below data :

0x01030000000100000076000000908FC85763BD2D41B5E1CEFAB9385841A8564425CABB2D412642A24FB83858411BFBC2F5D3BA2D4184A995ABBF385841CAD4637804BA2D415C8C795FA4385841F114538DDDB92D41F55448C2AC385841C64F734F30B92D41B249DC25CD385841A3CB7AAD4DB12D4199B5872A3B3858416D582E7839B02D41A9556428F637584143144991BBB02D416A06CFA4D53758417B22EADD21B12D41655331199F375841452E9D966AB12D4166ED16B88B3758419BB6676466B02D413D6180586D375841E89DECDF98B02D4191927111613758411C78DF662DB22D4170EA69F128375841D9D525280FB22D41A078FBDB103758415C485630A6B12D41F3EB2128EC3658419D97CCE77BB22D41C592B7E1CF365841831F9ECFB8B32D41589F8359E8365841A4599CA91AB42D4183458EF6F7365841BD50CAAF3AB42D41D8584F2DF836584115C52BF88AB42D41CB916AC0DF365841C9C54DE07BB32D41EE6958C1BF365841DE5A1651E3B22D415B4704FDA6365841D73CCC798FB22D41CB9E9E17963658415663F51F78B22D41E3403AA988365841B4235ECA76B22D41F0CA34EE7A36584188D446ECB9B32D41FB8ED8B5093658412812E3D8E8B32D41616C0DA2CC3558419DF34042BDB32D41EE235FC17B355841A36F009898B22D41054448484C355841E21AEBA9AFB42D41946B3A2761355841C463E28F4FB52D413FF3CA035F355841461A2E564BB52D4113CAB89257355841D2226CFEF8B62D41B71CC9EC153558419073855FC9B72D410A4B9247E1345841508668C7C4BC2D41E0B9D62FD7345841661D26E68ABE2D410E80C7D7AF34584149753FDA9AC02D418BDAADF075345841B50A90264DC32D41871EDC1F7D345841E0F2F43CA2C62D4107B6748781345841870AC042C0C62D41F61C18959D345841B238413BC0C72D4124BD21B4DC34584180D416141CC82D41E44457B4E934584175CA2F0700C92D4149867508343558414835F68B24CF2D418F8AD232F3345841D200559B07D02D414E65E5D5E1345841065A4DCB6CD02D4185CE4559E2345841254C3A15C5D02D415D1D78A14935584166C96D57F5D02D41C414E543623558415D6312F097D02D41C605342D94355841E65A332103D12D412A5960ABBA3558411194E61042D12D41F97012B6EC35584116DEF2091ED12D4186D8858FF03558412248444320D12D41EFAF11A6FD355841CB79634EEDD12D414275A2982D3658418C67CC2FE2D12D419E5D943153365841A93F65748BD12D418D280AC6643658419D1B2A62C0D12D41B19988E397365841934A32D73DD12D41BA8CCD9FAA365841646BF8D86DD22D414ED7D61EE136584146B4EFBE0DD32D417A3B6E9BE03658419F2A4C7030D32D41FCBCC386E53658413BB53AA930D32D4164780A6D1337584191D463BCD9D12D41B116E437083758419B2461F02CD12D415D301E26073758412FE24D704FD02D41DF24A4CB0C3758416BACD3FE50D02D411FFCC625173758413730503BD0CF2D41AAE124111C3758413061531920CF2D41A0D6E5442A3758411A6E1BE5A4CE2D414619DF4C3837584115D1D11FF4CC2D412A2B02E47B375841B0897DE34ECC2D41F7A6AFD36F3758418831353299CB2D4110590A15B03758414401543C3ACB2D4191DD8968C137584146EBF50830CA2D4158913B28D63758414ED056195EC92D4133420C50EF375841CF6C4EDAAAC82D41A57F0D5BF73758419E80ED363DC82D4120E370DA00385841A0D330BE85C72D4176B51AAC163858418132924D17C42D41ED5B97AB51385841DB1AC747F9C32D4163E87BEE4638584157EEAC6699C42D41C9D1BC2B30385841040137B0EFC42D4119CE642E2A3858417020BF9CB1C52D41BC74946A263858416A3227FE16C72D41489711A10E385841C9151581BDC92D414F1C52F8D637584124EAE0F5F3CA2D413D750DBCB237584147D5911AB2CB2D41B9E3E10F743758417635C66FB1CB2D4140FA886365375841632C30082CCA2D41CE0050B46437584105493CCA11CA2D413CB74FA156375841FE65D622B6CA2D414A1C58C131375841C950F9B0B5CA2D41B4443CB92B375841AD2913B768CB2D414719112023375841A9A4228E94CA2D4133F15AA90F375841AEE3024BA5C82D41EC089804EA365841F76822311FC82D41CC821BEE01375841DE1EB75E2AC62D418150AD64E0365841C039F774E3C42D41551E26A4CE3658414520FB2E0CC42D415933F721D336584151443641D7C32D41FE09F7AA0437584112C708BA1AC12D41F123AB9F14375841E933DCD86CBF2D412A142AE92A375841A3BBE95204BF2D41538E58422037584179B2EE56F2BD2D416FA23E89EF365841F8B58C69BFBD2D41387D57ABF4365841EABF0EE24EBD2D41C63A39B302375841E83C19224DBC2D410263BB2A333758411530B6EA54BC2D41C672B000853758413E00C81F41BC2D4171582088943758416914F51292BC2D41B3EC88989837584110B8EC661EBD2D41582E965DC13758416CE832F109BD2D41A36943A90C3858419923E1491BBD2D41161BD1B918385841E17A43A53ABD2D4126D39FFB2038584123EF3F59FEBD2D41EC01CAEC313858419D044C88A4BC2D410422669584385841908FC85763BD2D41B5E1CEFAB938584100000000

@BugFinder i am having geometry in sql server database previously, which was provided by our client. But for fullfilling the requirement i have to use the SQLite database, so i use option "Save Result As.." in sql server and stored the data in the CSV file. After that i import the tables in SQLite using those CSV file. — pankaj, Feb 12 '18 at 13:01

score 0 · Answer 1 · answered Feb 12 '18 at 18:45

0

You have stored the hexadecimal representation of the value as text.

CSV files cannot contain blobs; you have to export the data in some other format. Try as SQL, and then convert the blob literal from the SQL Server format, i.e.:

INSERT INTO ... VALUES (... 0x0123...)

into the SQLite format, i.e.:

INSERT INTO ... VALUES (... x'0123...')

answered Feb 12 '18 at 18:45

CL.

173,858
17
217
259

i need to store the data in SQLite only for fulfilling the requirement, there is only one way to import the data in SQLite that is CSV. After completion of data import, there is no data type displayed for that column in which the hexadecimal data is stored, so i changed the datatype to BLOB after the import. Is there any possibility of importing data from SQL to SQLite ? – pankaj Feb 13 '18 at 04:02
You cannot use CSV. Export as SQL, or convert the CSV into SQL. – CL. Feb 13 '18 at 08:00

score 0 · Answer 2 · answered Feb 13 '18 at 11:21

If CSV is your exchange format you should transform your geometry into a text representation using e.g. the .STAsText() function on the geometry.
When you import the CSV text (by writing own code) into your SQLite database you sould use a Well-Known-Text parser transforming that text inot a geometry and from there into a Well-Known-Binary blob that can be handled by SharpMap. This can be done using (e.g.) NetTopologySuite's Wkt reader:

var rdr = new NetTopologySuite.IO.WktReader();
var geom = rdr.Read(csvValue);
var blob = geom.AsBinary();

thanks to give your time, the issue was storing the data in the CSV, while storing the data from sql server to CSV, it converts the image data in the Hexadecimal format. so i need something like directly converting the sql server data in SQLite. I have posted my answer. Thanks :) — pankaj, Feb 15 '18 at 04:39

score 0 · Accepted Answer · answered Feb 15 '18 at 04:44

The issue is solved.

What happened is when i am storing the sql server geometry data in CSV file, it stores in the hexadecimal format. And when i am importing that data in SQLite, it stores geometry in hexadecimal format only not in the BLOB format.

So i search something like directly converting the sql server geometry data in the SQLite BLOB data.

So i found this link : https://www.codeproject.com/Articles/26932/Convert-SQL-Server-DB-to-SQLite-DB

It directly convert the sql server table into the SQLite table, so that the geometry data in sql server is automatically converts into the BLOB, and there is no issues while converting that BLOb data into the SharpMap Geometry.

How to convert BLOB data stored in SQLite database into the SharpMap.Geometries.Geometry

3 Answers3