locked
an application point of view RRS feed

  • Question

  • What would be the best approach to use the data from spatial columns in a .net application using the Microsoft.SqlServer.Types types as proposed in another thread here? How can I retrieve the from the database if so? And most of all what about some spatial features in the .NET framework, any plans?

    Sunday, November 25, 2007 4:51 PM

Answers

  • I'm not sure what you're asking - how your code should use the spatial types within a .net application depends a lot on what your application wants to do.  To just get the data out of the database and use it in your application you can do something like the below.  Please clarify if you had something else in mind.

     

    Are there any specific spatial features you would be interested in seeing added to the .NET Framework?

     

    using (SqlConnection sqlConn = new SqlConnection(sqlConnString))

    {

    sqlConn.Open();

    using (SqlCommand cmd = new SqlCommand("select shape from mygeometries", sqlConn))

    {

    using (SqlDataReader rdr = cmd.ExecuteReader())

    {

    while (rdr.Read())

    {

    SqlGeometry geom = (SqlGeometry)rdr.GetValue(0);

    Console.WriteLine("Geometry Type: " + geom.STGeometryType());

    Console.WriteLine("Length: " + geom.STLength());

    Console.WriteLine("Area: " + geom.STArea());

    Console.WriteLine("Envelope: " + geom.STEnvelope().ToString());

    Console.WriteLine("Points X Y:");

    for (int i = 1; i <= geom.STNumPoints(); i++)

    Console.WriteLine(geom.STPointN(i).STX + " " + geom.STPointN(i).STY);

    Console.WriteLine();

    }

    }

    }

    Tuesday, November 27, 2007 7:50 PM

All replies

  • I'm not sure what you're asking - how your code should use the spatial types within a .net application depends a lot on what your application wants to do.  To just get the data out of the database and use it in your application you can do something like the below.  Please clarify if you had something else in mind.

     

    Are there any specific spatial features you would be interested in seeing added to the .NET Framework?

     

    using (SqlConnection sqlConn = new SqlConnection(sqlConnString))

    {

    sqlConn.Open();

    using (SqlCommand cmd = new SqlCommand("select shape from mygeometries", sqlConn))

    {

    using (SqlDataReader rdr = cmd.ExecuteReader())

    {

    while (rdr.Read())

    {

    SqlGeometry geom = (SqlGeometry)rdr.GetValue(0);

    Console.WriteLine("Geometry Type: " + geom.STGeometryType());

    Console.WriteLine("Length: " + geom.STLength());

    Console.WriteLine("Area: " + geom.STArea());

    Console.WriteLine("Envelope: " + geom.STEnvelope().ToString());

    Console.WriteLine("Points X Y:");

    for (int i = 1; i <= geom.STNumPoints(); i++)

    Console.WriteLine(geom.STPointN(i).STX + " " + geom.STPointN(i).STY);

    Console.WriteLine();

    }

    }

    }

    Tuesday, November 27, 2007 7:50 PM
  • Sorry for being unclear. I realize that I can use SQL types in .NET code, but this seems far from being a best practice. let me rephrase: Just for general knowledge, if someone would start developing a location aware application using SQL Server Katamai and the .NET platform in the future, it seems to me very clear what should be the development approach while developing inside SQL server but are there any future plans to release supplementary types and features that are designed to be used in .NET applications?

    Saturday, December 1, 2007 11:44 AM
  • And what about Win32 developers, who can't use .NET types and access the data via ADO, DBX drivers? Is geometry conversion the only way of using new types, because now such columns are even not visible in query?
    Monday, December 3, 2007 12:52 PM
  • I'm still not quite sure I follow the question---let us know if this answers it.

     

    We are natuarlly looking at additional spatial functionality, and it is very likely that any future enhancements will be supported on the CLR platform.  Right now, the CLR is central to our extensibility story in SQL Server, and I would expect future investments to support that direction.

     

    To be clear: I can't make any firm promises, but this is the direction I would expect.

     

    Native platforms can still interoperate with the types, but should go through one of the supported interchange formats: WKT, WKB, or GML.

     

    Cheers,

    -Isaac

    Monday, December 3, 2007 9:53 PM
  • (I've asked about the same question in another thread but never got a reply)

    So Isaac, with the focus on CLR, can you tell use what the story will be on client use of the new SqlGeo* types?
    In the November CTP I could only find them living entrenched in the server:

    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\Microsoft.SqlServer.Types.dll
    along with its unmanaged support lib
    SqlServerSpatial.dll.

    It would also be nice to know what changes were made in .NET2 SP1 that were required to use the new classes (which I've successfully done).

    /P


    Wednesday, December 5, 2007 8:20 AM
  • There will be a client redistributable installer available that contains those two files for use on client machines, and it is currently invoked if you install the SQL Management Tools.  If so, you'll note that Microsoft.SqlServer.Types is in the GAC and SqlServerSpatial.dll is in your %windir%\system32 directory.

     

    The only change in .NET2 SP1 required to support the spatial types was the generic Large Udt support added to SqlClient so that any User-Defined Type could be declared to be a Large Object, similar to varbinary(max).

     

    Wednesday, December 5, 2007 5:31 PM