mssqlspatial.cs

C# 的地图开发例子（sharp map)

					adapter.Fill(ds);
					conn.Close();
					if (ds.Tables.Count > 0)
					{
						FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
						foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
							if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
								fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
						foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
						{
							SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
							foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							if (dr["sharpmap_tempgeometry"] != DBNull.Value)
								fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							fdt.AddRow(fdr);
						}
						ds.Tables.Add(fdt);
					}
				}
			}
		}

		/// <summary>
		/// Convert WellKnownText to linestrings
		/// </summary>
		/// <param name="WKT"></param>
		/// <returns></returns>
		private SharpMap.Geometries.LineString WktToLineString(string WKT)
		{
			SharpMap.Geometries.LineString line = new SharpMap.Geometries.LineString();
			WKT = WKT.Substring(WKT.LastIndexOf('(') + 1).Split(')')[0];
			string[] strPoints = WKT.Split(',');
			foreach (string strPoint in strPoints)
			{
				string[] coord = strPoint.Split(' ');
				line.Vertices.Add(new SharpMap.Geometries.Point(double.Parse(coord[0], SharpMap.Map.numberFormat_EnUS), double.Parse(coord[1], SharpMap.Map.numberFormat_EnUS)));
			}
			return line;
		}

		/// <summary>
		/// Returns the number of features in the dataset
		/// </summary>
		/// <returns>number of features</returns>
		public int GetFeatureCount()
		{
			int count = 0;
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "SELECT COUNT(*) FROM " + this.Table;
				if (!String.IsNullOrEmpty(_definitionQuery))
					strSQL += " WHERE " + this.DefinitionQuery;
				using (SqlCommand command = new SqlCommand(strSQL, conn))
				{
					conn.Open();
					count = (int)command.ExecuteScalar();
					conn.Close();
				}				
			}
			return count;
		}

		#region IProvider Members

		private string _definitionQuery;

		/// <summary>
		/// Definition query used for limiting dataset
		/// </summary>
		public string DefinitionQuery
		{
			get { return _definitionQuery; }
			set { _definitionQuery = value; }
		}

		/// <summary>
		/// Gets a collection of columns in the dataset
		/// </summary>
		public System.Data.DataColumnCollection Columns
		{
			get {
				throw new NotImplementedException();
				//using (SqlConnection conn = new SqlConnection(this.ConnectionString))
				//{
				//    System.Data.DataColumnCollection columns = new System.Data.DataColumnCollection();
				//    string strSQL = "SELECT column_name, udt_name FROM information_schema.columns WHERE table_name='" + this.Table + "' ORDER BY ordinal_position";
				//    using (SqlCommand command = new SqlCommand(strSQL, conn))
				//    {
				//        conn.Open();
				//        using (SqlDataReader dr = command.ExecuteReader())
				//        {
				//            while (dr.Read())
				//            {
				//                System.Data.DataColumn col = new System.Data.DataColumn((string)dr["column_name"]);
				//                switch((string)dr["udt_name"])
				//                {
				//                    case "int4":
				//                        col.DataType = typeof(Int32);
				//                        break;
				//                    case "int8":
				//                        col.DataType = typeof(Int64);
				//                        break;
				//                    case "varchar":
				//                        col.DataType = typeof(string);
				//                        break;
				//                    case "text":
				//                        col.DataType = typeof(string);
				//                        break;
				//                    case "bool":
				//                        col.DataType = typeof(bool);
				//                        break;
				//                    case "geometry":
				//                        col.DataType = typeof(SharpMap.Geometries.Geometry);
				//                        break;
				//                    default:
				//                        col.DataType = typeof(object);
				//                        break;
				//                }
				//                columns.Add(col);
				//            }
				//        }
				//    }
				//    return columns;
				//}
			}
		}

		private int _srid=-2;

		/// <summary>
		/// Spacial Reference ID
		/// </summary>
		public int SRID
		{
			get {
				if (_srid == -2)
				{
					int dotPos = this.Table.IndexOf(".");
					string strSQL = "";
					if (dotPos == -1)
						strSQL = "select SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_NAME='" + this.Table + "'";
					else
					{
						string schema = this.Table.Substring(0, dotPos);
						string table = this.Table.Substring(dotPos + 1);
						strSQL = "select SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA='" + schema + "' AND F_TABLE_NAME='" + table + "'";
					}

					using (SqlConnection conn = new SqlConnection(_ConnectionString))
					{
						using (SqlCommand command = new SqlCommand(strSQL, conn))
						{
							try
							{
								conn.Open();
								_srid = (int)command.ExecuteScalar();
								conn.Close();
							}
							catch
							{
								_srid = -1;
							}
						}
					}
				}
				return _srid;
			}
			set
			{
				// SRID can be set in order to support views.
				_srid = value;
			}
		}

		/// <summary>
		/// Queries the MsSqlSpatial database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
		/// </summary>
		/// <remarks></remarks>
		/// <returns>Name of column containing geometry</returns>
		private string GetGeometryColumn()
		{
			string strSQL = "select F_GEOMETRY_COLUMN from ST.GEOMETRY_COLUMNS WHERE F_TABLE_NAME='" + this.Table + "'";
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
				using (SqlCommand command = new SqlCommand(strSQL, conn))
				{
					conn.Open();
					object columnname = command.ExecuteScalar();
					conn.Close();
					if (columnname == System.DBNull.Value)
						throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
					return (string)columnname;					
				}
		}


		/// <summary>
		/// Returns a datarow based on a RowID
		/// </summary>
		/// <param name="RowID"></param>
		/// <returns>datarow</returns>
		public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
		{
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "select * , ST.AsBinary(" + this.BuildGeometryExpression() + ") As sharpmap_tempgeometry from " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
				using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
				{
					FeatureDataSet ds = new FeatureDataSet();
					conn.Open();
					adapter.Fill(ds);
					conn.Close();
					if (ds.Tables.Count > 0)
					{
						FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
						foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
							if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
								fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
						if(ds.Tables[0].Rows.Count>0)
						{
							System.Data.DataRow dr = ds.Tables[0].Rows[0];
							SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
							foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							if (dr["sharpmap_tempgeometry"] != DBNull.Value)
								fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							return fdr;
						}
						else
							return null;

					}
					else 
						return null;
				}				
			}
		}

		/// <summary>
		/// Boundingbox of dataset
		/// </summary>
		/// <returns>boundingbox</returns>
		public SharpMap.Geometries.BoundingBox GetExtents()
		{
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = string.Format("SELECT ST.AsBinary(ST.EnvelopeQueryWhere('{0}', '{1}', '{2}'))", this.Table, this.GeometryColumn, this.DefinitionQuery.Replace("'", "''"));
				using (SqlCommand command = new SqlCommand(strSQL, conn))
				{
					conn.Open();
					object result = command.ExecuteScalar();
					conn.Close();
					if (result == System.DBNull.Value)
						return null;
					SharpMap.Geometries.BoundingBox bbox = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])result).GetBoundingBox();
					return bbox;
				}
			}
		}

		/// <summary>
		/// Gets the connection ID of the datasource
		/// </summary>
		public string ConnectionID
		{
			get { return _ConnectionString; }
		}

		#endregion

		#region IProvider Members

		/// <summary>
		/// Returns all features with the view box
		/// </summary>
		/// <param name="bbox">view box</param>
		/// <param name="ds">FeatureDataSet to fill data into</param>
		[Obsolete("Use ExecuteIntersectionQuery")]
		public void GetFeaturesInView(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
		{
			ExecuteIntersectionQuery(bbox, ds);
		}

		/// <summary>
		/// Returns all features with the view box
		/// </summary>
		/// <param name="bbox">view box</param>
		/// <param name="ds">FeatureDataSet to fill data into</param>
		public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
		{
			List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "SELECT *, ST.AsBinary(" + this.BuildGeometryExpression() + ") AS sharpmap_tempgeometry ";
				strSQL += "FROM ST.FilterQuery" + this.BuildSpatialQuerySuffix() + "(" + this.BuildEnvelope(bbox) + ")";

				if (!String.IsNullOrEmpty(_definitionQuery))
					strSQL += " WHERE " + this.DefinitionQuery;

				using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
				{
					conn.Open();
					System.Data.DataSet ds2 = new System.Data.DataSet();
					adapter.Fill(ds2);
					conn.Close();
					if (ds2.Tables.Count > 0)
					{
						FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
						foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
							if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
								fdt.Columns.Add(col.ColumnName,col.DataType,col.Expression);
						foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
						{
							SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
							foreach(System.Data.DataColumn col in ds2.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && !col.ColumnName.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							if (dr["sharpmap_tempgeometry"] != DBNull.Value)
								fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							fdt.AddRow(fdr);								
						}
						ds.Tables.Add(fdt);
					}
				}
			}
		}
		#endregion

		private string BuildSpatialQuerySuffix()
		{
			string schema;
			string table = this.Table;
			int dotPosition = table.IndexOf('.');
			if (dotPosition == -1)
			{
				schema = "dbo";
			}
			else
			{
				schema = table.Substring(0, dotPosition);
				table = table.Substring(dotPosition + 1);
			}
			return "#" + schema + "#" + table + "#" + this.GeometryColumn;
		}

		private string BuildGeometryExpression()
		{
			return string.Format(this.GeometryExpression, this.GeometryColumn);
		}

		private string BuildEnvelope(SharpMap.Geometries.BoundingBox bbox)
		{
			return string.Format(SharpMap.Map.numberFormat_EnUS,
				"ST.MakeEnvelope({0},{1},{2},{3},{4})",
					bbox.Min.X,
					bbox.Min.Y,
					bbox.Max.X,
					bbox.Max.Y,
					this.SRID);
		}
	}
}