/// <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); } }
/// <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(); } } }
/// <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)); }
/// <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(); } } }
/// <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); }
/// <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); }