sqlGeometry 类型为sql server 2008之后的版本 新加的一种CLR扩展数据类型，为广大sql server开发人员存储几何类型及空间运算提供极大的便利，下面说明geometry类型的具体操作
示例SQL语句代码
IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL
DROP TABLE dbo.SpatialTable;
GO
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO
INSERT INTO SpatialTable (GeomCol1)
VALUES(geometry::STGeomFromText('LINESTRING (116.387112 39.920977,116.385243 39.913063,116.394226 39.917988,116.401772 39.921364,116.41248 39.927893,116.387112 39.920977)', 4326))
几何类型操作
1 Database db = DatabaseFactory.CreateDatabase(); 2 DbCommand dbCmd = db.GetSqlStringCommand("SELECT GeomCol1 FROM SpatialTable WHERE id=4"); 3 using (IDataReader reader = db.ExecuteReader(dbCmd)) 4 { 5 if (reader.Read()) 6 { 7 SqlGeometry o = reader[0] as SqlGeometry; 8 9 } 10 } 11 12 SqlGeometry geo = SqlGeometry.Parse("POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"); 13 //DataSet ds = db.ExecuteDataSet(dbCmd); 14 15 //SqlGeometry geo = ds.Tables[0].Rows[0][1] as SqlGeometry; 16 17 //SqlDouble area = geo.STArea(); 18 19 dbCmd = db.GetSqlStringCommand(@"INSERT INTO SpatialTable (GeomCol1) values(@p)"); 20 21 //构建多边形 22 SqlGeometryBuilder sb = new SqlGeometryBuilder(); 23 sb.SetSrid(4326); 24 sb.BeginGeometry(OpenGisGeometryType.Polygon); 25 sb.BeginFigure(0, 0); 26 sb.AddLine(150, 0); 27 sb.AddLine(150, 150); 28 sb.AddLine(0, 150); 29 sb.AddLine(0, 0); 30 sb.EndFigure(); 31 sb.EndGeometry(); 32 33 dbCmd = db.GetSqlStringCommand(string.Format("INSERT INTO SpatialTable (GeomCol1) values(geometry::STGeomFromText('{0}', {1}))" 34 , sb.ConstructedGeometry.ToString(), 4326)); 35 //db.AddInParameter(dbCmd, "@p", DbType.Binary, sb.ConstructedGeometry.STAsBinary().Buffer); 36 37 int cnt = db.ExecuteNonQuery(dbCmd);
数据库对数据进行过滤操作
1 SELECT c_geomCol.MakeValid().STCentroid().STAsText() FROM t_green_point 2 WHERE c_geomCol IS NOT NULL 3 --AND c_geomCol2<>'POLYGON EMPTY' 4 AND c_geomCol.STIsValid()=1
通过这个sql获得系统的坐标系（Sql server中）：Select * from sys.spatial_reference_systems
//MultiPolygon 多个多边形结合处理
1 SqlGeometryBuilder sb = new SqlGeometryBuilder(); //构造多个多边形实例 2 sb.SetSrid(0); 3 sb.BeginGeometry(OpenGisGeometryType.MultiPolygon); 4 5 sb.BeginGeometry(OpenGisGeometryType.Polygon); //构造第一个多边形 6 sb.BeginFigure(1, 1); 7 sb.AddLine(1, -1); 8 sb.AddLine(-1,-1); 9 sb.AddLine(-1,1); 10 sb.AddLine(1,1); 11 sb.EndFigure(); 12 sb.EndGeometry(); 13 14 sb.BeginGeometry(OpenGisGeometryType.Polygon); //构造第二个多边形 15 sb.BeginFigure(1,1); 16 sb.AddLine(3,1); 17 sb.AddLine(3,3); 18 sb.AddLine(1,3); 19 sb.AddLine(1,1); 20 sb.EndFigure(); 21 sb.EndGeometry(); 22 23 sb.BeginGeometry(OpenGisGeometryType.Polygon); //构造第N个多边形 24 sb.BeginFigure(4,4); 25 sb.AddLine(6,4); 26 sb.AddLine(6,6); 27 sb.AddLine(4,6); 28 sb.AddLine(4,4); 29 sb.EndFigure(); 30 sb.EndGeometry(); 31 32 sb.EndGeometry(); 33 34 geo = sb.ConstructedGeometry; 35 36 //下面是解析代码 37 int numGeometries = geo.STNumGeometries().Value; //有多少个多边形 38 39 SqlGeometry geoN = null; 40 41 for (int i = 1; i <= numGeometries; i++) 42 { 43 geoN = geo.STGeometryN(i);//第几个多边形 44 for (int j = 1; j <= geoN.STNumPoints(); j++) //转到百度地图多边形最后一个点可以去掉 45 { 46 point = geoN.STPointN(i); 47 Console.WriteLine("第{0}个多边形，第{1}点,X={2},Y={3}", i, j, point.STX.Value, point.STY); 48 } 49 50 }
输出结果：
参数化的SQL语句传值
DbCommand dbCmd = db.GetSqlStringCommand(@"INSERT INTO SpatialTable (GeomCol1) values(@p)"); db.AddInParameter(dbCmd, "@p", DbType.Binary, sb.ConstructedGeometry.Serialize()); int cnt = db.ExecuteNonQuery(dbCmd);
判断多边形是否相交
DECLARE @bigGeo geometry= 'POLYGON((0 0, 3 0, 3 3, 0 3,0 0))'; DECLARE @smallGeo geometry='POLYGON((1 1 ,2 1,2 2,1 2,1 1))'; DECLARE @midGeo geometry='POLYGON((0 0, 1.5 0, 1.5 1.5, 0 1.5,0 0))'; DECLARE @Geo3 geometry='POLYGON((2 2, 3 2,3 3,2 3,2 2))'; SELECT @bigGeo.STIntersection(@smallGeo).STAsText() SELECT @midGeo.STIntersection(@smallGeo).STIsEmpty() SELECT @midGeo.STIntersection(@Geo3).STIsEmpty()
如果相交则结果不为空
参考资料：