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.