locked
Dear SQLDataReader, stop converting my geometry to WKT! RRS feed

  • Question

  • Is there a way to convert the geometry datatype to string? I don't mean using the ToString() method, I mean to string as in writing it to a file. I'm attempting to write Insert statements to file (that can later be used to insert geometrys into a seperate table). I am using a datareader to retrieve the original geometrys from a table and then write the results to file, but the reader is converting the geometry to Polygons (WKT). It appears to be triggering the ToString method?? This is despite me using a stored proc to retrieve the results, and me not using the Microsoft.SqlServer.Types assembly. Even casting the datatype to Char in SQL Server triggers the method.

    Where's the conversion happening and how do I stop it?

    And why are all the forum contributers in Australia :) ?

    Thanks

    Drew
    Tuesday, June 23, 2009 1:57 PM

Answers

  • Hi Drewster,

    The problem is that geometry data is binary data, so you can't simply write it to a text file (which, by definition, holds text rather than binary data).
    Is it that you want to store the hexadecimal representation of binary data as text? i.e. you want the text string "03000000010C000000000000F03F0000000000000040" to represent the geometry Point(1,2,3)?

    If so, you can do something like this: (I'm sure that there are more elegant ways, but this is the first to spring to mind)
    SqlConnection sqlConn = new SqlConnection("Data Source=ecco; Trusted_Connection=Yes;");
    sqlConn.Open();
    SqlCommand sqlComm = new SqlCommand("SELECT CAST(geomColumn) AS varbinary(max)) AS geometry;", sqlConn);
    SqlDataReader r = sqlComm.ExecuteReader();
    StreamWriter sw = new StreamWriter(@"C:\geometry.txt");
          
    while (r.Read())
    {
      string stringValue = "";
      byte[] g = (byte[])r["geometry"];
      for (int i = 0; i < g.Length; i++)
        stringValue += string.Format("{0:X2}", g[i]);
    
      sw.Write(stringValue);
    }
    
    r.Close(); sw.Close(); sqlConn.Close();
    The text file will now contain the hexadecimal representation of each byte in the geometry instance.... is that what you wanted?
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by The_Drewster Wednesday, June 24, 2009 11:58 AM
    Tuesday, June 23, 2009 10:49 PM
    Answerer

All replies

  • What format would you like the geometry to be serialized as for use in your INSERT statement?

    Data Types in SQL Server must support serialization to and from a string by contract, and WKT is the format used by ToString() and Parse() for the Geometry data type.  Values contained within INSERT statements are inheritantly strings, so, to me, having the geometry defined as WKT in my INSERT statement (so that is can be directly used by Geometry's Parse() method) would be preferred.
    Tuesday, June 23, 2009 5:23 PM
  • Hi Jason,

    Thanks for replying. I need it reinserted as Geometry. The reason is that I am combining new columns from different data sources and manipulating these columns before writing them as the Insert statement along with the Geometry column. I want them combined into one table, with one of those columns being the original Geometry column, so that I can use the Geometry datatypes methods and return the coressponding fields from the other columns in a query. Surely there is a way to write the geometry string to file?
    Tuesday, June 23, 2009 5:51 PM
  • Dear Drewster,

    Have you tried casting the geometry result to varbinary(max)? It won't alter the fidelity of the stored value, but it should stop the WKT ToString() kicking in...
    As Jason says, I would rather have INSERT statements that specified WKT (or, actually, WKB) strings in combination with an SRID rather than using the SQL Server proprietary format. That way, I can tell at a glance what sort of values I'm about to insert before

    p.s. Not all the forum contributors are in Australia! I'm currently here visiting tanoshimi in sunny England.

    Love,

    SqlDataReader

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, June 23, 2009 5:57 PM
    Answerer
  • I stand corrected! I thought your pal was a Brisbanite. Anyway, forget about him.

    So I just tried your suggestion. Using...

    SuburbGeometry = reader[

    "TheGeometryField"].ToString();

    returned the string "System.Byte[]" for every row.

    I tried casting the read field to System.Byte

    but this time a load of integers appeared for every row ??

    Incidently, the Insert statement doesn't have WKT because it has a field of google encoded coordinates that have themselves been converted from WKT. The geometry field is used to do the calculations whilst the corresponding encoded strings are returned.

    Tuesday, June 23, 2009 7:07 PM
  • Drewster,

    I'm still trying to understand your problem (i.e., what the end goal is).

    But, despite my confusion, perhaps showing several examples of how to construct INSERT statements using WKT might help to clear up the situation.

    CREATE TABLE sample (id INT IDENTITY(1,1), shape Geometry)
    
    INSERT INTO sample (shape)
    VALUES ('POINT(1 2)')                     -- This implicitly calls Parse()
    
    INSERT INTO sample (shape)
    VALUES ('LINESTRING(1 2, 3 4, 5 6)')      -- This implicitly calls Parse()
    
    INSERT INTO sample (shape)
    VALUES (geometry::STGeomFromText('POINT(5 6)', 0))
    
    INSERT INTO sample (shape)
    VALUES (geometry::Parse('POINT(7 8)'))    -- SRID of 0 is implied
    
    
    SELECT id, shape FROM sample
    
    SELECT id, shape.ToString() FROM sample
    
    
    

    The first SELECT statement (without ToString()) should return a binary serialized version of the geometry (i.e. a byte[] array, but it is NOT WKB).  I think your original question was in regards to code like this not returning a byte array, to which tonoshimi suggested casting as a varbinary(max) in order to force the binary serialization. 

    The second SELECT statement (with ToString()) should return WKT.  This would be suitable to include in any INSERT statement, as demonstrated.

    Tuesday, June 23, 2009 9:07 PM
  • Hi Jason, can I just say thanks for taking the time to help me out.

    I'll go into a little more detail on what I am trying to achieve.

    I have Table1 that has a geometry field. I want to use .Net to extract that field i.e. Select geometryField from Table1, and write it to a text file. That's it. I just want to write it as it is, not converted to wkt, to a text file. I am also (though irrelvant to this post) converting the geometry to wkt, as the idea is to build a SQL Insert statement such as "Insert into Table2 values (geometry, wkt, andOtherStuffRelevantToThisRow). The problem is that in .Net, the datareader is not writing the geometry as "0xE61000..." but as WKT even though I have not asked it to. I've not called the ToString() method at all, I've just asked for the geometry field as it is.

    Tuesday, June 23, 2009 9:27 PM
  • Hi Drewster,

    The problem is that geometry data is binary data, so you can't simply write it to a text file (which, by definition, holds text rather than binary data).
    Is it that you want to store the hexadecimal representation of binary data as text? i.e. you want the text string "03000000010C000000000000F03F0000000000000040" to represent the geometry Point(1,2,3)?

    If so, you can do something like this: (I'm sure that there are more elegant ways, but this is the first to spring to mind)
    SqlConnection sqlConn = new SqlConnection("Data Source=ecco; Trusted_Connection=Yes;");
    sqlConn.Open();
    SqlCommand sqlComm = new SqlCommand("SELECT CAST(geomColumn) AS varbinary(max)) AS geometry;", sqlConn);
    SqlDataReader r = sqlComm.ExecuteReader();
    StreamWriter sw = new StreamWriter(@"C:\geometry.txt");
          
    while (r.Read())
    {
      string stringValue = "";
      byte[] g = (byte[])r["geometry"];
      for (int i = 0; i < g.Length; i++)
        stringValue += string.Format("{0:X2}", g[i]);
    
      sw.Write(stringValue);
    }
    
    r.Close(); sw.Close(); sqlConn.Close();
    The text file will now contain the hexadecimal representation of each byte in the geometry instance.... is that what you wanted?
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by The_Drewster Wednesday, June 24, 2009 11:58 AM
    Tuesday, June 23, 2009 10:49 PM
    Answerer
  • Oh! I see.

    Is it the hexidecimal representation that is displayed in the query results pane when selecting a geometry field? If so, then yes, that's that I wanted! I'll give it a try tomorrow and report back. Many thanks,

    Drew
    Tuesday, June 23, 2009 11:02 PM
  • Genius!  By inserting 0x before each hex string, I can insert the geom as it from a text file without have to make any conversions myself. I have a slightly better understanding now too though it does lead to another question. I've not worked with varbinary before so forgive me, but if this datatype holds binary data, is converting it to hex the only way of writing an Insert statement (in a query screen or a file) for this binary data. I think I'm still missing a link in the chain.
    Wednesday, June 24, 2009 9:08 AM
  • An INSERT statement is simply text that is sent to SQL Server where it is then parsed and executed.  Therefore, all data that you include in that INSERT must be in a textual representation, and for binary data, this means using hexidecimal strings (but don't surround the hex with quotes).

    I guess I never considered using the binary serialized data directly within DML before, so I couldn't envision what you were trying to do or why you just didn't use WKT.  But, a quick test shows that it does work, so... cheers!

    The following instantiate identical geometry objects:

    declare @g1 geometry = 0x00000000010C000000000000F03F0000000000000040
    declare @g2 geometry = 'POINT(1 2)'

    Your INSERT statement, therefore, would resemble:

    INSERT INTO sample (shape)
    VALUES (0x00000000010C000000000000F03F0000000000000040)
    • Edited by JasonFollas Wednesday, June 24, 2009 11:56 AM
    Wednesday, June 24, 2009 11:54 AM
  • Thank you both for your help.
    Wednesday, June 24, 2009 11:58 AM