postgis2.cs

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

// Copyright 2005, 2006 - Christian Gr鋐e (www.sharptools.de)
//
// This file is part of SharpMap.
// SharpMap is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// SharpMap is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with SharpMap; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 


using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Runtime;
using System.Text;

// more info at http://sf.net/projects/pgsqlclient
using PostgreSql.Data.PostgreSqlClient;
using PostgreSql.Data.PgTypes;

namespace SharpMap.Data.Providers
{
    /// <summary>
    /// PostGreSQL / PostGIS dataprovider
    /// </summary>
    /// <example>
    /// Adding a datasource to a layer:
    /// <code lang="C#">
    /// SharpMap.Layers.VectorLayer myLayer = new SharpMap.Layers.VectorLayer("My layer");
    ///	string ConnStr = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=password;Database=myGisDb;";
    /// myLayer.DataSource = new SharpMap.Data.Providers.PostGIS2(ConnStr, "myTable");
    /// </code>
    /// </example>
    [Serializable]
    public class PostGIS2 : SharpMap.Data.Providers.IProvider, IDisposable
    {
        /// <summary>
        /// Initializes a new connection to PostGIS
        /// </summary>
        /// <param name="ConnectionStr">Connectionstring</param>
        /// <param name="tablename">Name of data table</param>
        /// <param name="geometryColumnName">Name of geometry column</param>
        /// <param name="OID_ColumnName">Name of column with unique identifier</param>
        public PostGIS2(string ConnectionStr, string tablename, string geometryColumnName, string OID_ColumnName)
        {
            this.ConnectionString = ConnectionStr;
            this.Table = tablename;
            this.GeometryColumn = geometryColumnName;
            this.ObjectIdColumn = OID_ColumnName;
        }

        /// <summary>
        /// Initializes a new connection to PostGIS
        /// </summary>
        /// <param name="ConnectionString">Connectionstring</param>
        /// <param name="TableName">Name of data table</param>
        /// <param name="OID_ColumnName">Name of column with unique identifier</param>
        public PostGIS2(string ConnectionString, string TableName, string OIdColumnName)
            : this(ConnectionString, TableName, "", OIdColumnName)
        {
            this.GeometryColumn = this.GetGeometryColumn();
        }

        private bool _IsOpen;

        /// <summary>
        /// Returns true if the datasource is currently open
        /// </summary>
        public bool IsOpen
        {
            get { return _IsOpen; }
        }

        /// <summary>
        /// Opens the datasource
        /// </summary>
        public void Open()
        {
            //Don't really do anything. npgsql's ConnectionPooling takes over here
            _IsOpen = true;
        }
        /// <summary>
        /// Closes the datasource
        /// </summary>
        public void Close()
        {
            //Don't really do anything. npgsql's ConnectionPooling takes over here
            _IsOpen = false;
        }


        #region Disposers and finalizers
        private bool disposed = false;

        /// <summary>
        /// Disposes the object
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        internal void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    //Close();
                }
                disposed = true;
            }
        }

        /// <summary>
        /// Finalizer
        /// </summary>
        ~PostGIS2()
        {
            Dispose();
        }
        #endregion

        private string _ConnectionString;

        /// <summary>
        /// Connectionstring
        /// </summary>
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        private string _Table;

        /// <summary>
        /// Data table name
        /// </summary>
        public string Table
        {
            get { return _Table; }
            set { _Table = value; }
        }

        private string _GeometryColumn;

        /// <summary>
        /// Name of geometry column
        /// </summary>
        public string GeometryColumn
        {
            get { return _GeometryColumn; }
            set { _GeometryColumn = value; }
        }

        private string _ObjectIdColumn;

        /// <summary>
        /// Name of column that contains the Object ID
        /// </summary>
        public string ObjectIdColumn
        {
            get { return _ObjectIdColumn; }
            set { _ObjectIdColumn = value; }
        }


        /// <summary>
        /// Returns geometries within the specified bounding box
        /// </summary>
        /// <param name="bbox"></param>
        /// <returns></returns>
        public Collection<Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
        {
            Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>();
            using (PgConnection conn = new PgConnection(_ConnectionString))
            {
                string strBbox = GetBoundingBoxSql(bbox, this.SRID);

                String strSql = String.Format("SELECT AsBinary({0}) as geom FROM {1} WHERE ",
                                              this.GeometryColumn,
                                              this.Table);

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

                strSql += String.Format("{0} && {1}", this.GeometryColumn, strBbox);

                using (PgCommand command = new PgCommand(strSql, conn))
                {
                    conn.Open();
                    using (PgDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            //object obj = dr[0];
                            SharpMap.Geometries.Geometry geom = null;

                            //							if ( typeof(PgPoint) == obj.GetType() )
                            //								geom = new SharpMap.Geometries.Point( ((PgPoint)obj).X, ((PgPoint)obj).Y );
                            //							else 
                            if (dr[0] != DBNull.Value)
                                geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);


                            if (geom != null)
                                features.Add(geom);

                        }
                    }
                    conn.Close();
                }
            }
            return features;
        }

        /// <summary>
        /// Returns the geometry corresponding to the Object ID
        /// </summary>
        /// <param name="oid">Object ID</param>
        /// <returns>geometry</returns>
        public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
        {
            SharpMap.Geometries.Geometry geom = null;
            using (PgConnection conn = new PgConnection(_ConnectionString))
            {
                String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
                                              this.GeometryColumn,
                                              this.Table,
                                              this.ObjectIdColumn,
                                              oid);

                conn.Open();
                using (PgCommand command = new PgCommand(strSql, conn))
                {
                    using (PgDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            object obj = dr[0];
                            if (typeof(PgPoint) == obj.GetType())
                                geom = new SharpMap.Geometries.Point(((PgPoint)obj).X, ((PgPoint)obj).Y);
                            else if (obj != DBNull.Value)
                                geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
                        }
                    }
                }
                conn.Close();
            }
            return geom;
        }
        /// <summary>
        /// Returns geometry Object IDs whose bounding box intersects 'bbox'
        /// </summary>
        /// <param name="bbox"></param>
        /// <returns></returns>
        public Collection<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
        {
            Collection<uint> objectlist = new Collection<uint>();
            using (PgConnection conn = new PgConnection(_ConnectionString))
            {
                string strBbox = GetBoundingBoxSql(bbox, this.SRID);

                String strSql = String.Format("SELECT {0} FROM {1} WHERE ", this.ObjectIdColumn, this.Table);

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

                strSql += this.GeometryColumn + " && " + strBbox;

                using (PgCommand command = new PgCommand(strSql, conn))
                {
                    conn.Open();
                    using (PgDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            if (dr[0] != DBNull.Value)
                            {
                                uint ID = (uint)(int)dr[0];
                                objectlist.Add(ID);
                            }
                        }
                    }
                    conn.Close();
                }
            }
            return objectlist;
        }

        /// <summary>
        /// Returns all objects within a distance of a geometry
        /// </summary>
        /// <param name="geom"></param>
        /// <param name="distance"></param>
        /// <returns></returns>
        [Obsolete("Use ExecuteIntersectionQuery instead")]
        public SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance)
        {
            Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>();
            using (PgConnection conn = new PgConnection(_ConnectionString))
            {
                string strGeom = "GeomFromText('" + geom.AsText() + "')";
                if (this.SRID > 0)
                    strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";

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

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

                strSQL += this.GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.numberFormat_EnUS) + ")";
                strSQL += " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.numberFormat_EnUS);

                using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    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);
                        }
                        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 (PgConnection conn = new PgConnection(_ConnectionString))
            {
                string strGeom = "GeomFromText('" + geom.AsText() + "')";
                if (this.SRID > 0)
                    strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";

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