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;
            }
        }
    }
}