/// <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 List <GeometryColumnInfo> GetGeometryColumns(string connString) { List <GeometryColumnInfo> lst = new List <GeometryColumnInfo>(); string sql = "SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns"; using (SQLiteCommand cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); SQLiteDataReader r = cmd.ExecuteReader(); while (r.Read()) { GeometryColumnInfo gci = new GeometryColumnInfo(); gci.TableName = Convert.ToString(r["f_table_name"]); gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]); gci.GeometryType = Convert.ToString(r["type"]); gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]); gci.SRID = Convert.ToInt32(r["srid"]); gci.SpatialIndexEnabled = false; lst.Add(gci); } cmd.Connection.Close(); } return(lst); }
/// <summary> /// Reads the complete feature set from the database /// </summary> /// <param name="connString">sqlite db connection string</param> /// <param name="sqlQuery">the SQL Query string</param> /// <returns>the resulting feature set</returns> public IFeatureSet ReadFeatureSet(string connString, string sqlQuery) { //RunInitialCommands(connString); //Find the geometry type and geometry column GeometryColumnInfo geomInfo = FindGeometryColumnInfo(connString, sqlQuery); return(ReadFeatureSet(connString, geomInfo, sqlQuery)); }
/// <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 List <GeometryColumnInfo> GetGeometryColumns(string connString) { var lst = new List <GeometryColumnInfo>(); var sql = "SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns"; using (var cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); var r = cmd.ExecuteReader(); while (r.Read()) { var gci = new GeometryColumnInfo { TableName = Convert.ToString(r["f_table_name"]), GeometryColumnName = Convert.ToString(r["f_geometry_column"]), GeometryType = Convert.ToString(r["type"]) }; try { gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]); } catch (Exception) { var coords = Convert.ToString(r["coord_dimension"]); switch (coords) { case "XY": gci.CoordDimension = 2; break; case "XYZ": case "XYM": gci.CoordDimension = 3; break; case "XYZM": gci.CoordDimension = 4; break; } } gci.Srid = Convert.ToInt32(r["srid"]); gci.SpatialIndexEnabled = false; lst.Add(gci); } cmd.Connection.Close(); } return(lst); }
// when clicking "OK" private void BtnOkClick(object sender, EventArgs e) { foreach (DataGridViewRow r in dgGeometryColumns.SelectedRows) { GeometryColumnInfo item = r.DataBoundItem as GeometryColumnInfo; if (item != null) { IFeatureSet fs = _slh.ReadFeatureSet(item); _mainMap.Layers.Add(fs); } } Close(); }
/// <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> /// <param name="sql">the sql query</param> /// <returns>the resulting feature set</returns> public IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo, string sql) { DataTable tab = new DataTable(); FeatureType fType = GetGeometryType(featureSetInfo.GeometryType); FeatureSet fs = new FeatureSet(fType); fs.IndexMode = false; //setting the initial index mode.. using (SQLiteCommand cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); RunInitialCommands(cmd.Connection); //DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader(); SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader(); SQLiteDataReader rdr = cmd.ExecuteReader(); string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr); int numColumns = fs.DataTable.Columns.Count; while (rdr.Read()) { byte[] wkb = rdr[featureSetInfo.GeometryColumnName] as byte[]; IGeometry geom = wkbr.Read(wkb); IFeature newFeature = fs.AddFeature(geom); //populate the attributes foreach (string colName in columnNames) { newFeature.DataRow[colName] = rdr[colName]; } } cmd.Connection.Close(); fs.Name = featureSetInfo.TableName; //HACK required for selection to work properly fs.IndexMode = true; //assign projection ProjectionInfo proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID); fs.Projection = proj; return(fs); } }
// finds out the geometry column information.. private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery) { GeometryColumnInfo result = null; using (var cmd = CreateCommand(connString, sqlQuery)) { cmd.Connection.Open(); RunInitialCommands(cmd.Connection); var wkbr = new SpatiaLiteWkbReader(); var rdr = cmd.ExecuteReader(CommandBehavior.SingleRow); var schemaTable = rdr.GetSchemaTable(); if (schemaTable == null) { return(null); } 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 GeometryColumnInfo { 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.GeometryType; } cmd.Connection.Close(); return(result); } }
// when clicking "OK" private void BtnOkClick(object sender, EventArgs e) { SpatiaLiteHelper slh = new SpatiaLiteHelper(); foreach (DataGridViewRow r in dgGeometryColumns.Rows) { if (r.Selected) { GeometryColumnInfo item = r.DataBoundItem as GeometryColumnInfo; if (item != null) { IFeatureSet fs = slh.ReadFeatureSet(_connString, item); _mainMap.Layers.Add(fs); } } } }
/// <summary> /// Reads the complete feature set from the database /// </summary> /// <param name="featureSetInfo">information about the table</param> /// <param name="sql">the sql query</param> /// <returns>the resulting feature set</returns> public IFeatureSet ReadFeatureSet(GeometryColumnInfo featureSetInfo, string sql) { var fType = GetGeometryType(featureSetInfo.GeometryType); SpatiaLiteFeatureSet fs = new SpatiaLiteFeatureSet(fType) { IndexMode = true, // setting the initial index mode.. Name = featureSetInfo.TableName, Filename = SqLiteHelper.GetSqLiteFileName(ConnectionString), LayerName = featureSetInfo.TableName }; using (var cmd = CreateCommand(ConnectionString, sql)) { cmd.Connection.Open(); 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]; } } cmd.Connection.Close(); // assign projection if (featureSetInfo.Srid > 0) { var proj = ProjectionInfo.FromEpsgCode(featureSetInfo.Srid); fs.Projection = proj; } return(fs); } }
/// <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 List<GeometryColumnInfo> GetGeometryColumns(string connString) { List<GeometryColumnInfo> lst = new List<GeometryColumnInfo>(); string sql = "SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns"; using (SQLiteCommand cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); SQLiteDataReader r = cmd.ExecuteReader(); while (r.Read()) { GeometryColumnInfo gci = new GeometryColumnInfo(); gci.TableName = Convert.ToString(r["f_table_name"]); gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]); gci.GeometryType = Convert.ToString(r["type"]); gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]); gci.SRID = Convert.ToInt32(r["srid"]); gci.SpatialIndexEnabled = false; lst.Add(gci); } cmd.Connection.Close(); } return lst; }
/// <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 IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo) { var sql = $"SELECT * FROM {featureSetInfo.TableName}"; return(ReadFeatureSet(connString, featureSetInfo, sql)); }
//finds out the geometry column information.. private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery) { GeometryColumnInfo result = null; using (SQLiteCommand cmd = CreateCommand(connString, sqlQuery)) { cmd.Connection.Open(); RunInitialCommands(cmd.Connection); SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader(); SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow); DataTable schemaTable = rdr.GetSchemaTable(); foreach (DataRow r in schemaTable.Rows) { string colName = Convert.ToString(r["ColumnName"]); string colDataType = Convert.ToString(r["DataType"]); //if BLOB, then assume geometry column if (Type.GetType(colDataType) == typeof(byte[])) { result = new GeometryColumnInfo(); result.GeometryColumnName = colName; break; } } if (result != null && rdr.HasRows) { rdr.Read(); byte[] blob = rdr[result.GeometryColumnName] as byte[]; IGeometry geom = wkbr.Read(blob); result.GeometryType = geom.GeometryType; } cmd.Connection.Close(); return result; } }
/// <summary> /// Reads the complete feature set from the database. /// </summary> /// <param name="featureSetInfo">information about the table.</param> /// <returns>the resulting feature set.</returns> public IFeatureSet ReadFeatureSet(GeometryColumnInfo featureSetInfo) { var sql = $"SELECT * FROM '{featureSetInfo.TableName}'"; return(ReadFeatureSet(featureSetInfo, sql)); }
/// <summary> /// Finds a list of all valid geometry columns in the database. /// </summary> /// <returns>the list of geometry columns.</returns> public List <GeometryColumnInfo> GetGeometryColumns() { if (_version4Plus) { var lst = new List <GeometryColumnInfo>(); var sql = "SELECT f_table_name, f_geometry_column, geometry_type, srid FROM geometry_columns"; using (var cmd = CreateCommand(ConnectionString, sql)) { cmd.Connection.Open(); var r = cmd.ExecuteReader(); while (r.Read()) { var gci = new GeometryColumnInfo { TableName = Convert.ToString(r["f_table_name"]), GeometryColumnName = Convert.ToString(r["f_geometry_column"]), Srid = Convert.ToInt32(r["srid"]), SpatialIndexEnabled = false }; if (int.TryParse(r["geometry_type"].ToString(), out int geometryType)) { AssignGeometryTypeStringAndCoordDimension(geometryType, gci); } lst.Add(gci); } cmd.Connection.Close(); } return(lst); } else { var lst = new List <GeometryColumnInfo>(); var sql = "SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns"; using (var cmd = CreateCommand(ConnectionString, sql)) { cmd.Connection.Open(); var r = cmd.ExecuteReader(); while (r.Read()) { var gci = new GeometryColumnInfo { TableName = Convert.ToString(r["f_table_name"]), GeometryColumnName = Convert.ToString(r["f_geometry_column"]), GeometryType = Convert.ToString(r["type"]), Srid = Convert.ToInt32(r["srid"]), SpatialIndexEnabled = false }; try { gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]); } catch (Exception) { var coords = Convert.ToString(r["coord_dimension"]); switch (coords) { case "XY": gci.CoordDimension = 2; break; case "XYZ": case "XYM": gci.CoordDimension = 3; break; case "XYZM": gci.CoordDimension = 4; break; } } lst.Add(gci); } cmd.Connection.Close(); } return(lst); } }
/// <summary> /// Gets the GeometryType string and the CoordDimension from the given geometryTypeInt and assigns them to the given GeometryColumnInfo. /// </summary> /// <param name="geometryTypeInt">geometryTypeInt that indicates the GeometryType and CoordDimension that was used.</param> /// <param name="gci">The GeometryColumnInfo the values get assigned to.</param> private static void AssignGeometryTypeStringAndCoordDimension(int geometryTypeInt, GeometryColumnInfo gci) { var dimensionBase = geometryTypeInt / 1000; geometryTypeInt = geometryTypeInt - (dimensionBase * 1000); // get only the last number switch (geometryTypeInt) { case 1: gci.GeometryType = "point"; break; case 2: gci.GeometryType = "linestring"; break; case 3: gci.GeometryType = "polygon"; break; case 4: gci.GeometryType = "multipoint"; break; case 5: gci.GeometryType = "multilinestring"; break; case 6: gci.GeometryType = "multipolygon"; break; case 7: gci.GeometryType = "geometrycollection"; break; default: gci.GeometryType = "geometry"; break; } if (dimensionBase >= 3) { gci.CoordDimension = 4; } else if (dimensionBase >= 1) { gci.CoordDimension = 3; } else { gci.CoordDimension = 2; } }
/// <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 IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo) { string sql = String.Format("SELECT * FROM {0}", featureSetInfo.TableName); return ReadFeatureSet(connString, featureSetInfo, sql); }
/// <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> /// <param name="sql">the sql query</param> /// <returns>the resulting feature set</returns> public IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo, string sql) { DataTable tab = new DataTable(); FeatureType fType = GetGeometryType(featureSetInfo.GeometryType); FeatureSet fs = new FeatureSet(fType); fs.IndexMode = false; //setting the initial index mode.. using (SQLiteCommand cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); RunInitialCommands(cmd.Connection); //DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader(); SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader(); SQLiteDataReader rdr = cmd.ExecuteReader(); string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr); int numColumns = fs.DataTable.Columns.Count; while (rdr.Read()) { byte[] wkb = rdr[featureSetInfo.GeometryColumnName] as byte[]; IGeometry geom = wkbr.Read(wkb); IFeature newFeature = fs.AddFeature(geom); //populate the attributes foreach (string colName in columnNames) { newFeature.DataRow[colName] = rdr[colName]; } } cmd.Connection.Close(); fs.Name = featureSetInfo.TableName; //HACK required for selection to work properly fs.IndexMode = true; //assign projection ProjectionInfo proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID); fs.Projection = proj; return fs; } }
/// <summary> /// Gets the GeometryType string and the CoordDimension from the given geometryTypeInt and assigns them to the given GeometryColumnInfo. /// </summary> /// <param name="geometryTypeInt">geometryTypeInt that indicates the GeometryType and CoordDimension that was used.</param> /// <param name="gci">The GeometryColumnInfo the values get assigned to.</param> private static void AssignGeometryTypeStringAndCoordDimension(int geometryTypeInt, GeometryColumnInfo gci) { var dimensionBase = geometryTypeInt / 1000; geometryTypeInt -= dimensionBase * 1000; // get only the last number gci.GeometryType = geometryTypeInt switch { 1 => "point", 2 => "linestring", 3 => "polygon", 4 => "multipoint", 5 => "multilinestring", 6 => "multipolygon", 7 => "geometrycollection", _ => "geometry", }; if (dimensionBase >= 3) { gci.CoordDimension = 4; } else if (dimensionBase >= 1) { gci.CoordDimension = 3; } else { gci.CoordDimension = 2; } }
/// <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 IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo) { var sql = String.Format("SELECT * FROM {0}", featureSetInfo.TableName); return(ReadFeatureSet(connString, featureSetInfo, sql)); }
/// <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 List<GeometryColumnInfo> GetGeometryColumns(string connString) { var lst = new List<GeometryColumnInfo>(); var sql = "SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns"; using (var cmd = CreateCommand(connString, sql)) { cmd.Connection.Open(); var r = cmd.ExecuteReader(); while (r.Read()) { var gci = new GeometryColumnInfo(); gci.TableName = Convert.ToString(r["f_table_name"]); gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]); gci.GeometryType = Convert.ToString(r["type"]); try { gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]); } catch (Exception) { var coords = Convert.ToString(r["coord_dimension"]); switch (coords) { case "XY": gci.CoordDimension = 2; break; case "XYZ": case "XYM": gci.CoordDimension = 3; break; case "XYZ;": gci.CoordDimension = 4; break; } } gci.SRID = Convert.ToInt32(r["srid"]); gci.SpatialIndexEnabled = false; lst.Add(gci); } cmd.Connection.Close(); } return lst; }