none
geography and the entity framework

    Question

  • I am trying to use the EF to read data from my gps data logger into sql2008 db, and later manipulate it on maps.
    I managed to read the geography column from the database with the help of
    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=4019816&SiteID=1

    but I couldn't insert the data into the database because the serialization of the SqlGeography object does not keep the srid info. When the SQL server tried de-serializing it back into geography data type it threw an exception that I should put in a valid srid. My guess is that it used some default value (probably 0) which is not valid for geography. How can I somehow contol the srid for the de-serialization process?
    Another problem I have with the binary approach is that the
    STBinary method just ditches the Z and M values of the SqlGeography object (what is that good for?), and I do want to keep those values in my db.

    I then tried switching to a text mode. This way I was able to insert data (ToString keeps the Z and M), and save it to the database, but in this way I can't read data from the it. When trying to select from the table, the server can automatically convert the geography into varbinary. not into nvarchar.

    What would be the simplest way to read geography data from the db, have it as SqlGeography in my c# code, and have the option to add new data to the table as well? Wasn't that the whole point of having the SqlGeography class? To manipulate the geography columns in the client?

    I also looked at nHibernate.Spatial, but they only have geometry types (so far).

    Thanks for your help,
     Noam Gal.
    Tuesday, October 21, 2008 9:58 AM

Answers

  • Hi Noam,

     

    I've poked around the EF team and it looks like they indeed do not support CLR UDTs at this time, though it sounds like they are planning on adding it in a future release. It looks like we have had some success internally with casting to bytes and then deserializing on the client.  We ran a test for EF where we cast a SqlGeography column to varbinary in SSDL, and then created a memory stream over the resulting byte[] to pass into SqlGeography.Read().  For the 19614 records in AdventureWorks2008.Person.Address, this seems to yield a SqlGeography value equal to that retrieved by SqlClient directly.

     

    That's probably the best work around for the time being. Hope this helps.

     

    Thanks,

    Goldie

     

    Tuesday, November 18, 2008 7:47 AM
    Moderator

All replies

  • I think you might be getting mixed up with the different forms of binary serialization available...

     

    WKB (as returned by the STAsBinary() method) serializes the co-ordinates of each point in the geometry (and the type of geometry that they represent). It does not specify the SRID in which those co-ordinates were defined. That is why, when using static methods to construct instances from WKB, you must specify both  the WKB representation and the SRID.

    However, SQL Server's own native binary storage format, as used by SqlGeography (and as returned by, say, SELECT geog from table) does serialize the SRID, in the first four bytes.

     

    To demonstrate:

    Code Snippet

    DECLARE @Point geometry
    SET @Point = geometry::STPointFromWKB(
      0x0101000000000000000000F0BF0000000000004940,
      4326)
    SELECT @Point

     

     

    The result is:

    0xE6100000010C000000000000F0BF0000000000004940

     

    Notice that although the serilization of each co-ordinate value remains the same (an 8-byte floating point representation - 000000000000F0BF is the X co-ordinate and 0000000000004940 is the Y co-ordinate), the first 4-bytes of the result, 0xE6100000 is the binary representation of 4326 - the SRID. You shoudn't have a problem accessing this in .Net.

     

    However, you are correct in saying that if you want to store and retrieve Z and M co-ordinate values you will either have to use WKT or SQL Server native format. GML and WKB do not support these co-ordinate values (at least, not the implementation of these formats currently used in SQL Server 2008). I don't understand the issue that you say you can't convert from the WKT produced by ToString() to an nvarchar field - the output already is an nvarchar.... isn't it?

     

    Perhaps if you paste a code sample it will be easier to see if there's a solution to your problem?

    Tuesday, October 21, 2008 12:40 PM
    Answerer
  • I have made two tests. In one I used a varbinary to read/write the geography column. In the other I used nvarchar.

    I have a simple test program that tries to write a random coordinate into the db, and read the coordinates from the db. Every time with both ObjectContexts.

     

    In the write bit, I manage to add a db record with the nvarchar version. The binary version throws

     

    Code Snippet
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
    System.ArgumentException: 24204: The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view.
    System.ArgumentException:
       at Microsoft.SqlServer.Types.SqlGeography.set_Srid(Int32 value)
       at Microsoft.SqlServer.Types.SqlGeography.Read(BinaryReader r)
       at SqlGeography::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )
    .
    The statement has been terminated.

     

     

    in the read bit, both versions throw me the same exception, basically.

     

    Code Snippet
    The 'location_binary' property on 'coordinate' could not be set to a 'SqlGeography' value. You must set this property to a non-null value of type 'Byte[]'.

     

    for the binary version, and

    Code Snippet

    The 'location_text' property on 'coordinate' could not be set to a 'SqlGeography' value. You must set this property to a non-null value of type 'String'.

    for the text version.

     

    here is a link to my little test (including the table definition) - link (rapidshare - 15KB)

     

    What am I doing wrong? Can I get the text version working with reads as well as writes, and then I am home free? shouldn't there be a much simpler way to read/write geography<->SqlGeography objects from the database?

    Sunday, October 26, 2008 9:57 AM
  • I still haven't managed to read geography data from sql2008 into c# with EF. Does nobody here ever do that? Can I only use geography with sqlcommands, and hand written connections?
    Wednesday, November 5, 2008 7:33 AM
  • Hi Noam,

    I was hoping an EF guru would answer this decisively, but if I recall correctly, the EF does not support CLR UDTs on the server.  Since the new spatial types look like UDTs to the client, I would not expect them to work.

    Let me see if I can't dig up something more definitive.

    Cheers,
    -Isaac
    Thursday, November 6, 2008 3:30 PM
    Moderator
  • Hi Noam,

     

    I've poked around the EF team and it looks like they indeed do not support CLR UDTs at this time, though it sounds like they are planning on adding it in a future release. It looks like we have had some success internally with casting to bytes and then deserializing on the client.  We ran a test for EF where we cast a SqlGeography column to varbinary in SSDL, and then created a memory stream over the resulting byte[] to pass into SqlGeography.Read().  For the 19614 records in AdventureWorks2008.Person.Address, this seems to yield a SqlGeography value equal to that retrieved by SqlClient directly.

     

    That's probably the best work around for the time being. Hope this helps.

     

    Thanks,

    Goldie

     

    Tuesday, November 18, 2008 7:47 AM
    Moderator