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