Differences

This shows you the differences between two versions of the page.

--- source_code_osmworldmapkitextractor.zip [2016/04/11 01:36]
tgwikiupdate
+++ source_code_osmworldmapkitextractor.zip [2017/02/23 21:55]
ryanduan Updated Extractor code
@@ Line 35: / Line 35: @@
 using System.Windows.Forms;
 using ThinkGeo.MapSuite.Core;
+using WorldMapKitDataExtractor.Model;
 namespace WorldMapKitDataExtractor
@@ Line 56: / Line 57: @@
                 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>();
+                Collection<BoundingBoxWithZoomLevels> boundariesFeatures = new Collection<BoundingBoxWithZoomLevels>();
+                if(extractor.PreserveCountryLevelData)
+                {
+                    RectangleShape boundingBox = new RectangleShape();
+                    switch(txtInputDatabaseSrid.Text)
+                    {
+                        case "4326":
+                            boundingBox = new RectangleShape(-180, 90, 180, -90);
+                            break;
+                        case "3857":
+                            boundingBox = new RectangleShape(-20026376, 20048966, 20026376, -20048966);
+                            break;
+                        default:
+                            boundingBox = new RectangleShape(0, 0, 0, 0);
+                            break;
+                    }
+                    boundariesFeatures.Add(new BoundingBoxWithZoomLevels(boundingBox, (int)cmbCountryStartZoomLevel.SelectedValue, (int)cmbCountryEndZoomLevel.SelectedValue));
+                }
                 if (rbtnShapeFile.IsChecked == true)
@@ Line 73: / Line 92: @@
                     foreach (DataRowView row in selectItems)
                     {
-                        boundariesFeatures.Add(shapeFileLayer.QueryTools.GetFeatureById(row.Row[0].ToString(), ReturningColumnsType.NoColumns));
+                        boundariesFeatures.Add(new BoundingBoxWithZoomLevels(shapeFileLayer.QueryTools.GetFeatureById(row.Row[0].ToString(), ReturningColumnsType.NoColumns).GetBoundingBox(), 1, 20));
                     }
                 }
                 else
                 {
-                    var uppperLeftPoint = txtUpperLeftPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                    BoundingBoxRow row1 = new BoundingBoxRow(txtUpperLeft1, txtLowerRight1, cmbStartZoomLevel1, cmbEndZoomLevel1, chkEnable1);
-                    var lowerRightPoint = txtLowerRightPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                    BoundingBoxRow row2 = new BoundingBoxRow(txtUpperLeft2, txtLowerRight2, cmbStartZoomLevel2, cmbEndZoomLevel2, chkEnable2);
+                    BoundingBoxRow row3 = new BoundingBoxRow(txtUpperLeft3, txtLowerRight3, cmbStartZoomLevel3, cmbEndZoomLevel3, chkEnable3);
+                    BoundingBoxRow row4 = new BoundingBoxRow(txtUpperLeft4, txtLowerRight4, cmbStartZoomLevel4, cmbEndZoomLevel4, chkEnable4);
+                    BoundingBoxRow row5 = new BoundingBoxRow(txtUpperLeft5, txtLowerRight5, cmbStartZoomLevel5, cmbEndZoomLevel5, chkEnable5);
+                    BoundingBoxRow[] boundingBoxRows = { row1, row2, row3, row4, row5 };
-                    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));
+                    foreach (BoundingBoxRow row in boundingBoxRows)
+                    {
+                        if ((bool)row.chkRowEnabled.IsChecked)
+                        {
+                            var uppperLeftPoint = row.txtUpperLeftPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                            var lowerRightPoint = row.txtLowerRightPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                            RectangleShape boundingBox = new RectangleShape(uppperLeftPoint[0], uppperLeftPoint[1], lowerRightPoint[0], lowerRightPoint[1]);
+                            boundariesFeatures.Add(new BoundingBoxWithZoomLevels(boundingBox, (int)row.cmbStartZoomLevel.SelectedValue, (int)row.cmbEndZoomLevel.SelectedValue));
+                        }
+                    }
                 }
@@ Line 97: / Line 131: @@
             int srid;
             if (!File.Exists(txtInputDatabase.Text))
-                errorMessage.AppendLine("Please enter currect input database file path.");
+                errorMessage.AppendLine("Please enter correct input database file path.");
             if (!int.TryParse(txtInputDatabaseSrid.Text, out srid))
-                errorMessage.AppendLine("Please enter currect SRID for input database.");
+                errorMessage.AppendLine("Please enter correct SRID for input database.");
             if (string.IsNullOrEmpty(txtOutputDatabase.Text))
-                errorMessage.AppendLine("Please enter currect output database file path.");
+                errorMessage.AppendLine("Please enter correct output database file path.");
             if (rbtnShapeFile.IsChecked == true)
             {
                 if (!File.Exists(txtShapeFile.Text))
-                    errorMessage.AppendLine("Please enter currect shape file path.");
+                    errorMessage.AppendLine("Please enter correct 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.");
+                    errorMessage.AppendLine("Please enter correct SRID for boundary shaplefile.");
             }
-            if (rbtnBoundingBox.IsChecked == true && !int.TryParse(boundarySrid.Text, out srid))
+            if (rbtnBoundingBox.IsChecked == true)
-                errorMessage.AppendLine("Please enter currect SRID for boundary.");
+            {
+                if(!int.TryParse(boundarySrid.Text, out srid))
+                    errorMessage.AppendLine("Please enter correct SRID for boundary.");
+                if((bool)chkPreserveCountryLevelData.IsChecked)
+                {
+                    if((int)cmbCountryStartZoomLevel.SelectedValue > (int)cmbCountryEndZoomLevel.SelectedValue)
+                        errorMessage.AppendLine("Invalid ZoomLevel Input for Preserve Country-Level Data.");
+                }
+                BoundingBoxRow row1 = new BoundingBoxRow(txtUpperLeft1, txtLowerRight1, cmbStartZoomLevel1, cmbEndZoomLevel1, chkEnable1);
+                BoundingBoxRow row2 = new BoundingBoxRow(txtUpperLeft2, txtLowerRight2, cmbStartZoomLevel2, cmbEndZoomLevel2, chkEnable2);
+                BoundingBoxRow row3 = new BoundingBoxRow(txtUpperLeft3, txtLowerRight3, cmbStartZoomLevel3, cmbEndZoomLevel3, chkEnable3);
+                BoundingBoxRow row4 = new BoundingBoxRow(txtUpperLeft4, txtLowerRight4, cmbStartZoomLevel4, cmbEndZoomLevel4, chkEnable4);
+                BoundingBoxRow row5 = new BoundingBoxRow(txtUpperLeft5, txtLowerRight5, cmbStartZoomLevel5, cmbEndZoomLevel5, chkEnable5);
+                BoundingBoxRow[] boundingBoxRows = { row1, row2, row3, row4, row5 };
+                foreach (BoundingBoxRow row in boundingBoxRows)
+                {
+                    if ((bool)row.chkRowEnabled.IsChecked)
+                    {
+                        var upperLeftPoint = row.txtUpperLeftPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                        var lowerRightPoint = row.txtLowerRightPoint.Text.Split(',').Select(a => double.Parse(a.Trim())).ToArray();
+                        if(upperLeftPoint[0] > lowerRightPoint[0] || upperLeftPoint[1] < lowerRightPoint[1])
+                            errorMessage.AppendLine("Invalid BoundingBox Input.");
+                        if ((int)row.cmbStartZoomLevel.SelectedValue > (int)row.cmbEndZoomLevel.SelectedValue || (int)row.cmbStartZoomLevel.SelectedValue < 0 || (int)row.cmbEndZoomLevel.SelectedValue < 0)
+                            errorMessage.AppendLine("Invalid ZoomLevel Input.");
+                    }
+                }
+                if(!((bool)chkEnable1.IsChecked || (bool)chkEnable2.IsChecked || (bool)chkEnable3.IsChecked || (bool)chkEnable4.IsChecked || (bool)chkEnable5.IsChecked))
+                    errorMessage.AppendLine("Please enable a bounding box.");
+            }
             if (!string.IsNullOrEmpty(errorMessage.ToString()))
@@ Line 220: / Line 291: @@
         {
             Close();
+        }
+        private void Window_Loaded(object sender, RoutedEventArgs e)
+        {
+            System.Windows.Controls.ComboBox[] zoomLevelBoxes = { cmbEndZoomLevel1, cmbEndZoomLevel2, cmbEndZoomLevel3, cmbEndZoomLevel4, cmbEndZoomLevel5,
+                                                                  cmbStartZoomLevel1, cmbStartZoomLevel2, cmbStartZoomLevel3, cmbStartZoomLevel4, cmbStartZoomLevel5,
+                                                                  cmbCountryStartZoomLevel, cmbCountryEndZoomLevel };
+            foreach (System.Windows.Controls.ComboBox comboBox in zoomLevelBoxes)
+            {
+                int[] zoomLevels = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 };
+                comboBox.ItemsSource = zoomLevels;
+            }
+            cmbStartZoomLevel1.SelectedIndex = 7;
+            cmbEndZoomLevel1.SelectedIndex = 19;
+            cmbCountryStartZoomLevel.SelectedIndex = 0;
+            cmbCountryEndZoomLevel.SelectedIndex = 6;
         }
     }
 }
 </code>
@@ Line 343: / Line 433: @@
 ====Extractor.cs====
 <code csharp>
-using OsmWorldMapKitExtractor;
 using System;
 using System.Collections.Generic;
 using System.Collections.ObjectModel;
 using System.Data.SQLite;
-using System.IO;
-using System.Linq;
 using System.Text;
+using ThinkGeo.MapSuite.Core;
+using System.Linq;
+using WorldMapKitDataExtractor.Model;
-namespace ThinkGeo.MapSuite.Core
+namespace WorldMapKitDataExtractor
 {
     public class Extractor
@@ Line 403: / Line 493: @@
         }
-        public void ExtractDataByShape(IEnumerable<Feature> boundaryFeatures)
+        public void ExtractDataByShape(Collection<BoundingBoxWithZoomLevels> boundaryFeatureSet)
         {
-            UpdateStatus("Begain to extract world map kit data by shape file.");
+            UpdateStatus("Beginning 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;");
+            Collection<BoundingBoxWithZoomLevels> projectedBoundaryFeaturesWithZoomLevels = new Collection<BoundingBoxWithZoomLevels>();
-            connection.Open();
+            Collection<RectangleShape> projectedBoundingBoxes = new Collection<RectangleShape>();
-            var projectedBoundaryFeatures = ProjectedBoundaryFeatures(boundaryFeatures);
+            foreach (BoundingBoxWithZoomLevels boundaryFeatureWithZoomLevels in boundaryFeatureSet)
-            DeleteDataByBoundary(projectedBoundaryFeatures, connection);
+            {
-            ProcessBaselandByShapes(projectedBoundaryFeatures, connection);
+                RectangleShape projectedBoundaryFeature = ProjectBoundingBox(boundaryFeatureWithZoomLevels.BoundingBox);
+                projectedBoundaryFeaturesWithZoomLevels.Add(new BoundingBoxWithZoomLevels(projectedBoundaryFeature, boundaryFeatureWithZoomLevels.StartingZoomLevel, boundaryFeatureWithZoomLevels.EndingZoomLevel));
+                projectedBoundingBoxes.Add(projectedBoundaryFeature);
+            }
-            UpdateStatus("Excuting Vacuum for output database.");
+            SQLiteConnection.CreateFile(outputDataPath);
-            ExecuteNonQueryCommand("VACUUM;", connection);
+            SQLiteConnection targetDatabaseConnection = new SQLiteConnection($"Data Source={outputDataPath};Version=3;");
+            targetDatabaseConnection.Open();
+            SQLiteConnection sourceDatabaseConnection = new SQLiteConnection($"Data Source={inputDataPath};Version=3;");
+            sourceDatabaseConnection.Open();
-            connection.Close();
+            //this is the set of layers we use to determine which tables to grab for each bounding area
-            connection.Dispose();
+            OsmWorldMapKitLayer wmkLayer = new OsmWorldMapKitLayer(sourceDatabaseConnection.ConnectionString, OsmWorldMapKitDatabaseType.Sqlite);
-            UpdateStatus("Done for extracting world map kit data!");
+            wmkLayer.Open();
-        }
-        private IEnumerable<Feature> ProjectedBoundaryFeatures(IEnumerable<Feature> boundaryFeatures)
+            CopyDataToABuffer(GetBoundingBoxesForLayers(projectedBoundaryFeaturesWithZoomLevels, sourceDatabaseConnection, wmkLayer.Layers), sourceDatabaseConnection);
-        {
+            ProcessBaselandByShapes(projectedBoundingBoxes, targetDatabaseConnection);
-            Proj4Projection proj = new Proj4Projection(boundaryPrj, inputDataPrj);
-            proj.Open();
+            UpdateStatus("Executing vacuum for output database.");
-            foreach (Feature feature in boundaryFeatures)
+            ExecuteNonQueryCommand("VACUUM;", targetDatabaseConnection);
-            {
-                yield return proj.ConvertToExternalProjection(feature);
+            targetDatabaseConnection.Close();
-            }
+            targetDatabaseConnection.Dispose();
-            proj.Close();
+            UpdateStatus("Done extracting world map kit data!");
         }
-        private string CreateBoundaryIndexPathFile(string indexTable, IEnumerable<Feature> boundaryFeatures)
+        private void CopyDataToABuffer(Dictionary<FeatureLayer, Collection<RectangleShape>> layersWithBoundingShapes, SQLiteConnection sourceDatabaseConnection)
         {
-            UpdateStatus("Creating temp bounding box index file.");
+            Proj4Projection proj4 = new Proj4Projection(inputDataPrj, inputDataPrj);
-            //Create the temp Bounding Box Index Database
+            GeographyUnit geographyUnit = proj4.GetInternalGeographyUnit();
-            string bboxIndexPathFileName = $@"{Path.GetDirectoryName(inputDataPath)}\boundary_index.sqlite.temp";
-            string bboxIndexConnectionString = $@"Data Source={bboxIndexPathFileName};Version=3;";
-            Collection<string> tables = SqliteFeatureSource.GetTableNames(bboxIndexConnectionString);
+            //OSM WorldMapKit contains several views and view indices that we want to grab as well for our OSMLayer, so we create temporary tables of the data from these within the boundingbox
-            if (!tables.Contains(indexTable))
+            var views = GetViews(sourceDatabaseConnection);
-                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");
+            foreach (View view in views)
-            bboxIndexFeatureSource.Open();
-            bboxIndexFeatureSource.BeginTransaction();
-            foreach (Feature boundary in boundaryFeatures)
             {
-                UpdateStatus($"Getting grid cells by boundary feature: {boundary.Id}.");
+                if (layersWithBoundingShapes.Keys.Where(featureLayer => featureLayer.Name == view.Name).Count() > 0)
-                GeoCollection<Feature> cells = DynamicGridPolygonIndexer.GetIntersectingGridCells(boundary.GetShape(), GeographyUnit.DecimalDegree, 10, AreaUnit.SquareMiles);
-                foreach (Feature cell in cells)
                 {
-                    bboxIndexFeatureSource.AddFeature(cell);
+                    Collection<RectangleShape> boundingBoxes = layersWithBoundingShapes[layersWithBoundingShapes.Keys.Where(featureLayer => featureLayer.Name == view.Name).ElementAt(0)];
+                    CreateTempTableForView(view, boundingBoxes, sourceDatabaseConnection);
+                }
+                else
+                {
+                    //create the table so the database is valid
+                    CreateTempTableForView(view, new Collection<RectangleShape>() { new RectangleShape(0, 0, 0, 0) }, sourceDatabaseConnection);
                 }
             }
-            bboxIndexFeatureSource.CommitTransaction();
+            //get the features from every table within the bounding box and add them to the temporary output file
-            return bboxIndexPathFileName;
+            var tables = GetTables(sourceDatabaseConnection);
-        }
+            foreach (var table in tables)
+            {
+                UpdateStatus($"Copying {table.TableName} table to output database.");
+                SqliteFeatureSource source = new SqliteFeatureSource($@"Data Source={inputDataPath};Version=3;", table.TableName);
+                source.Open();
-        private void DeleteDataByBoundary(IEnumerable<Feature> boundaryFeatures, SQLiteConnection connection)
+                Collection<Feature> intersectFeatures = new Collection<Feature>();
-        {
-            UpdateStatus("Deleting data by bounding box index.");
-            string bboxtable = "bboxIndex";
+                if (layersWithBoundingShapes.Keys.Where(featureLayer => featureLayer.Name == table.TableName).Count() > 0)
-            string boundaryIndexPathFile = CreateBoundaryIndexPathFile(bboxtable, boundaryFeatures);
+                {
+                    intersectFeatures = GetFeaturesIntersects(source, layersWithBoundingShapes[layersWithBoundingShapes.Keys.Where(featureLayer => featureLayer.Name == table.TableName).ElementAt(0)], ReturningColumnsType.AllColumns);
+                }
-            //Attach to a bbox index database on which to extract data
+                Collection<string> targetTables = SqliteFeatureSource.GetTableNames($@"Data Source={outputDataPath};Version=3;");
-            ExecuteNonQueryCommand($"ATTACH DATABASE '{boundaryIndexPathFile}' as bbox;", connection);
-            Collection<Table> tables = GetTables(connection);
+                if (!targetTables.Contains(table.TableName))
-            foreach (var table in tables)
+                    SqliteFeatureSource.CreateTable($@"Data Source={outputDataPath};Version=3;", table.TableName, table.Columns, geographyUnit);
-            {
-                UpdateStatus($"Deleting table data by bounding box index {table.TableName}.");
-                DeleteTableDataByBoundingBoxIndex(table, connection, bboxtable);
-            }
-            Collection<View> views = GetViews(connection);
+                SqliteFeatureSource tempSource = new SqliteFeatureSource($@"Data Source={outputDataPath};Version=3;", table.TableName);
-            foreach (var view in views)
+                tempSource.Open();
-            {
+                tempSource.BeginTransaction();
-                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.");
+                int counter = 0;
-            ExecuteNonQueryCommand("DETACH DATABASE 'bbox'; PRAGMA journal_mode=OFF;", connection);
+                UpdateStatus($"Adding <1000 new features.");
-            File.Delete(boundaryIndexPathFile);
+                foreach (var feature in intersectFeatures.Distinct(new FeatureComparer()))
+                {
+                    tempSource.AddFeature(feature);
+                    counter++;
+                    if (counter % 1000 == 0)
+                    {
+                        UpdateStatus($"Adding {counter} new features.");
+                    }
+                }
+                UpdateStatus("Commiting sqlite database transaction.");
+                tempSource.CommitTransaction();
+                tempSource.Close();
+            }
         }
-        private void ProcessBaselandByShapes(IEnumerable<Feature> boundaryFeatures, SQLiteConnection connection)
+        private void ProcessBaselandByShapes(Collection<RectangleShape> boundingBoxes, SQLiteConnection connection)
         {
             UpdateStatus("Processing baseland by shapes.");
-            if (!preserveCountryLevelData) return;
+            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);
+            //remove full world bounding box from boundary features
+            boundingBoxes.RemoveAt(0);
             SqliteFeatureSource baselandFeatureSource = new SqliteFeatureSource(connection.ConnectionString, "osm_baseland_polygon", "id", "geometry");
@@ Line 524: / Line 609: @@
             UpdateStatus("Querying baseland features from osm_baseland_polygon table.");
-            Collection<Feature> baselandFeatures = baselandFeatureSource.SpatialQuery(boundaryShape, QueryType.Intersects, ReturningColumnsType.NoColumns);
+            List<Feature> baselandFeatures = new List<Feature>();
+            foreach (var boundingBox in boundingBoxes)
+            {
+                Collection<Feature> features = baselandFeatureSource.SpatialQuery(boundingBox, QueryType.Intersects, ReturningColumnsType.NoColumns);
+                baselandFeatures.AddRange(features);
+            }
             UpdateStatus("Querying baseland1m features from osm_baseland1m_polygon table.");
             Collection<Feature> baseland1mFeatures = baseland1mFeatureSource.GetAllFeatures(ReturningColumnsType.NoColumns);
+            if (baselandFeatures.Count == 0 || baseland1mFeatures.Count == 0)
+            {
+                UpdateStatus("Cannot process baseland by shapes - one or more tables is empty");
+                return;
+            }
             Collection<Feature> newBaselandFeatures = new Collection<Feature>();
+            int counter = 0;
+            UpdateStatus($"Getting <1000 intersection features for baseland 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));
+                Feature intersectionFeature = GetInterSection(validFeature, boundingBoxes);
+                if (!(intersectionFeature == null))
+                {
+                    newBaselandFeatures.Add(intersectionFeature);
+                    counter++;
+                    if (counter % 1000 == 0)
+                    {
+                        UpdateStatus($"Getting {counter} intersection features for baseland feature.");
+                    }
+                }
             }
+            counter = 0;
+            UpdateStatus($"Getting <1000 difference features for baseland1m feature.");
             foreach (Feature feature in baseland1mFeatures)
             {
-                UpdateStatus($"Getting difference feature for baseland1m feautre: {feature.Id}.");
+                if (IsDisjointed(feature, boundingBoxes))
-                if (feature.IsDisjointed(boundaryFeature)) newBaselandFeatures.Add(feature);
+                {
+                    newBaselandFeatures.Add(feature);
+                    counter++;
+                }
                 else
                 {
                     Feature validFeature = SqlTypesGeometryHelper.MakeValid(feature);
-                    Feature difference = SqlTypesGeometryHelper.GetDifference(validFeature, boundaryFeature);
+                    Feature difference = GetDifference(validFeature, boundingBoxes);
-                    if (difference != null) newBaselandFeatures.Add(difference);
+                    if (difference != null)
+                    {
+                        newBaselandFeatures.Add(difference);
+                        counter++;
+                    }
+                }
+                if (counter != 0 && counter % 1000 == 0)
+                {
+                    UpdateStatus($"Getting {counter} difference features for baseland1m feature.");
                 }
             }
@@ Line 558: / Line 676: @@
             baselandFeatureSource.ExecuteNonQuery("DELETE FROM idx_osm_baseland_polygon_geometry;");
-            int counter = 0;
+            counter = 0;
+            UpdateStatus($"Adding <1000 new features.");
             foreach (Feature f in newBaselandFeatures)
             {
-                UpdateStatus($"Adding new baseland feature: {f.Id}.");
                 Feature feature = new Feature(f.GetWellKnownBinary());
                 baselandFeatureSource.AddFeature(feature);
                 counter++;
-                if (counter > 100000)
+                if (counter % 1000 == 0)
                 {
-                    UpdateStatus("Commiting sqlite database transaction.");
+                    UpdateStatus($"Adding {counter} new features.");
-                    baselandFeatureSource.CommitTransaction();
-                    baselandFeatureSource.BeginTransaction();
-                    counter = 0;
                 }
             }
+            UpdateStatus("Commiting sqlite database transaction.");
             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);
         }
@@ Line 682: / Line 719: @@
                 //Get rid of any leading or trailing spaces that might exist
                 baseTable = baseTable.Trim();
-                View view = new View(viewName, baseTable);
+                View view = new View(viewName, baseTable, createStatement);
                 views.Add(view);
@@ Line 692: / Line 729: @@
         {
             // 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);
+            SQLiteCommand getTablesCommand = new SQLiteCommand(@"SELECT sqlite_master.name FROM sqlite_master LEFT OUTER JOIN sqlite_temp_master ON sqlite_master.name = sqlite_temp_master.name WHERE (sqlite_master.type = 'table' OR sqlite_temp_master.type = 'table') AND sqlite_master.name NOT LIKE 'sqlite_%' and sqlite_master.name NOT LIKE 'idx_%' and sqlite_master.name NOT LIKE 'planet_%'", connection);
             SQLiteDataReader getTablesreader = getTablesCommand.ExecuteReader();
@@ Line 702: / Line 739: @@
                 Table table = new Table();
                 table.TableName = getTablesreader.GetValue(0).ToString();
-                table.Columns = new Collection<Column>();
+                table.Columns = new Collection<SqliteColumn>();
                 tables.Add(table);
             }
@@ Line 719: / Line 756: @@
                 while (getSchemaReader.Read())
                 {
-                    table.Columns.Add(new Column() { ColumnName = getSchemaReader.GetValue(1).ToString(), DataType = getSchemaReader.GetValue(2).ToString() });
+                    string columnName = getSchemaReader.GetValue(1).ToString();
+                    string columnType = getSchemaReader.GetValue(2).ToString();
+                    if (!(columnName.Equals("geometry") || columnName.Equals("id")))
+                    {
+                        switch (columnType)
+                        {
+                            case "character varying":
+                                table.Columns.Add(new SqliteColumn() { ColumnName = columnName, ColumnType = SqliteColumnType.Text });
+                                break;
+                            case "bigint":
+                            case "INT":
+                                table.Columns.Add(new SqliteColumn() { ColumnName = columnName, ColumnType = SqliteColumnType.Integer });
+                                break;
+                            case "double precision":
+                                table.Columns.Add(new SqliteColumn() { ColumnName = columnName, ColumnType = SqliteColumnType.Real });
+                                break;
+                            case "numeric":
+                                table.Columns.Add(new SqliteColumn() { ColumnName = columnName, ColumnType = SqliteColumnType.Text });
+                                break;
+                            default:
+                                table.Columns.Add(new SqliteColumn() { ColumnName = columnName, ColumnType = (SqliteColumnType)Enum.Parse(typeof(SqliteColumnType), columnType, true) });
+                                break;
+                        }
+                    }
                 }
@@ Line 728: / Line 789: @@
             return tables;
+        }
+        private void CreateTempTableForView(View view, Collection<RectangleShape> boundingBoxes, SQLiteConnection sourceDatabaseConnection)
+        {
+            UpdateStatus($"Creating temporary table for {view.Name}");
+            StringBuilder createNewTempTableFromViewCommand = new StringBuilder();
+            createNewTempTableFromViewCommand.AppendFormat("CREATE TEMP TABLE {0} AS ", view.Name);
+            createNewTempTableFromViewCommand.AppendFormat("SELECT * FROM {0} WHERE id in (", view.Name);
+            createNewTempTableFromViewCommand.AppendFormat("SELECT id FROM {0} WHERE id in (", view.BaseTable);
+            createNewTempTableFromViewCommand.AppendFormat("SELECT id from idx_{0}_geometry WHERE (maxx < {1} AND maxy < {2} AND minx > {3} AND miny > {4})", view.Name, boundingBoxes[0].UpperRightPoint.X, boundingBoxes[0].UpperRightPoint.Y, boundingBoxes[0].LowerLeftPoint.X, boundingBoxes[0].LowerLeftPoint.Y);
+            for (int i = 1; i < boundingBoxes.Count; i++)
+            {
+                createNewTempTableFromViewCommand.AppendFormat(" OR (maxx < {0} AND maxy < {1} AND minx > {2} AND miny > {3})", boundingBoxes[i].UpperRightPoint.X, boundingBoxes[i].UpperRightPoint.Y, boundingBoxes[i].LowerLeftPoint.X, boundingBoxes[i].LowerLeftPoint.Y);
+            }
+            createNewTempTableFromViewCommand.AppendFormat("));");
+            ExecuteNonQueryCommand(createNewTempTableFromViewCommand.ToString(), sourceDatabaseConnection);
         }
@@ Line 740: / Line 821: @@
             command.ExecuteNonQuery();
             command.Dispose();
+        }
+        private RectangleShape ProjectBoundingBox(RectangleShape boundingBox)
+        {
+            Proj4Projection proj = new Proj4Projection(boundaryPrj, inputDataPrj);
+            proj.Open();
+            RectangleShape projectedBoundaryFeature = proj.ConvertToExternalProjection(boundingBox);
+            proj.Close();
+            return projectedBoundaryFeature;
+        }
+        private Collection<Feature> GetFeaturesIntersects(FeatureSource source, Collection<RectangleShape> boundingBoxes, ReturningColumnsType returningColumnsType)
+        {
+            List<Feature> results = new List<Feature>();
+            foreach (var boundingBox in boundingBoxes)
+            {
+                Collection<Feature> allPossibleFeatures = source.GetFeaturesInsideBoundingBox(boundingBox, returningColumnsType);
+                Collection<Feature> returnFeatures = new Collection<Feature>();
+                foreach (Feature feature in allPossibleFeatures)
+                {
+                    ShapeValidationResult validationResult = feature.GetShape().Validate(ShapeValidationMode.Simple);
+                    if (validationResult.IsValid && feature.GetShape().Intersects(boundingBox))
+                    {
+                        returnFeatures.Add(feature);
+                    }
+                }
+                results.AddRange(returnFeatures);
+            }
+            return new Collection<Feature>(results);
+        }
+        private Dictionary<FeatureLayer, Collection<RectangleShape>> GetBoundingBoxesForLayers(Collection<BoundingBoxWithZoomLevels> boundaryFeatures, SQLiteConnection sourceDatabaseConnection, Collection<FeatureLayer> featureLayers)
+        {
+            Dictionary<FeatureLayer, Collection<RectangleShape>> layersWithBoundingBoxes = new Dictionary<FeatureLayer, Collection<RectangleShape>>();
+            foreach (BoundingBoxWithZoomLevels boundingBoxParameters in boundaryFeatures)
+            {
+                Collection<FeatureLayer> layersForBoundingBoxes = GetAllFeatureLayers(featureLayers, boundingBoxParameters.StartingZoomLevel, boundingBoxParameters.EndingZoomLevel);
+                foreach (FeatureLayer featureLayer in layersForBoundingBoxes)
+                {
+                    if (!layersWithBoundingBoxes.Keys.Contains(featureLayer))
+                    {
+                        var boxes = new Collection<RectangleShape>();
+                        boxes.Add(boundingBoxParameters.BoundingBox);
+                        layersWithBoundingBoxes.Add(featureLayer, boxes);
+                    }
+                    else
+                    {
+                        layersWithBoundingBoxes[featureLayer].Add(boundingBoxParameters.BoundingBox);
+                    }
+                }
+            }
+            return layersWithBoundingBoxes;
+        }
+        private Collection<FeatureLayer> GetAllFeatureLayers(Collection<FeatureLayer> featureLayers, int fromZoomLevelNumber, int toZoomLevelNumber)
+        {
+            Collection<FeatureLayer> featureLayersForZoomLevels = new Collection<FeatureLayer>();
+            Dictionary<int, Collection<FeatureLayer>> allZoomLevelFeatureLayers = GetAllFeatureLayersCore(featureLayers);
+            for (int i = fromZoomLevelNumber; i <= toZoomLevelNumber; i++)
+            {
+                foreach (FeatureLayer featureLayer in allZoomLevelFeatureLayers[i])
+                {
+                    if (!featureLayersForZoomLevels.Contains(featureLayer))
+                    {
+                        featureLayersForZoomLevels.Add(featureLayer);
+                    }
+                }
+            }
+            return featureLayersForZoomLevels;
+        }
+        private Dictionary<int, Collection<FeatureLayer>> GetAllFeatureLayersCore(Collection<FeatureLayer> featureLayers)
+        {
+            Dictionary<int, Collection<FeatureLayer>> featureLayersInAllZoomLevels = new Dictionary<int, Collection<FeatureLayer>>();
+            for (int i = 1; i <= 20; i++)
+            {
+                featureLayersInAllZoomLevels.Add(i, new Collection<FeatureLayer>());
+            }
+            foreach (FeatureLayer layer in featureLayers)
+            {
+                Collection<ZoomLevel> layerZoomLevels = layer.ZoomLevelSet.GetZoomLevels();
+                for (int i = 1; i <= 20; i++)
+                {
+                    ZoomLevel zoomLevel = layerZoomLevels[i - 1];
+                    if (zoomLevel.CustomStyles.Count != 0 || !IsDefaultStyleSet(zoomLevel))
+                    {
+                        if ((int)zoomLevel.ApplyUntilZoomLevel == 0)
+                        {
+                            if (!featureLayersInAllZoomLevels[i].Contains(layer))
+                            {
+                                featureLayersInAllZoomLevels[i].Add(layer);
+                            }
+                        }
+                        else
+                        {
+                            for (int j = i; j <= (int)zoomLevel.ApplyUntilZoomLevel; j++)
+                            {
+                                if (!featureLayersInAllZoomLevels[j].Contains(layer))
+                                {
+                                    featureLayersInAllZoomLevels[j].Add(layer);
+                                }
+                            }
+                        }
+                    }
+                }
+            }
+            return featureLayersInAllZoomLevels;
+        }
+        private bool IsDefaultStyleSet(ZoomLevel zoomLevel)
+        {
+            return IsDefaultAreaStyle(zoomLevel.DefaultAreaStyle) && IsDefaultLineStyle(zoomLevel.DefaultLineStyle) && IsDefaultPointStyle(zoomLevel.DefaultPointStyle) && IsDefaultTextStyle(zoomLevel.DefaultTextStyle);
+        }
+        private bool IsDefaultAreaStyle(AreaStyle style)
+        {
+            if (!style.IsActive)
+            {
+                return true;
+            }
+            bool isDefault = false;
+            if (style.OutlinePen.Color.IsTransparent && style.FillSolidBrush.Color.IsTransparent && style.OutlinePen.Brush is GeoSolidBrush)
+            {
+                isDefault = true;
+            }
+            if (isDefault && style.Advanced.FillCustomBrush != null)
+            {
+                isDefault = false;
+            }
+            if (isDefault && style.CustomAreaStyles.Count != 0)
+            {
+                isDefault = false;
+            }
+            return isDefault;
+        }
+        private bool IsDefaultLineStyle(LineStyle style)
+        {
+            if (!style.IsActive)
+            {
+                return true;
+            }
+            bool isDefault = false;
+            if (style.OuterPen.Color.IsTransparent && style.InnerPen.Color.IsTransparent && style.CenterPen.Color.IsTransparent)
+            {
+                if (style.OuterPen.Brush is GeoSolidBrush && style.InnerPen.Brush is GeoSolidBrush && style.CenterPen.Brush is GeoSolidBrush)
+                {
+                    isDefault = true;
+                }
+            }
+            if (style.CustomLineStyles.Count > 0)
+            {
+                isDefault = false;
+            }
+            return isDefault;
+        }
+        private bool IsDefaultPointStyle(PointStyle style)
+        {
+            if (!style.IsActive)
+            {
+                return true;
+            }
+            switch (style.PointType)
+            {
+                case PointType.Symbol:
+                    bool isDefault = false;
+                    if (style.SymbolPen.Color.IsTransparent && style.SymbolSolidBrush.Color.IsTransparent && style.SymbolPen.Brush is GeoSolidBrush)
+                    {
+                        isDefault = true;
+                    }
+                    if (isDefault && style.Advanced.CustomBrush != null)
+                    {
+                        isDefault = false;
+                    }
+                    return isDefault && style.CustomPointStyles.Count == 0;
+                case PointType.Bitmap:
+                    return style.Image == null && style.CustomPointStyles.Count == 0;
+                case PointType.Character:
+                    return style.CharacterSolidBrush.Color.IsTransparent && style.CustomPointStyles.Count == 0;
+                default:
+                    return true;
+            }
+        }
+        private bool IsDefaultTextStyle(TextStyle style)
+        {
+            if (!style.IsActive)
+            {
+                return true;
+            }
+            bool isDefault = false;
+            if (style.TextSolidBrush.Color.IsTransparent)
+            {
+                isDefault = true;
+            }
+            if (isDefault && style.Advanced.TextCustomBrush != null)
+            {
+                isDefault = false;
+            }
+            return isDefault;
+        }
+        private Feature GetInterSection(Feature feature, Collection<RectangleShape> boundingBoxes)
+        {
+            Feature result = null;
+            foreach (var boundingBox in boundingBoxes)
+            {
+                var intersection = feature.GetIntersection(boundingBox.GetFeature());
+                if (result == null)
+                {
+                    result = intersection;
+                }
+                else if (intersection != null)
+                {
+                    result = result.Union(intersection);
+                }
+            }
+            return result;
+        }
+        private bool IsDisjointed(Feature feature, Collection<RectangleShape> boundingBoxes)
+        {
+            bool result = true;
+            foreach (var boundingBox in boundingBoxes)
+            {
+                result = result && feature.IsDisjointed(boundingBox.GetFeature());
+            }
+            return result;
+        }
+        private Feature GetDifference(Feature feature, Collection<RectangleShape> boundingBoxes)
+        {
+            Feature result = null;
+            foreach (var boundingBox in boundingBoxes)
+            {
+                if (result == null)
+                {
+                    result = SqlTypesGeometryHelper.GetDifference(feature, boundingBox.GetFeature());
+                }
+                else
+                {
+                    result = SqlTypesGeometryHelper.GetDifference(result, boundingBox.GetFeature());
+                }
+            }
+            return result;
         }
     }
 }
 </code>
@@ Line 789: / Line 1149: @@
 }
 </code>
+====BoundingBoxRow.cs====
+<code csharp>
+namespace WorldMapKitDataExtractor.Model
+{
+    public class BoundingBoxRow
+    {
+        public System.Windows.Controls.TextBox txtUpperLeftPoint { get; set; }
+        public System.Windows.Controls.TextBox txtLowerRightPoint { get; set; }
+        public System.Windows.Controls.ComboBox cmbStartZoomLevel { get; set; }
+        public System.Windows.Controls.ComboBox cmbEndZoomLevel { get; set; }
+        public System.Windows.Controls.CheckBox chkRowEnabled { get; set; }
+        public BoundingBoxRow() { }
+        public BoundingBoxRow(System.Windows.Controls.TextBox txtUpperLeftPoint, System.Windows.Controls.TextBox txtLowerRightPoint, System.Windows.Controls.ComboBox cmbStartZoomLevel, System.Windows.Controls.ComboBox cmbEndZoomLevel, System.Windows.Controls.CheckBox chkRowEnabled)
+        {
+            this.txtUpperLeftPoint = txtUpperLeftPoint;
+            this.txtLowerRightPoint = txtLowerRightPoint;
+            this.cmbStartZoomLevel = cmbStartZoomLevel;
+            this.cmbEndZoomLevel = cmbEndZoomLevel;
+            this.chkRowEnabled = chkRowEnabled;
+        }
+    }
+}
+</code>
+====BoundingBoxWithZoomLevels.cs====
+<code csharp>
+using ThinkGeo.MapSuite.Core;
+namespace WorldMapKitDataExtractor.Model
+{
+    public struct BoundingBoxWithZoomLevels
+    {
+        public RectangleShape BoundingBox { get; }
+        public int StartingZoomLevel { get; }
+        public int EndingZoomLevel { get; }
+        public BoundingBoxWithZoomLevels(RectangleShape boundingBox, int startingZoomLevel, int endingZoomLevel)
+        {
+            this.BoundingBox = boundingBox;
+            this.StartingZoomLevel = startingZoomLevel;
+            this.EndingZoomLevel = endingZoomLevel;
+        }
+    }
+}
+</code>
 ====AssemblyInfo.cs====
 <code csharp>

User Tools

Site Tools

Differences

Page Tools