The Microsoft MapPoint Add-in for SQL Server (originally released for SQL Server 2008) is a free, Windows Presentation Foundation (WPF)-based utility designed to bridge Microsoft’s legacy desktop mapping software with native SQL Server spatial capabilities. It allows developers and data analysts to seamlessly visualize, query, and optimize spatial data directly between the two environments. Core Capabilities of the Add-in
Direct ADO.NET Connectivity: The add-in connects natively to standalone or networked SQL Server instances using ADO.NET. It lets you select any table containing geometry or geography columns to add directly as a map layer.
On-the-Fly Optimization: To maximize performance, the tool features built-in query optimization strategies. You can force spatial operations to utilize Common Table Extensions (CTEs) and specify detailed SQL WHERE clauses before data downloads.
Dynamic Data Generalization: To speed up rendering, the add-in reduces the client-side data footprint by automatically limiting records to the current map extent and simplifying complex spatial polygons into lighter-weight versions.
Offline Portability: Once spatial data layers are pulled from SQL Server and visually customized (using built-in symbology dials), the final product can be saved locally to a disk. This map can be distributed to stakeholders who do not have a live connection to the back-end database server. Best Practices for Optimizing Spatial Data in SQL Server
Because MapPoint relies heavily on the indexing mechanics of the database engine it is connected to, maximizing performance requires structuring your SQL Server spatial layers correctly. 1. Implement Strict Grid Tessellation Tuning
SQL Server breaks spatial data down using a hierarchical grid layout. Standard indexing configurations can cause performance to tank depending on object shapes.
For Simplistic Points: Use tight, flat grids. Settings such as LOW across all grid levels (or a hierarchy like MEDIUM, LOW, LOW, LOW) prevent the query planner from doing wasteful calculations on single-coordinate items.
For Complex Polygons: When mapping out large, sprawling geographical shapes, scale up the CELLS_PER_OBJECT parameter (e.g., from 64 to 8192) to match structural complexity. This mitigates excessive precision filter evaluations. 2. Apply Spatial Data Simplification
Do not pass highly precise geographic objects directly to the mapping UI if high resolution isn’t required.
Split up large MultiPolygon collections into individual row items to shrink deserialization workloads.
Use built-in database methods like STReduce() to remove unnecessary vertex noise from geometric lines before passing the dataset down to the MapPoint interface. 3. Build a Bounding Box Filter Strategy
Instead of running expensive distance evaluations (like STDistance) across an entire database, use a Bounding Box approach. Find the corner coordinates of your targeted mapping window, persist those constraints in a temporary index, and use standard non-clustered indexes to pre-filter your data prior to execution. Alternative Mapping Options
Because the MapPoint desktop software lineup was discontinued by Microsoft, modern environments utilize newer tools to interact with SQL Server spatial layers: Spatial Indexing: From 4 Days to 4 Hours
Leave a Reply