oracle.cs

用c#开发的基础gis库源代码

						{
							SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
							foreach(System.Data.DataColumn col in ds.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							fdt.AddRow(fdr);								
						}
						return fdt;
					}
					else return null;
				}
			}
		}

		/// <summary>
		/// Returns the features that intersects with 'geom'
		/// </summary>
		/// <param name="geom"></param>
		/// <param name="ds">FeatureDataSet to fill data into</param>
		public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
		{
			List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
			using (OracleConnection conn = new OracleConnection(_ConnectionString))
			{
				
                string strGeom = "MDSYS.SDO_GEOMETRY('" + geom.AsText() + "', #SRID#)";

                if (this.SRID > 0) {
                    strGeom = strGeom.Replace("#SRID#", this.SRID.ToString(Map.numberFormat_EnUS));
                } else {
                    strGeom = strGeom.Replace("#SRID#", "NULL");
                }

                strGeom = "SDO_RELATE(g." + this.GeometryColumn + ", " + strGeom + ", 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'";

                string strSQL = "SELECT g.* , g." + this.GeometryColumn + ").Get_WKB() As sharpmap_tempgeometry FROM " + this.Table + " g WHERE ";

				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += this.DefinitionQuery + " AND ";

				strSQL += strGeom;

				using (OracleDataAdapter adapter = new OracleDataAdapter(strSQL, conn))
				{
					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 != "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 != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							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 (OracleConnection conn = new OracleConnection(_ConnectionString))
			{
				string strSQL = "SELECT COUNT(*) FROM " + this.Table;
				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += " WHERE " + this.DefinitionQuery;
				using (OracleCommand command = new OracleCommand(strSQL, conn))
				{
					conn.Open();
					count = (int)command.ExecuteScalar();
					conn.Close();
				}				
			}
			return count;
		}

		#region IProvider Members

		private string _defintionQuery;

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

		/// <summary>
		/// Gets a collection of columns in the dataset
		/// </summary>
		public System.Data.DataColumnCollection Columns
		{
			get {
				throw new NotImplementedException();
			}
		}

		private int _srid=-2;

		/// <summary>
		/// Spacial Reference ID
		/// </summary>
		public int SRID
		{
			get {
				if (_srid == -2)
				{
					string strSQL = "select SRID from USER_SDO_GEOM_METADATA WHERE TABLE_NAME='" + this.Table + "'";
                    
					using (OracleConnection conn = new OracleConnection(_ConnectionString))
					{
                        using (OracleCommand command = new OracleCommand(strSQL, conn))
						{
							try
							{								
								conn.Open();
								_srid = (int)(decimal)command.ExecuteScalar();
								conn.Close();
							}
							catch
							{								
								_srid = -1;
							}
						}
					}
				}
				return _srid;
			}
			set {
				throw (new ApplicationException("Spatial Reference ID cannot by set on a Oracle table"));
			}
		}

		/// <summary>
		/// Queries the Oracle 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 COLUMN_NAME from USER_SDO_GEOM_METADATA WHERE TABLE_NAME='" + this.Table + "'";
            using (OracleConnection conn = new OracleConnection(_ConnectionString))
            using (OracleCommand command = new OracleCommand(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 (OracleConnection conn = new OracleConnection(_ConnectionString))
			{
                string strSQL = "select g.* , g." + this.GeometryColumn + ").Get_WKB() As sharpmap_tempgeometry from " + this.Table + " g WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
                using (OracleDataAdapter adapter = new OracleDataAdapter(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 != "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 != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							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 (OracleConnection conn = new OracleConnection(_ConnectionString))
			{
                string strSQL = "SELECT SDO_AGGR_MBR(g." + this.GeometryColumn + ").Get_WKT() FROM " + this.Table + " g ";
				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += " WHERE " + this.DefinitionQuery;
                using (OracleCommand command = new OracleCommand(strSQL, conn))
				{
					conn.Open();
					object result = command.ExecuteScalar();
					conn.Close();
					if (result == System.DBNull.Value)
						return null;
					string strBox = (string)result;
					if (strBox.StartsWith("POLYGON", StringComparison.InvariantCultureIgnoreCase))
					{
                        strBox = strBox.Replace("POLYGON", "");                    
                        strBox = strBox.Trim();
                        strBox = strBox.Replace("(", "");
                        strBox = strBox.Replace(")", "");

                        List<double> xX = new List<double>();
                        List<double> yY = new List<double>();

                        String[] points = strBox.Split(',');
                        String[] nums;
                        string point;

                        foreach (string s in points)
	                    {
                            point = s.Trim();
                            nums = point.Split(' ');
                            xX.Add(double.Parse(nums[0], SharpMap.Map.numberFormat_EnUS));
                            yY.Add(double.Parse(nums[1], SharpMap.Map.numberFormat_EnUS));
	                    }

                        double minX = Double.MaxValue;
                        double minY = Double.MaxValue;
                        double maxX = Double.MinValue;
                        double maxY = Double.MinValue;

                        foreach (double d in xX) {
                            if(d > maxX){
                                maxX = d;
                            }
                            if (d < minX) {
                                minX = d;
                            }
                        }

                        foreach (double d in yY) {
                            if (d > maxY) {
                                maxY = d;
                            }
                            if (d < minY) {
                                minY = d;
                            }
                        }

						return new SharpMap.Geometries.BoundingBox(minX, minY, maxX, maxY);
					}
					else
						return null;
				}
			}
		}

		/// <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(box) instead")]
		public void GetFeaturesInView(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
		{
			GetFeaturesInView(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 (OracleConnection conn = new OracleConnection(_ConnectionString))
			{
                //Get bounding box string
                string strBbox = GetBoxFilterStr(bbox);

                string strSQL = "SELECT g.*, g." + this.GeometryColumn + ".Get_WKB() AS sharpmap_tempgeometry ";
				strSQL += "FROM " + this.Table + " g WHERE ";

				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += this.DefinitionQuery + " AND ";

				strSQL += strBbox;

                using (OracleDataAdapter adapter = new OracleDataAdapter(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 != "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 != "sharpmap_tempgeometry")
									fdr[col.ColumnName] = dr[col];
							fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							fdt.AddRow(fdr);								
						}
						ds.Tables.Add(fdt);
					}
				}
			}
		}
		#endregion
	}
}