NOTE: THESE TOOLS DOES NOT YET WORK WITH THE CURRENT RC0 VERSION OF SQL SERVER 2008.
Download SqlSpatialTools.zip (333 kb)
The SQL Spatial Tools consists of two tools to make it easy to get experience with the new spatial capabilities of SQL Server 2008:
- Shape2Katmai: Uploads ESRI Shapefiles to Microsoft SQL Server Spatial.
- Sql Spatial Query Tool: Queries MSSQL Server 2008 and displays geometry output on a WPF-based interactive map.
Requirements:
- SQL Server 2008 November or February CTP installed on client machine.
- Only Windows Authentication login supported.
Related blogposts:
Shape2Katmai
Shapetypes supported (Z and M values are included if available):
- Point
- MultiPoint
- (Multi)Polygon
- (Multi)LineStrings
Settings:
- Replace existing table: If checked and table exists, it will be overridden. If not, data will be appended (Table schema must match).
- Planar/Geometry geometry: Chooses between planar or spherical/geographic data type. Select planar geometry if your data is in a projected coordinate system or contains geographic features that doesn't adhere to the SqlGeography limitations, or you need spatial operators not available on the geography type.
- Set SRID: Sets the Spatial Reference EPSG ID of the data* (Geography requires an SRID in the 4000s).
- Create Spatial index: Adds a spatial index to the table** (if appending to an existing table this is ignored).
- Table Name: Name of table in database.
- Include Z values: Includes Z part of geometry if available in shapefile.
- Include M values: Includes M part of geometry if available in shapefile.
- Geometry Name: Name of geometry column in the database
- ID Column Name: Name of ID column. Leave empty if you don't want an ID column**
- Attribute columns: Select the columns you want to upload to the database.
*Setting the SRID on the table also creates a uniform SRID constraint on the table.
**An ID column name are required to create a spatial index.
SqlSpatial Query Tool
Features:
- Runs SQL queries and display the spatial results on a map.
- Attributes are shown when hovering on geometry features.
- Supports both Geographic and Planar geometry types.
- LineStrings using geographic geometry are densified to show the correct path of the line (note: line-segments on polygons are not densified!)
- Results can be saved as ESRI ShapeFile format (experimental).
- Change rendering style based on attributes, allowing thematic maps.
- Custom background maps.
- Right-click features to view them as GML or Well-Known Text, or to copy the attributes.
Colors
Control the output colors by creating FillColor and LineColor columns.
Examples:
Green fill, no outline:
SELECT *, 'Green' AS FillColor, 'Transparent' as LineColor FROM countries
Hex-value based fill color, default outline (black):
SELECT *, '#FF49A9CC' AS FillColor FROM countries
If your color is integral, it will map the color to a rainbow-like scale using values from 0..16777216.
If you color is floating point, it will map the color to a rainbow-like scale using values from 0..1.
Values outside this range will be clipped.
Example:
If the color cannot be parsed it will use default colors (Black for outline, White for fill).
Lines
Control the thickness of lines/outlines by creating a LineThickness column. The value specifies the width in pixels.
Example:
SELECT *, 'Blue' as LineColor, 3 as LineThickness FROM Rivers
Points
Points are currently not supported for display, but you can use STBuffer() to make the points into buffer-polygons.
Example - Large city creates large circles:
SELECT geom.STBuffer(1), * FROM citiesSELECT geom.STBuffer(POPULATION), * FROM cities;
Lines in spherical geometry
If you use the geography type or a geometry type with an SRID between 4000 and 4999, lines will be shown as curves following a great circle. This doesn't mean that the lines are not straight - Just that projecting a sphere onto a flat screen distorts straight lines into curves (see more here). If they cross the dateline, they will also be cut in two (see below).
Note that this curvature is not applied to polygons, so you might see some discrepancies on large polygons with few vertices.
Background Map
If you use a geometry type with an SRID between 4000 and 4999 (which is assumed to contain longitude/latitude coordinate pairs) or a Geography type, a rough world map is shown as background. You can disable this in View menu. You can also specify your own world map to use. The image has to be a map in the "Plate Carrée" projection containing the entire world. Width should be twice the size of the height.
You can get a good satellite image as background from here: http://visibleearth.nasa.gov/view_rec.php?id=7105 (I've had good success with the 5400x2700 JPEG image, but beware that larger images could cause problems).
Export result as ShapeFile
This feature is very beta. Most datatypes are converted to strings and Z and M values are stripped.
Note that if you a mix of points, multipoints, lines and polygons in your resultset, up to four sets of shapefiles will be created.
Screenshots
Countries sharing border with Switzerland
Spatial join: Countries that contain the worlds 10 largest cities
Area within 100km of Denmark
Merging multiple queries to generate a combined map