sql server Geometry 类型操作 笔记

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))

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCmd = db.GetSqlStringCommand("SELECT GeomCol1 FROM SpatialTable WHERE id=4");
using (IDataReader reader = db.ExecuteReader(dbCmd))
{
if (reader.Read())
{
SqlGeometry o = reader[0] as SqlGeometry;

}
}

SqlGeometry geo = SqlGeometry.Parse("POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))");
//DataSet ds = db.ExecuteDataSet(dbCmd);

//SqlGeometry geo = ds.Tables[0].Rows[0][1] as SqlGeometry;

//SqlDouble area = geo.STArea();

dbCmd = db.GetSqlStringCommand(@"INSERT INTO SpatialTable (GeomCol1) values(@p)");

SqlGeometryBuilder sb = new SqlGeometryBuilder();
sb.SetSrid(4326);
sb.BeginGeometry(OpenGisGeometryType.Polygon);
sb.BeginFigure(0, 0);
sb.AddLine(150, 0);
sb.AddLine(150, 150);
sb.AddLine(0, 150);
sb.AddLine(0, 0);
sb.EndFigure();
sb.EndGeometry();

dbCmd = db.GetSqlStringCommand(string.Format("INSERT INTO SpatialTable (GeomCol1) values(geometry::STGeomFromText(‘{0}‘, {1}))"
, sb.ConstructedGeometry.ToString(), 4326));
//db.AddInParameter(dbCmd, "@p", DbType.Binary, sb.ConstructedGeometry.STAsBinary().Buffer);

int cnt = db.ExecuteNonQuery(dbCmd);

通过这个sql获得系统的坐标系（Sql server中）：Select * from sys.spatial_reference_systems

sql server Geometry 类型操作 笔记

标签：