ThinkGeo Cloud
ThinkGeo UI Controls
ThinkGeo Open Source
Help and Support
External Resources
ThinkGeo Cloud
ThinkGeo UI Controls
ThinkGeo Open Source
Help and Support
External Resources
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Linq; using System.Threading.Tasks; using System.Windows; namespace WorldMapKitDataExtractor { /// <summary> /// Interaction logic for App.xaml /// </summary> public partial class App : Application { } }
using System; using System.Collections.ObjectModel; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Forms; using ThinkGeo.MapSuite.Core; namespace WorldMapKitDataExtractor { /// <summary> /// Interaction logic for MainWindow.xaml /// </summary> public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); } private void btnOk_Click(object sender, RoutedEventArgs e) { if (ValidateInput()) { EnableUI(false); Extractor extractor = new Extractor(txtInputDatabase.Text, txtOutputDatabase.Text); extractor.InputDataPrj = ManagedProj4Projection.GetEpsgParametersString(int.Parse(txtInputDatabaseSrid.Text)); extractor.UpdateStatus = value => { Dispatcher.Invoke(() => { lblStatus.Content = string.Format("Status: {0}", value); }); }; extractor.PreserveCountryLevelData = chkPreserveCountryLevelData.IsChecked == true; Collection<Feature> boundariesFeatures = new Collection<Feature>(); if (rbtnShapeFile.IsChecked == true) { ShapeFileFeatureLayer shapeFileLayer = new ShapeFileFeatureLayer(txtShapeFile.Text); shapeFileLayer.Open(); string prjFilePath = Path.ChangeExtension(txtShapeFile.Text, ".prj"); if (File.Exists(prjFilePath)) extractor.BoundaryPrj = ManagedProj4Projection.ConvertPrjToProj4(File.ReadAllText(prjFilePath)); else extractor.BoundaryPrj = ManagedProj4Projection.GetEpsgParametersString(int.Parse(shapeFileSrid.Text)); var selectItems = featureTable.SelectedItems.Count == 0 ? featureTable.Items.Cast<DataRowView>() : featureTable.SelectedItems.Cast<DataRowView>(); foreach (DataRowView row in selectItems) { boundariesFeatures.Add(shapeFileLayer.QueryTools.GetFeatureById(row.Row[0].ToString(), ReturningColumnsType.NoColumns)); } } else { var uppperLeftPoint = txtUpperLeftPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray(); var lowerRightPoint = txtLowerRightPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray(); RectangleShape boundingBox = new RectangleShape(uppperLeftPoint[0], uppperLeftPoint[1], lowerRightPoint[0], lowerRightPoint[1]); extractor.BoundaryPrj = ManagedProj4Projection.GetEpsgParametersString(int.Parse(boundarySrid.Text)); boundariesFeatures.Add(new Feature(boundingBox)); } Task.Run(() => extractor.ExtractDataByShape(boundariesFeatures)).ContinueWith(task => EnableUI(true)); } } private bool ValidateInput() { bool result = true; StringBuilder errorMessage = new StringBuilder(); int srid; if (!File.Exists(txtInputDatabase.Text)) errorMessage.AppendLine("Please enter currect input database file path."); if (!int.TryParse(txtInputDatabaseSrid.Text, out srid)) errorMessage.AppendLine("Please enter currect SRID for input database."); if (string.IsNullOrEmpty(txtOutputDatabase.Text)) errorMessage.AppendLine("Please enter currect output database file path."); if (rbtnShapeFile.IsChecked == true) { if (!File.Exists(txtShapeFile.Text)) errorMessage.AppendLine("Please enter currect shape file path."); if (!File.Exists(Path.ChangeExtension(txtShapeFile.Text, ".prj")) && !int.TryParse(shapeFileSrid.Text, out srid)) errorMessage.AppendLine("Please enter currect SRID for boundary shaplefile."); } if (rbtnBoundingBox.IsChecked == true && !int.TryParse(boundarySrid.Text, out srid)) errorMessage.AppendLine("Please enter currect SRID for boundary."); if (!string.IsNullOrEmpty(errorMessage.ToString())) { System.Windows.MessageBox.Show(errorMessage.ToString(), "Warning"); result = false; } return result; } private void EnableUI(bool enable) { Dispatcher.Invoke(() => { btnOk.IsEnabled = enable; btnCancel.IsEnabled = enable; btnOutputDatabase.IsEnabled = enable; btnOpenShapeFile.IsEnabled = enable; btnInputDatabase.IsEnabled = enable; }); } private void ExtractorTypeChanged(object sender, RoutedEventArgs e) { if (IsLoaded) { if (rbtnBoundingBox.IsChecked == true) { shapeFilePanel.Visibility = Visibility.Hidden; boundingBoxPanel.Visibility = Visibility.Visible; } else { shapeFilePanel.Visibility = Visibility.Visible; boundingBoxPanel.Visibility = Visibility.Hidden; } } } private void btnInputDatabase_Click(object sender, RoutedEventArgs e) { OpenFileDialog dlg = new OpenFileDialog(); dlg.DefaultExt = ".sqlite"; dlg.Filter = "SQLite Database (.sqlite)|*.sqlite"; if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) txtInputDatabase.Text = dlg.FileName; } private void btnOutputDatabase_Click(object sender, RoutedEventArgs e) { SaveFileDialog dlg = new SaveFileDialog(); dlg.DefaultExt = ".sqlite"; dlg.Filter = "SQLite Database (.sqlite)|*.sqlite"; if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) txtOutputDatabase.Text = dlg.FileName; } private void btnOpenShapeFile_Click(object sender, RoutedEventArgs e) { OpenFileDialog dlg = new OpenFileDialog(); dlg.DefaultExt = ".shp"; dlg.Filter = "Shape File (.shp)|*.shp"; if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) txtShapeFile.Text = dlg.FileName; ShapeFileFeatureSource shapeFile = new ShapeFileFeatureSource(txtShapeFile.Text); shapeFile.Open(); Collection<Feature> features = shapeFile.GetAllFeatures(ReturningColumnsType.AllColumns); shapeFile.Close(); DataTable dt = new DataTable(); bool isColumnName = true; foreach (var feature in features) { var columnValues = feature.ColumnValues; if (isColumnName) { dt.Columns.Add(new DataColumn("FeatureId")); foreach (var column in columnValues.Keys) { dt.Columns.Add(new DataColumn(column)); } isColumnName = false; } DataRow row = dt.NewRow(); row["FeatureId"] = feature.Id; foreach (var item in columnValues) { row[item.Key] = item.Value; } dt.Rows.Add(row); } featureTable.ItemsSource = dt.DefaultView; } private void btnCancel_Click(object sender, RoutedEventArgs e) { Close(); } } }
using System.Collections.Generic; using System.Collections.ObjectModel; namespace ThinkGeo.MapSuite.Core { public static class DynamicGridPolygonIndexer { //Get all Cells without cutting polygon. restricts how many levels to iterate based on minimum cell area public static GeoCollection<Feature> GetIntersectingGridCells(BaseShape source, GeographyUnit sourceUnit, double minimumCellArea, AreaUnit minimumCellAreaUnit) { //find bounding box of set of boundary RectangleShape boundingBox = source.GetBoundingBox(); Stack<RectangleShape> processingStack = new Stack<RectangleShape>(); processingStack.Push(boundingBox); Collection<RectangleShape> insideRectangles = new Collection<RectangleShape>(); Collection<RectangleShape> intersectingRectangles = new Collection<RectangleShape>(); while (processingStack.Count > 0) { RectangleShape currentBoundingBox = processingStack.Pop(); bool toBeSplit = false; if (source.Contains(currentBoundingBox)) { //if inside a boundary add to inside rectangles holder insideRectangles.Add(currentBoundingBox); } else if (source.Intersects(currentBoundingBox)) { //if intersecting //we multiply the minimum cell area by 9 to do this check because if it is below it we are splitting it into 9 equal parts //this guarantees that after it is split they new cells are still larger than the minimum area. if (currentBoundingBox.GetArea(sourceUnit, minimumCellAreaUnit) > minimumCellArea * 9) { //if it is too big it needs to be split toBeSplit = true; } else { //if small enough add to intersecting rectangles holder intersectingRectangles.Add(currentBoundingBox); } } if (toBeSplit) { //split bounding boxes Collection<RectangleShape> splitBoundingBoxes = splitRectangles(currentBoundingBox); //and add each to stack foreach (RectangleShape splitBoundingBox in splitBoundingBoxes) { processingStack.Push(splitBoundingBox); } } } GeoCollection<Feature> cells = new GeoCollection<Feature>(); foreach (var rect in insideRectangles) { RectangleShape bboxShape = rect; Feature bbox = new Feature(bboxShape); bbox.ColumnValues["type"] = "in"; cells.Add(bbox); } foreach (var rect in intersectingRectangles) { RectangleShape bboxShape = rect; Feature bbox = new Feature(bboxShape); bbox.ColumnValues["type"] = "intersecting"; cells.Add(bbox); } return cells; } private static Collection<RectangleShape> splitRectangles(RectangleShape currentBoundingBox) { double x1, x2, x3, x4, y1, y2, y3, y4; x1 = currentBoundingBox.UpperLeftPoint.X; x4 = currentBoundingBox.UpperRightPoint.X; x2 = x1 + (x4 - x1) / 3.0; x3 = x4 - (x4 - x1) / 3.0; y1 = currentBoundingBox.UpperLeftPoint.Y; y4 = currentBoundingBox.LowerLeftPoint.Y; y2 = y1 + (y4 - y1) / 3.0; y3 = y4 - (y4 - y1) / 3.0; Collection<RectangleShape> splitBoundingBoxes = new Collection<RectangleShape>(); splitBoundingBoxes.Add(new RectangleShape(x1, y1, x2, y2)); splitBoundingBoxes.Add(new RectangleShape(x2, y1, x3, y2)); splitBoundingBoxes.Add(new RectangleShape(x3, y1, x4, y2)); splitBoundingBoxes.Add(new RectangleShape(x1, y2, x2, y3)); splitBoundingBoxes.Add(new RectangleShape(x2, y2, x3, y3)); splitBoundingBoxes.Add(new RectangleShape(x3, y2, x4, y3)); splitBoundingBoxes.Add(new RectangleShape(x1, y3, x2, y4)); splitBoundingBoxes.Add(new RectangleShape(x2, y3, x3, y4)); splitBoundingBoxes.Add(new RectangleShape(x3, y3, x4, y4)); return splitBoundingBoxes; } } }
using WorldMapKitDataExtractor; using System; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Data.SQLite; using System.IO; using System.Linq; using System.Text; namespace ThinkGeo.MapSuite.Core { public class Extractor { private string inputDataPath; private string outputDataPath; private string inputDataPrj; private string boundaryPrj; private bool preserveCountryLevelData; public Extractor(string inputDataPath, string outputDataPath, string inputDataPrj = null, string boundaryPrj = null, bool preserveCountryLevelData = true) { this.inputDataPath = inputDataPath; this.outputDataPath = outputDataPath; this.inputDataPrj = inputDataPrj; this.boundaryPrj = boundaryPrj; this.preserveCountryLevelData = preserveCountryLevelData; } public Action<string> UpdateStatus { get; set; } public string InputDataPath { get { return inputDataPath; } set { inputDataPath = value; } } public string OutputDataPath { get { return outputDataPath; } set { outputDataPath = value; } } public string InputDataPrj { get { return inputDataPrj; } set { inputDataPrj = value; } } public string BoundaryPrj { get { return boundaryPrj; } set { boundaryPrj = value; } } public bool PreserveCountryLevelData { get { return preserveCountryLevelData; } set { preserveCountryLevelData = value; } } public void ExtractDataByShape(IEnumerable<Feature> boundaryFeatures) { UpdateStatus("Begain to extract world map kit data by shape file."); UpdateStatus("Copying source database to output database."); File.Copy(inputDataPath, outputDataPath, true); FileInfo targetDatabaseFileInfo = new FileInfo(outputDataPath); targetDatabaseFileInfo.IsReadOnly = false; SQLiteConnection connection = new SQLiteConnection($"Data Source={outputDataPath};Version=3;"); connection.Open(); var projectedBoundaryFeatures = ProjectedBoundaryFeatures(boundaryFeatures); DeleteDataByBoundary(projectedBoundaryFeatures, connection); ProcessBaselandByShapes(boundaryFeatures, connection); UpdateStatus("Excuting Vacuum for output database."); ExecuteNonQueryCommand("VACUUM;", connection); connection.Close(); connection.Dispose(); UpdateStatus("Done for extracting world map kit data!"); } private IEnumerable<Feature> ProjectedBoundaryFeatures(IEnumerable<Feature> boundaryFeatures) { Proj4Projection proj = new Proj4Projection(boundaryPrj, inputDataPrj); proj.Open(); foreach (Feature feature in boundaryFeatures) { yield return proj.ConvertToExternalProjection(feature); } proj.Close(); } private string CreateBoundaryIndexPathFile(string indexTable, IEnumerable<Feature> boundaryFeatures) { UpdateStatus("Creating temp bounding box index file."); //Create the temp Bounding Box Index Database string bboxIndexPathFileName = $@"{Path.GetDirectoryName(inputDataPath)}\boundary_index.sqlite.temp"; string bboxIndexConnectionString = $@"Data Source={bboxIndexPathFileName};Version=3;"; Collection<string> tables = SqliteFeatureSource.GetTableNames(bboxIndexConnectionString); if (!tables.Contains(indexTable)) SqliteFeatureSource.CreateTable(bboxIndexConnectionString, indexTable, new Collection<SqliteColumn> { new SqliteColumn("boundaryID", SqliteColumnType.Integer), new SqliteColumn("type", SqliteColumnType.Text) }, GeographyUnit.DecimalDegree); SqliteFeatureSource bboxIndexFeatureSource = new SqliteFeatureSource(bboxIndexConnectionString, indexTable, "id", "geometry"); bboxIndexFeatureSource.Open(); bboxIndexFeatureSource.BeginTransaction(); foreach (Feature boundary in boundaryFeatures) { UpdateStatus($"Getting grid cells by boundary feature: {boundary.Id}."); GeoCollection<Feature> cells = DynamicGridPolygonIndexer.GetIntersectingGridCells(boundary.GetShape(), GeographyUnit.DecimalDegree, 10, AreaUnit.SquareMiles); foreach (Feature cell in cells) { bboxIndexFeatureSource.AddFeature(cell); } } bboxIndexFeatureSource.CommitTransaction(); return bboxIndexPathFileName; } private void DeleteDataByBoundary(IEnumerable<Feature> boundaryFeatures, SQLiteConnection connection) { UpdateStatus("Deleting data by bounding box index."); string bboxtable = "bboxIndex"; string boundaryIndexPathFile = CreateBoundaryIndexPathFile(bboxtable, boundaryFeatures); //Attach to a bbox index database on which to extract data ExecuteNonQueryCommand($"ATTACH DATABASE '{boundaryIndexPathFile}' as bbox;", connection); Collection<Table> tables = GetTables(connection); foreach (var table in tables) { UpdateStatus($"Deleting table data by bounding box index {table.TableName}."); DeleteTableDataByBoundingBoxIndex(table, connection, bboxtable); } Collection<View> views = GetViews(connection); foreach (var view in views) { UpdateStatus($"Deleting view indexes by bounding box index {view.Name}."); DeleteViewIndexesByBoundingBoxIndex(view, connection, bboxtable); } foreach (var groupView in views.GroupBy(v => v.BaseTable)) { UpdateStatus($"Deleting base table by view indexes {groupView.Key}."); DeleteBaseTableDataByViewsIndexes(groupView.Key, groupView.Select(v => v.Name).ToList(), connection, bboxtable); } UpdateStatus("Detaching database bbox."); ExecuteNonQueryCommand("DETACH DATABASE 'bbox'; PRAGMA journal_mode=OFF;", connection); File.Delete(boundaryIndexPathFile); } private void ProcessBaselandByShapes(IEnumerable<Feature> boundaryFeatures, SQLiteConnection connection) { UpdateStatus("Processing baseland by shapes."); if (!preserveCountryLevelData) return; MultipolygonShape boundaryShape = new MultipolygonShape(); foreach (Feature feature in boundaryFeatures) { boundaryShape.Polygons.Add(feature.GetBoundingBox().ToPolygon()); } Feature boundaryFeature = new Feature(boundaryShape); boundaryFeature = SqlTypesGeometryHelper.MakeValid(boundaryFeature); SqliteFeatureSource baselandFeatureSource = new SqliteFeatureSource(connection.ConnectionString, "osm_baseland_polygon", "id", "geometry"); SqliteFeatureSource baseland1mFeatureSource = new SqliteFeatureSource(connection.ConnectionString, "osm_baseland1m_polygon", "id", "geometry"); baselandFeatureSource.Open(); baseland1mFeatureSource.Open(); UpdateStatus("Querying baseland features from osm_baseland_polygon table."); Collection<Feature> baselandFeatures = baselandFeatureSource.SpatialQuery(boundaryShape, QueryType.Intersects, ReturningColumnsType.NoColumns); UpdateStatus("Querying baseland1m features from osm_baseland1m_polygon table."); Collection<Feature> baseland1mFeatures = baseland1mFeatureSource.GetAllFeatures(ReturningColumnsType.NoColumns); Collection<Feature> newBaselandFeatures = new Collection<Feature>(); foreach (Feature feature in baselandFeatures) { UpdateStatus($"Getting intersection feature for baseland feautre: {feature.Id}."); Feature validFeature = SqlTypesGeometryHelper.MakeValid(feature); newBaselandFeatures.Add(validFeature.GetIntersection(boundaryFeature)); } foreach (Feature feature in baseland1mFeatures) { UpdateStatus($"Getting difference feature for baseland1m feautre: {feature.Id}."); if (feature.IsDisjointed(boundaryFeature)) newBaselandFeatures.Add(feature); else { Feature validFeature = SqlTypesGeometryHelper.MakeValid(feature); Feature difference = SqlTypesGeometryHelper.GetDifference(validFeature, boundaryFeature); if (difference != null) newBaselandFeatures.Add(difference); } } baselandFeatureSource.BeginTransaction(); UpdateStatus("Deleting table osm_baseland_polygon."); baselandFeatureSource.ExecuteNonQuery("DELETE FROM osm_baseland_polygon;"); UpdateStatus("Deleting table idx_osm_baseland_polygon_geometry."); baselandFeatureSource.ExecuteNonQuery("DELETE FROM idx_osm_baseland_polygon_geometry;"); int counter = 0; foreach (Feature f in newBaselandFeatures) { UpdateStatus($"Adding new baseland feature: {f.Id}."); baselandFeatureSource.AddFeature(f); counter++; if (counter > 100000) { UpdateStatus("Commiting sqlite database transaction."); baselandFeatureSource.CommitTransaction(); baselandFeatureSource.BeginTransaction(); counter = 0; } } baselandFeatureSource.CommitTransaction(); } private void DeleteBaseTableDataByViewsIndexes(string baseTable, List<string> views, SQLiteConnection connection, string bboxtable) { // Here we execute a few PRAGMA commands to SQLite which helps speed things up in creating the new target StringBuilder commands = new StringBuilder(); commands.Append("PRAGMA journal_mode=OFF;PRAGMA synchronous=OFF;PRAGMA cache_size=500000;PRAGMA temp_store=2;"); // The next few lines of code covers the cutting of the data via a WHERE clause. commands.AppendFormat("DELETE FROM {0} ", baseTable); for (int i = 0; i < views.Count; i++) { if (i == 0) commands.Append("WHERE "); commands.AppendFormat("id NOT IN (SELECT id FROM idx_{0}_geometry)", views[i]); if (i + 1 < views.Count) commands.Append(" AND "); } commands.Append(";"); // Here we analyze the newly created target table. This helps the query analyzer. commands.AppendFormat("ANALYZE {0};", baseTable); ExecuteNonQueryCommand(commands, connection); } private void DeleteViewIndexesByBoundingBoxIndex(View view, SQLiteConnection connection, string bboxtable) { StringBuilder commands = new StringBuilder(); // Here we execute a few PRAGMA commands to SQLite which helps speed things up in creating the new target commands.Append("PRAGMA journal_mode=OFF;PRAGMA synchronous=OFF;PRAGMA cache_size=500000;PRAGMA temp_store=1;"); commands.Append("CREATE TABLE temp_ids_to_keep (id INTEGER PRIMARY KEY);"); // The next few lines of code covers the cutting of the data via a WHERE clause. /* delete with the temp table */ // This WHERE clause adds in the subselect from the spatial index based on the bounding box passed in. var insertIndexWhereClause = string.Format(@"WHERE id IN (select idx_{0}_geometry.id from idx_{0}_geometry join bbox.idx_{1}_geometry on bbox.idx_{1}_geometry.minx < idx_{0}_geometry.maxx and bbox.idx_{1}_geometry.miny < idx_{0}_geometry.maxy and bbox.idx_{1}_geometry.maxx > idx_{0}_geometry.minx and bbox.idx_{1}_geometry.maxy > idx_{0}_geometry.miny)", view.Name, bboxtable); var deleteIndexWhereClause = @"WHERE id NOT IN (SELECT id FROM temp_ids_to_keep)"; // INSERT ids into temp table commands.AppendFormat("INSERT INTO temp_ids_to_keep SELECT id FROM idx_{0}_geometry {1};", view.Name, insertIndexWhereClause); // Execute the DELETE statement for the spatial index. commands.AppendFormat("DELETE FROM idx_{0}_geometry {1};", view.Name, deleteIndexWhereClause); commands.Append("DROP TABLE temp_ids_to_keep;"); // Here we analyze the newly created target table's index. This helps the query analyzer. commands.AppendFormat("ANALYZE idx_{0}_geometry;", view.Name); ExecuteNonQueryCommand(commands, connection); } private void DeleteTableDataByBoundingBoxIndex(Table table, SQLiteConnection connection, string bboxtable) { StringBuilder commands = new StringBuilder(); // Here we execute a few PRAGMA commands to SQLite which helps speed things up in creating the new target commands.Append("PRAGMA journal_mode=OFF;PRAGMA synchronous=OFF;PRAGMA cache_size=500000;PRAGMA temp_store=2;"); string deleteDataWhereClause = "WHERE 1 = 0"; string deleteIndexWhereClause = "WHERE 1 = 0"; // The next few lines of code covers the cutting of the data via a WHERE clause. If you preserve country level data then there are certain // tables we do not want to cut and some we always want to cut. The ones we always want to cut is anything that starts with osm_ except ones starting with osm_baseland // If you say not to preserve country level data then everything gets cut if (!preserveCountryLevelData || (table.TableName.StartsWith("osm_") && !table.TableName.StartsWith("osm_baseland"))) { // This WHERE clause adds in the subselect from the spatial index based on the bounding box passed in. deleteDataWhereClause = string.Format(@"WHERE {0}.id NOT IN (select idx_{0}_geometry.id from idx_{0}_geometry)", table.TableName, bboxtable); deleteIndexWhereClause = string.Format(@"WHERE id NOT IN (select idx_{0}_geometry.id from idx_{0}_geometry join bbox.idx_{1}_geometry on bbox.idx_{1}_geometry.minx < idx_{0}_geometry.maxx and bbox.idx_{1}_geometry.miny < idx_{0}_geometry.maxy and bbox.idx_{1}_geometry.maxx > idx_{0}_geometry.minx and bbox.idx_{1}_geometry.maxy > idx_{0}_geometry.miny)", table.TableName, bboxtable); } // Execute the DELETE statement for the spatial index. commands.AppendFormat("DELETE FROM idx_{0}_geometry {1};", table.TableName, deleteIndexWhereClause); // Execute the DELETE statement for the data. ta commands.AppendFormat("DELETE FROM {0} {1};", table.TableName, deleteDataWhereClause); // Here we analyze the newly created target table. This helps the query analyzer. commands.AppendFormat("ANALYZE {0};", table.TableName); // Here we analyze the newly created target table's index. This helps the query analyzer. commands.AppendFormat("ANALYZE idx_{0}_geometry;", table.TableName); ExecuteNonQueryCommand(commands, connection); } private Collection<View> GetViews(SQLiteConnection connection) { // Here we query all of the views SQLiteCommand getTablesCommand = new SQLiteCommand(@"SELECT name, sql FROM sqlite_master WHERE (type='view');", connection); SQLiteDataReader getTablesreader = getTablesCommand.ExecuteReader(); Collection<View> views = new Collection<View>(); // Add the views into a collection while (getTablesreader.Read()) { string viewName = getTablesreader.GetValue(0).ToString(); string createStatement = getTablesreader.GetValue(1).ToString(); //Extract the base table from the create statement //Get rid of new lines string baseTable = createStatement.Replace("\n", ""); //Get rid of tabs baseTable = baseTable.Replace("\t", ""); //Get only the text after the "FROM" Statement baseTable = baseTable.Substring(baseTable.LastIndexOf("FROM ") + "FROM ".Length); //Get only the text before tho "WHERE" statement //uses inline if to handle the case of no "WHERE" statement //see osm_address_point for an example of this baseTable = baseTable.Substring(0, baseTable.LastIndexOf("WHERE") > 0 ? baseTable.LastIndexOf("WHERE") : baseTable.Length); //Get rid of any leading or trailing spaces that might exist baseTable = baseTable.Trim(); View view = new View(viewName, baseTable); views.Add(view); } return views; } private Collection<Table> GetTables(SQLiteConnection connection) { // Here we query all of the tables which are not master tables or the indexes SQLiteCommand getTablesCommand = new SQLiteCommand(@"SELECT name FROM sqlite_master WHERE (type='table') AND name NOT LIKE 'sqlite_%' and name NOT LIKE 'idx_%' and name NOT LIKE 'planet_%'", connection); SQLiteDataReader getTablesreader = getTablesCommand.ExecuteReader(); Collection<Table> tables = new Collection<Table>(); // Add the tables into a collection of table objects while (getTablesreader.Read()) { Table table = new Table(); table.TableName = getTablesreader.GetValue(0).ToString(); table.Columns = new Collection<Column>(); tables.Add(table); } // Cleanup the table reader and command getTablesreader.Close(); getTablesreader.Dispose(); // For each table we now get and load the column names and data types foreach (Table table in tables) { // Get the column info for the table SQLiteCommand getTableSchemasCommand = new SQLiteCommand(string.Format("Pragma table_info({0});", table.TableName), connection); SQLiteDataReader getSchemaReader = getTableSchemasCommand.ExecuteReader(); while (getSchemaReader.Read()) { table.Columns.Add(new Column() { ColumnName = getSchemaReader.GetValue(1).ToString(), DataType = getSchemaReader.GetValue(2).ToString() }); } // Cleanup the column command and reader getSchemaReader.Close(); getSchemaReader.Dispose(); } return tables; } private void ExecuteNonQueryCommand(StringBuilder commandText, SQLiteConnection connection) { ExecuteNonQueryCommand(commandText.ToString(), connection); } private void ExecuteNonQueryCommand(string commandText, SQLiteConnection connection) { SQLiteCommand command = new SQLiteCommand(commandText, connection); command.ExecuteNonQuery(); command.Dispose(); } } }
namespace WorldMapKitDataExtractor { public class Column { public string ColumnName { get; set; } public string DataType { get; set; } } }
using System.Collections.ObjectModel; namespace WorldMapKitDataExtractor { public class Table { public string TableName { get; set; } public Collection<Column> Columns { get; set; } } }
namespace WorldMapKitDataExtractor { public class View { public View(string name, string baseTable) { this.Name = name; this.BaseTable = baseTable; } public string Name { get; set; } public string BaseTable { get; set; } } }
using System.Reflection; using System.Resources; using System.Runtime.CompilerServices; using System.Runtime.InteropServices; using System.Windows; // General Information about an assembly is controlled through the following // set of attributes. Change these attribute values to modify the information // associated with an assembly. [assembly: AssemblyTitle("WorldMapKitDataExtractor")] [assembly: AssemblyDescription("")] [assembly: AssemblyConfiguration("")] [assembly: AssemblyCompany("")] [assembly: AssemblyProduct("WorldMapKitDataExtractor")] [assembly: AssemblyCopyright("Copyright © 2016")] [assembly: AssemblyTrademark("")] [assembly: AssemblyCulture("")] // Setting ComVisible to false makes the types in this assembly not visible // to COM components. If you need to access a type in this assembly from // COM, set the ComVisible attribute to true on that type. [assembly: ComVisible(false)] //In order to begin building localizable applications, set //<UICulture>CultureYouAreCodingWith</UICulture> in your .csproj file //inside a <PropertyGroup>. For example, if you are using US english //in your source files, set the <UICulture> to en-US. Then uncomment //the NeutralResourceLanguage attribute below. Update the "en-US" in //the line below to match the UICulture setting in the project file. //[assembly: NeutralResourcesLanguage("en-US", UltimateResourceFallbackLocation.Satellite)] [assembly: ThemeInfo( ResourceDictionaryLocation.None, //where theme specific resource dictionaries are located //(used if a resource is not found in the page, // or application resource dictionaries) ResourceDictionaryLocation.SourceAssembly //where the generic resource dictionary is located //(used if a resource is not found in the page, // app, or any theme specific resource dictionaries) )] // Version information for an assembly consists of the following four values: // // Major Version // Minor Version // Build Number // Revision // // You can specify all the values or you can default the Build and Revision Numbers // by using the '*' as shown below: // [assembly: AssemblyVersion("1.0.*")] [assembly: AssemblyVersion("1.0.0.0")] [assembly: AssemblyFileVersion("1.0.0.0")]
//------------------------------------------------------------------------------ // <auto-generated> // This code was generated by a tool. // Runtime Version:4.0.30319.42000 // // Changes to this file may cause incorrect behavior and will be lost if // the code is regenerated. // </auto-generated> //------------------------------------------------------------------------------ namespace WorldMapKitDataExtractor.Properties { using System; /// <summary> /// A strongly-typed resource class, for looking up localized strings, etc. /// </summary> // This class was auto-generated by the StronglyTypedResourceBuilder // class via a tool like ResGen or Visual Studio. // To add or remove a member, edit your .ResX file then rerun ResGen // with the /str option, or rebuild your VS project. [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "4.0.0.0")] [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] internal class Resources { private static global::System.Resources.ResourceManager resourceMan; private static global::System.Globalization.CultureInfo resourceCulture; [global::System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("Microsoft.Performance", "CA1811:AvoidUncalledPrivateCode")] internal Resources() { } /// <summary> /// Returns the cached ResourceManager instance used by this class. /// </summary> [global::System.ComponentModel.EditorBrowsableAttribute(global::System.ComponentModel.EditorBrowsableState.Advanced)] internal static global::System.Resources.ResourceManager ResourceManager { get { if (object.ReferenceEquals(resourceMan, null)) { global::System.Resources.ResourceManager temp = new global::System.Resources.ResourceManager("WorldMapKitDataExtractor.Properties.Resources", typeof(Resources).Assembly); resourceMan = temp; } return resourceMan; } } /// <summary> /// Overrides the current thread's CurrentUICulture property for all /// resource lookups using this strongly typed resource class. /// </summary> [global::System.ComponentModel.EditorBrowsableAttribute(global::System.ComponentModel.EditorBrowsableState.Advanced)] internal static global::System.Globalization.CultureInfo Culture { get { return resourceCulture; } set { resourceCulture = value; } } } }
//------------------------------------------------------------------------------ // <auto-generated> // This code was generated by a tool. // Runtime Version:4.0.30319.42000 // // Changes to this file may cause incorrect behavior and will be lost if // the code is regenerated. // </auto-generated> //------------------------------------------------------------------------------ namespace WorldMapKitDataExtractor.Properties { [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "14.0.0.0")] internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase { private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings()))); public static Settings Default { get { return defaultInstance; } } } }