SQL Server and MapPoint 2009, Together At Last

 

SQL Server and MapPoint 2009, Together at Last It’s like a Reese’s Peanut Butter Cup…what in the world has taken us so long to be these two together? Rhetorical question; however, I now bestow upon you, for the first time outside the walls of Redmond, The Microsoft® MapPoint® Add-In for SQL Server (download it FREE!). The Microsoft® MapPoint® Add-In for SQL Server gives you the power to visualize and manipulate your spatial data – stored in Microsoft SQL Server 2008—on a MapPoint map. To use the Add-in you need SQL Server 2008, MapPoint 2009, and the MapPoint Add-in for SQL Server – all are available to try for free from Microsoft.com/Downloads.

 

At a high level, you can now:

 

  • Create maps from your spatial and non-spatial data stored in SQL Server 2008.

  • Customize the display of your map data using a variety of query and retrieval options and map symbolization.

  • Query, edit, and insert individual rows in your database from the map.

  • Seamlessly integrate maps into the work you do in Office programs.

 

 

 

For the first time, natively, you can connect to your SQL Server 2008 spatial database right from MapPoint and pull in the respective fields, filtering data to be visualized in MapPoint 2009. “But CP, ESRI just launched MapIt. I’m so confused.” Sure, good question – allow me to elaborate. MapPoint is an offline mapping software product. It installs and runs right there on your nice, private hard drive. SQL Server 2008 Express also runs right on your nice, private hard drive and spatial indexing and support for geography and geometry types are natively included. See where I’m going with this? You can have a completely offline scenario for mapping GIS data on laptop using MapPoint 2009, SQL Server 2008 Express and of course the bridge – Microsoft® MapPoint® Add-In for SQL Server. Yay! MapPoint 2009 retails for around $300Lo. SQL Server Express is free. The Microsoft® MapPoint® Add-In for SQL Server is free. So, you get the power of some hardcore data visualization with offline capabilities for the price of MapPoint 2009. Holler!

 

 

 

Let’s dive in a little deeper, shall we? What can you do with The Microsoft® MapPoint® Add-In for SQL Server? Here’s the exhaustive list I stole from the product sheet.

 

Map Management

 

  • Create, open, edit, and save layers

  • Reload a map file into MapPoint when creating a new map

  • Map files are stored as XML and can be edited with text editors or created by external programs

  • MapPoint will reflect any database changes when a map is re-opened.

  • Information stored in each map file include layer definitions, symbology, map extent, MapPoint base map type, current MapPoint map view and database connections

 

 

 

 

 

 

Database Connection

 

 

 

  

  • Create a map from data from more than one database

  • Database queries are generated in one of three forms (simple SQL, selection into temp tables, using the sq_executesql stored procedure) to match query performance and spatial index utilization with the database version

  • database query timeout can be set to handle different database configurations

 

 

 

  

Map Layers

 

 

 

  

  • Select the data source for each layer from a list of tables with geography columns

  • Dynamically view the data source by entering a SQL Server common table expression (CTE)

  • user can select a geography column for each feature’s geography

  • user can optionally select a column to be the source for text for feature labeling on the map

  • the optional generalize distance can be entered to reduce the amount of detail for each feature

  • the optional feature limit can restrict the amount of data returned by the database

  • whether the layer is based on a table or a CTE, the user can enter a SQL WHERE clause to select specific rows

  • WHERE clauses (and the CTE definition) can contain spatial queries. For example, a WHERE clause may select only those features within a certain area contained in a row in a different table

  • WHERE clause execution can be optimized by using a parameterized query

  • SQL expressions are fully validated before being used to creating layers

  • features can be limited to a particular area of the map

  • the mapping limit can be taken from the current map view, from a selection box on the map, or the coordinates can be entered directly

  • the geometry type of the features in a layer can all be the same type, or differ (points, lines, or polygons)

  • the feature geometry shown on the map may be altered by the SQL code used to define the layer

 

 

 

  

 

 

Mapping Symbology

 

 

 

 

  • the symbology for each layer can be set by the user

  • the Add-in provides different default symbols for each layer if the user does not specify the symbology

  • all features in a layer will share the same symbology

  • each geometry type (point, line, or polygon) can be symbolized with the same or different colors

  • a custom color picker supports assignment of pairs of complementary colors

  • polygons can be filled or unfilled, outlined or not outlined

  • points are displayed as pushpins with square or circular symbols

  • point symbols size, color, outline, fill, and center colors and sizes can be varied

  • line thicknesses are adjustable

  • polygons, lines, and text labels can be drawn above or below MapPoint’s native road layers

  • points (as pushpins) are always drawn on top of other map elements

 

 

 

 

Map Legend

 

 

 

 

  • the legend contains one entry for each layer on the map

  • the legend displays the Add-in’s main menu for accessing the Add-in’s functionality

  • layers are drawn on the map starting from the bottom of the layer list in the legend

  • shapes for polygons, lines, and text labels are drawn below pushpins

  • layer display can be turned on and off by clicking the checkbox beside the layer

  • layers which cannot be reestablished when a map file is opened are disabled

  • updating a disabled layer’s properties (table or connection) will re-enable the layer

  • layers can be redrawn by clicking each layer’s redraw button

  • layer drawing can be stopped by clicking each layer’s “stop” button

  • layers can be reordered by grabbing them with the mouse and dropping them at a new position in the legend

  • the entire map will not be refreshed when layers are reordered or individual layers are changed

  • each legend entry contains a swatch showing samples of how the layer’s features will appear on the map

  • the number of features and shapes/pushpins that are shown on the map can be displayed for each layer

  • many functions that operate on individual layers or the entire map (clear, refresh, change layer symbology, change layer properties, zoom to layer contents, and so on) are available by right-clicking on the legend

 

 

 

 

Spatial Query

 

 

 

 

  • multiple features can be retrieved for inspection and map display using the Spatial Query dialog

  • find features in a layer’s table using the layer’s refining queries, or all rows in the layer’s table without filtering

  • features are searched based on their spatial relationship to a selected feature

  • the selected feature can be the one highlighted on the map, or retrieved from the database based on another query

  • when based on another query, the user can enter any SQL expression to choose the selected feature from one of the existing map layers

  • query features can be within or outside of the selected feature

  • the selected feature can be buffered before performing the query

  • the query dialog displays a preview of the SQL query as options are changed

  • the query features are displayed on the map using a user-specified color scheme and generalize distance

  • query features can be limited to a maximum number of features

  • query features can be limited to the current map area

  • when a query is run, the features are shown on the map, in a data grid, and as a legend entry

  • since the query is added to the legend, it’s presentation can be refined further

  • the data grid contains one row per feature and displays all the column values for the feature

  • the data grid can be expanded to fill the entire spatial query dialog, leaving more room to inspect the data

  • one new layer can be created for each query, or one layer can be reused for each query and discarded when the spatial query dialog is closed

  • after a query is performed, the map can be zoomed to the resulting query features

 

 

 

 

 

 

Individual Feature Query

 

 

 

 

  • individual features can be identified by clicking on them on the map

  • attributes are displayed in a data grid in a window

  • the layer name and source table or CTE name is displayed in the window

  • attributes are displayed on the map in a collapsible MapPoint “Information” box

 

 

 

 

Feature Editing

 

 

 

 

  • existing features can be edited

  • information about the selected feature on the map will be displayed in a dialog for editing

  • individual attributes (column values) can be changed using the data grid in the dialog

  • attributes are color coded (changed, read-only, unique/key column)

  • only attributes with text representations can be changed in the dialog

  • the geometry of features based on lines and polygons can be changed by editing individual vertices on the map using MapPoint’s shape editing tools

  • point features can be moved by dragging the pushpin to a new location on the map

  • individual parts of a feature based on a multipart geography type can be edited

  • new features can be added to the database

  • user selects the layer, feature type (point, line, polygon) and the spatial reference before creating a new feature

  • location or vertices of the feature are added and edited on the map

  • attribute values entered in the grid in the dialog

  • features can be deleted using the edit dialog, or by selecting and deleting them from the map while the edit dialog is open

 

 

 

 

Data Import

 

 

 

 

  • supports importing data into new SQL Server tables from ESRI shapefiles and MapInfo MIF files

  • user specifies the name for new geography column, SRID for data

  • user specifies the target database, table, and key column for the new table

  • if a key column is selected (whether preexisting in import file or created by add-in) a spatial index will be created for the table

  • the imported data can be added as a new map layer after the import operation completes

Multiple Layers of Data from SQL

 

I like Dundas maps inside of Visual Studio as much as the next guy, but this is just WAY too easy and useful to pass up. Download Microsoft® MapPoint® Add-In for SQL Server now.

 

CP

Share on twitter Share on facebook Share on linkedin Share on linkedin