locked
Serializing SqlGeography datatype RRS feed

  • Question

  • Hi,

    I'm not sure if this is the right place to aske this question, but maybe someone can help me anyway.
    I need to create a Web Service method which takes an instance of the SqlGeography datatype as an
    argument. Unfortunately SqlGeography is not marked as serializable and thus cannot be serialized.
    So my question is: What would be the best way to use SqlGeography as an argument for a Web Service
    method?

    Best regards,
    Michael 
    Saturday, November 8, 2008 9:14 AM

Answers

  • Hi Michael,

    SqlGeography implements the IBinarySerialize interface, and if you use the Write() method, you'll be able to retrieve the same binary string we persist to disk in the engine.  This is the right value to use if you want a full-fidelity representation.

    Cheers,
    -Isaac
    Saturday, November 8, 2008 7:14 PM

All replies

  • Hi Michael,

    SqlGeography implements the IBinarySerialize interface, and if you use the Write() method, you'll be able to retrieve the same binary string we persist to disk in the engine.  This is the right value to use if you want a full-fidelity representation.

    Cheers,
    -Isaac
    Saturday, November 8, 2008 7:14 PM
  • Hi Isaac,

    thanks for your hint. That was exactly what I was looking for, but I have one more question on this. I need to insert the SqlGeometry into a database. Since my application uses Linq-to-SQL which does not support SqlGeography I think it might be a good workaround to use a stored procedure for this.
    Whats the best way to pass the SqlGeometry to the stored procedure? Can I pass the serialized object as a varbinary paramater or should I use WKT instead?

    Best regards,
    Michael 
    Saturday, November 8, 2008 11:02 PM
  • Hi Michael,

    You should be able to cast this binary value directly to a geography on the server.  I'm not a LINQ expert, but if you can call a stored procedure, you should be able to pass it in as a varbinary and coerce it in the procedure.

    For what you're doing, I'd avoid WKT if you can.

    Cheers,
    -Isaac
    Sunday, November 9, 2008 12:09 AM
  • Hi Isaac,

    id got it working using WKB. Why would you avoid using WKT?
    Within stored procedures I use the WKT representation of geograpgy
    instances to compare, if two of them are equal (@geo1.STAsText() = @geo2.STAsText()).
    Do you think this is the right way to find out if two  geography instances
    are equal?

    Best regards,
    Michael
    Monday, November 10, 2008 1:26 PM
  • There's nothing wrong with WKT - it's the format you'll see used most often on these forums and in Microsoft Books Online, it's pretty easy to understand. and it's an accepted standard. However, because it's text-based, WKT representations must be parsed before they can be created, and this makes WKT slower than WKB. For demonstrating or sharing code with people, WKT is great, but for passing code within applications, use WKB (or SQL Server native binary) instead.

    Also, you should NOT use WKT to compare whether two objects are equal as you show above - since WKT is text-based, it inevitably introduces rounding-errors when trying to represent coordinate values that were obtained using binary floating point methods. You should use the STEquals() method to compare whether two instances are equal, instead:

    @geo1.STEquals(@geo2) = 1

    However, even then, you should be very careful testing for equality of two binary instances... Isaac has a post on the imprecise nature of geometry that explains how two seemingly 'equal' values might not be as equal as you expect... (http://blogs.msdn.com/isaac/archive/2008/08/07/the-imprecise-nature-of-geometry.aspx)
    Monday, November 10, 2008 2:21 PM
    Answerer
  • I was thinking about the fact that WKT (and WKB) are not quite full-fidelity since they do not carry the SRID of the instance.  If this is assumed by your webservice---e.g., you take in some coordinates that are presumed to be in, say, WGS84---then this may not matter.

    Tanoshimi is correct on equality, by the way: STEquals is your friend, but be careful about exact comparisons.

    Cheers,
    -Isaac
    Monday, November 10, 2008 3:41 PM