A first look at SQL 2008 spatial data types round trip with sql 2008. I was surprised to find that the Entity Framework, LINQ to SQL and Dataset Designer do not currently support Spatial data types. Given this was the case and wanting to have a play with the spatial data type the following is how I used the sqlgeometry spatial data type to store and retrieve a list both lines and points consisting of X,Y co-ordinates.
Lets use a bottom up approach create a table using the GEOMETRY data type:
CREATE TABLE [dbo].[MyData]( [ID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newsequentialid()) , [Data] [GEOMETRY] NULL); GO
Create a stored procedure to insert data into the MyData table:
CREATE PROCEDURE [dbo].[InsertMyData] @Lines GEOMETRY AS INSERT INTO [dbo].[MyData] (Data) VALUES (@Lines); RETURN 0 GO
And create a stored procedure to retrieve a the data:
CREATE PROCEDURE [dbo].[GetMyData] @ID uniqueIdentifier AS SELECT * FROM [dbo].[MyData] WHERE ID = @ID RETURN 0 GO
Code to insert and retrieve data:
- Add a reference to C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll which contains SqlGeometryBuilder
-
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Threading; using System.Threading.Tasks; using Microsoft.SqlServer.Types;
This is where it gets back to the !good old days without generation of your DAL. Add a method to call the InsertMyData stored procedure:
public bool InsertMyData(Guid id, List> lines) { int result = 0; if (lines.Count > 0) { using (SqlConnection conn = new SqlConnection(connString)) using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "InsertMyData"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ID", id)); cmd.Parameters.Add(SpatialDataHelper.GetGeometrySqlParameter("@Data", lines, true)); conn.Open(); result = cmd.ExecuteNonQuery(); } } return (result > 0); }
Now you may be wondering what is SpatialDataHelper.GetGeometrySqlParameter. Well that too is something that needs to be implemented. Even though the SQL geometry type is a system type the type must be treated as user defined type. Take note of the sqlParam.UdtTypeName = “geometry”;
public static class SpatialDataHelper { public static SqlParameter GetGeometrySqlParameter(string paramName, List> lines, bool makeValid) { SqlParameter sqlParam = new SqlParameter(); sqlParam.ParameterName = paramName; sqlParam.UdtTypeName = "geometry"; SqlGeometryBuilder geomBuilder = new SqlGeometryBuilder(); // SqlGeometryBuilder.SetSrid Must be called at the beginning of each geometry sequence geomBuilder.SetSrid(0); geomBuilder.BeginGeometry(OpenGisGeometryType.GeometryCollection); //break out each line foreach (List
lp in lines) { if (lp.Count > 0) { if (lp.Count == 1) //check if its a point or a line and start a geometry for the point or line geomBuilder.BeginGeometry(OpenGisGeometryType.Point); else geomBuilder.BeginGeometry(OpenGisGeometryType.LineString); int count = 0; foreach (Point p in lp) //add all points { if (count == 0) geomBuilder.BeginFigure(p.X, p.Y); else geomBuilder.AddLine(p.X, p.Y); count++; } geomBuilder.EndFigure(); geomBuilder.EndGeometry(); } } geomBuilder.EndGeometry(); SqlGeometry constructed = geomBuilder.ConstructedGeometry; if (makeValid) { //Note required to convert into a geometry instance with a valid Open Geospatial Consortium (OGC) type. // this can cause the points to shift - use with caution... constructed = constructed.MakeValid(); } sqlParam.Value = constructed; return sqlParam; } }
Finally to get your data back out its necessary to call the previously defined GetMyData stored procedure and then crunch out the points into their original form.
public List> GetMyData(Guid id) { ConcurrentBag
> bag = new ConcurrentBag
>(); using (SqlConnection conn = new SqlConnection(connString)) using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "GetMyData"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ID", id)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { startLinesID = reader.GetGuid(reader.GetOrdinal("ID")); SqlGeometry gLines = (SqlGeometry)reader["Data"]; // using Parallel.For to speed up crunch. Note: STGeometryN starts with 1 based index therefore start with i of 1 Parallel.For(1, gLines.STNumGeometries().Value + 1, delegate(int i) { SqlGeometry geom = gLines.STGeometryN(i); List
line = new List (); switch (geom.STGeometryType().ToString()) { case "LineString": //a LineString is a line within the SQLGeometry for (int j = 1; j <= geom.STNumPoints(); j++) //get all points forming the line { line.Add(new Point(geom.STPointN(j).STX.ToSqlInt32().Value, geom.STPointN(j).STY.ToSqlInt32().Value)); } break; case "Point": //an individual point line.Add(new Point(geom.STPointN(1).STX.ToSqlInt32().Value, geom.STPointN(1).STY.ToSqlInt32().Value)); //1 is the first index not 0 based break; } bag.Add(line); }); } } } return bag.ToList(); }
This seems to do the job although I would be interested to hear of better ways to do this.