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
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
Helped bing to get better look then google map
Does the "add layer" feature work for non spatial data? I tried it, but the table/columns I linked did not have geography or geometry data types. They were simply varchars/nchars. Add layer button was disabled.
@ruchi.rhodes
You cannot add a non-spatial table directly as a map layer because there's no location information to used to map each row. There are several ways you can map the data, however.
1. Add a spatial column and populate it using some technique such as geocoding the addresses. MapPoint can do this with various file formats.
2. Create a view in SQL Server that joins your non-spatial table to a spatial table and then add a layer based on this view.
3. Use the Common Table Expression capability in the add-in's "Add Layer" dialog to define a view on the fly that joins your non-spatial table to another spatially-enabled table such as you might have used in #2.
I'd recommend method #3 if you have a spatial table that you can join your data to.
- Eric
