예제 #1
0
        /// <summary>
        /// Finds out information about the geometry column
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        private FeatureSetTableInfo FindGeometryColumnInfo(SQLiteConnection conn, string sqlQuery)
        {
            FeatureSetTableInfo result = null;

            using (var cmd = new SQLiteCommand(sqlQuery, conn))
            {
                var wkbr = new SpatiaLiteWkbReader();

                var rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);

                var schemaTable = rdr.GetSchemaTable();
                foreach (DataRow r in schemaTable.Rows)
                {
                    var colName     = Convert.ToString(r["ColumnName"]);
                    var colDataType = Convert.ToString(r["DataType"]);
                    //if BLOB, then assume geometry column
                    if (Type.GetType(colDataType) == typeof(byte[]))
                    {
                        result = new FeatureSetTableInfo();
                        result.GeometryColumnName = colName;
                        break;
                    }
                }

                if (result != null && rdr.HasRows)
                {
                    rdr.Read();
                    var blob = rdr[result.GeometryColumnName] as byte[];
                    var geom = wkbr.Read(blob);
                    result.GeometryType = geom.FeatureType;
                }

                return(result);
            }
        }
예제 #2
0
        /// <summary>
        /// Finds a list of all valid geometry columns in the database
        /// </summary>
        /// <param name="connString">connection string</param>
        /// <returns>the list of geometry columns</returns>
        public IEnumerable <FeatureSetTableInfo> ListFeatureSets(SQLiteConnection conn)
        {
            string version = GetSpatiaLiteDbVersion(conn);

            if (version.Length == 0)
            {
                this.ErrorMessage = "The database doesn't appear to be valid.";
                log.Error(this.ErrorMessage);
                return(new List <FeatureSetTableInfo>());
            }

            if (!version.StartsWith("4"))
            {
                this.ErrorMessage = "The database software requires version 4 of the SpatiaLite framework.";
                log.Error(this.ErrorMessage);
                return(new List <FeatureSetTableInfo>());
            }

            var           sql = "SELECT f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns";
            SQLiteCommand cmd = null;

            try
            {
                var columns = new List <FeatureSetTableInfo>();
                cmd = new SQLiteCommand(sql, conn);

                var r = cmd.ExecuteReader();
                while (r.Read())
                {
                    var info = new FeatureSetTableInfo();
                    info.TableName          = Convert.ToString(r["f_table_name"]);
                    info.GeometryColumnName = Convert.ToString(r["f_geometry_column"]);
                    info.GeometryType       = GetGeometryType(Convert.ToString(r["geometry_type"]));
                    CoordinateDimension coordDim;
                    if (!Enum.TryParse(Convert.ToString(r["coord_dimension"]), out coordDim))
                    {
                        info.CoordDimension = CoordinateDimension.XY;
                    }
                    info.SRID = Convert.ToInt32(r["srid"]);
                    info.SpatialIndexEnabled = false;
                    columns.Add(info);
                }

                return(columns);
            }
            catch (Exception ex)
            {
                log.Error("Failed to retrieve feature set listing from database: {0}", ex.Message);
                return(new List <FeatureSetTableInfo>());
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }
예제 #3
0
        /// <summary>
        /// Reads the complete feature set from the database
        /// </summary>
        /// <param name="connString">sqlite db connection string</param>
        /// <param name="featureSetInfo">information about the table</param>
        /// <returns>the resulting feature set</returns>
        public bool TryReadFeatureSet(SQLiteConnection conn, FeatureSetTableInfo featureSetInfo, out IFeatureSet featureSet)
        {
            featureSet = null;
            if (featureSetInfo == null)
            {
                return(false);
            }
            var sql = string.Format("SELECT * FROM {0}", featureSetInfo.TableName);

            return(TryReadFeatureSet(conn, featureSetInfo, sql, out featureSet));
        }
예제 #4
0
        /// <summary>
        /// Reads the feature set from the database, satisfying the given SQL query
        /// </summary>
        /// <param name="connString">sqlite db connection string</param>
        /// <param name="featureSetInfo">information about the table</param>
        /// <param name="sqlFilter">the sql query; to select all: "SELECT * FROM TableName"</param>
        /// <returns>the resulting feature set</returns>
        public bool TryReadFeatureSet(SQLiteConnection conn, FeatureSetTableInfo featureSetInfo, string sqlFilter, out IFeatureSet featureSet)
        {
            featureSet = null;
            if (featureSetInfo == null)
            {
                return(false);
            }

            var fs = new SpatiaLiteFeatureSet(featureSetInfo.GeometryType);

            fs.IndexMode = false; //setting the initial index mode..

            SQLiteCommand cmd = null;

            try
            {
                cmd = new SQLiteCommand(sqlFilter, conn);

                var wkbr = new SpatiaLiteWkbReader();

                var rdr = cmd.ExecuteReader();

                var columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
                while (rdr.Read())
                {
                    var wkb  = rdr[featureSetInfo.GeometryColumnName] as byte[];
                    var geom = wkbr.Read(wkb);

                    var newFeature = fs.AddFeature(geom);

                    //populate the attributes
                    foreach (var colName in columnNames)
                    {
                        newFeature.DataRow[colName] = rdr[colName];
                    }
                }

                fs.Name = featureSetInfo.TableName;

                //TODO: look into this...
                //HACK required for selection to work properly
                fs.IndexMode = true;

                // assign projection
                ProjectionInfo proj;
                if (featureSetInfo.SRID >= 0)
                {
                    try
                    {
                        proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
                    }
                    catch (Exception ex)
                    {
                        log.Warning("Unable to find projection info from EPSG code {0}, using default: {1}", featureSetInfo.SRID, ex.Message);
                        proj = new ProjectionInfo();
                    }
                }
                else
                {
                    proj = new ProjectionInfo();
                }
                fs.Projection = proj;

                featureSet = fs;
                return(true);
            }
            catch (Exception ex)
            {
                log.Error("Unable to read feature set {0} using SQL \"{1}\": {2}", featureSetInfo.TableName, sqlFilter, ex.Message);
                return(false);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }
예제 #5
0
        /// <summary>
        /// Creates a feature set table using the given SQLite database connection.  Catches exceptions and returns creation status.
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="metadata"></param>
        /// <param name="geomMetadata"></param>
        /// <returns></returns>
        private bool TryCreateFeatureSetTable(SQLiteConnection conn, SQLiteTableMetadata metadata, FeatureSetTableInfo geomMetadata)
        {
            try
            {
                string sql = metadata.GetCreateSqlCode();
                log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);

                var transaction = conn.BeginTransaction();

                var cmd   = new SQLiteCommand(sql, conn);
                int nRows = cmd.ExecuteNonQuery();

                log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);

                sql = String.Format("SELECT AddGeometryColumn('{0}', '{1}', {2}, '{3}', '{4}', 1);", // last parameter means that only NOT NULL geometries will be accepted
                                    metadata.TableName, geomMetadata.GeometryColumnName, geomMetadata.SRID,
                                    geomMetadata.GetGeometryType(), geomMetadata.CoordDimension.ToString().ToUpper());

                log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);

                cmd = new SQLiteCommand(sql, conn);

                nRows = cmd.ExecuteNonQuery();
                log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                this.ErrorMessage = string.Format("Unable to create feature set table \"{0}\": {1}", metadata.TableName, ex.Message);
                log.Error(this.ErrorMessage);
                return(false);
            }

            return(true);
        }
예제 #6
0
        /// <summary>
        /// Save a feature set to a SQLite database. The FeatureSet is saved to a table
        /// with the same name as FeatureSet.Name.  Existing tables will get dropped.
        /// </summary>
        /// <param name="connstring">database connection string</param>
        /// <param name="fs">the feature set to save</param>
        public bool TrySaveFeatureSet(SQLiteConnection conn, DotSpatial.Data.IFeatureSet fs)
        {
            var meta = new SQLiteTableMetadata(fs.Name);

            if (meta.TableName == null || meta.TableName.Length == 0)
            {
                this.ErrorMessage = "Invalid table name provided";
                log.Error(this.ErrorMessage);
                return(false);
            }

            int srid = 0;

            if (fs.Projection == null)
            {
                srid = -1; // unknown
            }
            else
            {
                srid = fs.Projection.AuthorityCode;
            }

            using (SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + meta.TableName, conn))
            {
                log.Debug("TrySaveFeatureSet: dropped table {0} if it exists", meta.TableName);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    this.ErrorMessage = string.Format("SQL execution issue 1: {0}", ex.Message);
                    log.Error(this.ErrorMessage);
                    return(false);
                }
            }

            string colNames = "";
            string vals     = "";

            List <SQLiteParameter> parms        = new List <SQLiteParameter>();
            List <string>          colNamesInFS = new List <string>();

            foreach (DataColumn c in fs.DataTable.Columns)
            {
                meta.Columns.Add(new Column(c.ColumnName, c.DataType));
                colNames += "[" + c.ColumnName + "], ";
                vals     += "?, ";
                parms.Add(new SQLiteParameter());
                colNamesInFS.Add(c.ColumnName);
            }

            var geomMeta = new FeatureSetTableInfo(meta.TableName, fs.FeatureType, srid);

            colNames += "[" + geomMeta.GeometryColumnName + "]";
            vals     += "ST_GeomFromWKB(?, " + geomMeta.SRID + ")";
            parms.Add(new SQLiteParameter(DbType.Object));

            if (!TryCreateFeatureSetTable(conn, meta, geomMeta))
            {
                return(false);
            }
            log.Debug("Created feature set table");

            var trans = conn.BeginTransaction();

            try
            {
                SQLiteCommand insCmd = new SQLiteCommand(conn);
                insCmd.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});", meta.TableName, colNames, vals);
                insCmd.Parameters.AddRange(parms.ToArray());

                for (int i = 0; i < fs.Features.Count; i++)
                {
                    try
                    {
                        UpdateCommandParameters(fs.Features[i], parms, colNamesInFS, srid);
                        //log.Debug("Starting insert of feature {0}: sql=\"{1}\" parameters=\"{2}\"", i, insCmd.CommandText, GetParametersAsString(insCmd.Parameters));
                        //log.Debug(string.Join("|", Array.ConvertAll(fs.Features[i].BasicGeometry.Coordinates.ToArray(), x => x.X.ToString() + "," + x.Y.ToString())));
                        insCmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        this.ErrorMessage = string.Format("SQL execution issue 2: {0}", ex.Message);
                        log.Error(this.ErrorMessage);
                        return(false);
                    }
                }
                log.Debug("Inserted {0} features", fs.Features.Count);
            }
            catch (KeyNotFoundException kex)
            {
                log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, kex.Message);
                trans.Rollback();
                return(false);
            }
            catch (Exception ex)
            {
                log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, ex.Message);
                trans.Rollback();
                return(false);
            }

            trans.Commit();

            log.Debug("Saved feature set into the table");

            return(true);
        }