Spatial Ed's blog

Setting Up A Delayed Sending Rule For Email In Microsoft Outlook

2012-12-18T01:45:59Z

Author: Ed Katibah, Microsoft SQLCAT

Reviewer: Abdallah Fateen, Microsoft

Introduction

A few weeks ago, a couple of us were in the hallway discussing email and the topic of recalling sent email came up. After some discussion, a colleague, Chris Kurt suggested another approach – implementing a delay in outgoing email, a.k.a. a delayed sending rule? That sounded like it could be a great general purpose approach and subsequently became the basis for this blog post since I liked it so much.

As a means of introducing this rule, how many times have you pressed the Send button on an email only to realize in the following moments that you wished to add (or delete) something on the email – maybe you left somebody off of the To… line who needed to be on it. Or worse, you had second thoughts about sending the email at all. Wouldn’t it be nice if the email were delayed for a few minutes before it was actually sent, allowing you time to be able to modify it (or delete it)? The following tutorial steps you through how to set up such a rule in Microsoft Outlook, in this case Outlook 2010 (Version 14). The post reviewer, Abdallah, reports that the instructions presented in this post work with Outlook 2013, also.

Quick Start

If you have done this before and/or want just the basic
steps to get started, here is a quick start guide:

A. In Outlook, select “File”, “Info”

B. On the “Account Information” screen, select “Manage Rules and Alerts”

C. On the “Rules and Alerts” screen, select “New Rule”

D. On the "Rules Wizard" screen, go the "Start from blank rule" section and select "Apply rule on messages I send"

E. On the next "Rules Wizard" screen, go to "Step 1: Select condition(s)" and check "on this computer only"

F. On the next "Rules Wizard" screen, go to the "Step 1: Select actions(s)"
section and...

check "defer delivery by a number of minutes"
select "a number of" minutes hyperlink... under Step 2 on the screen
enter the number of minutes to delay sending in the popup menu

G. On the next "Rules Wizard" screen, go to the "Step 1: Select exceptions(s)" section and...

check "except if the subject contains specific words"
select the "specific words" hyperlink in Step 2...”
enter the specific word(s) string in the "Search Text" menu

H. Enter the specific word or phrases string in the "Search Text" menu on which to apply the delay exception

I. This step is a recap of the last wizard screen, confirming the choices that you just made

J. On the next "Rules Wizard" screen, you can name, finalize and review the new rule in 3 easy steps…

K. Accept the final notification screen and you are done.

Steps

Here are steps I employed to set up a rule to delay sending mail by 4 minutes. I also added several key words, which when added (typically appended) to the subject line, instruct Outlook to bypass the delayed mail send rule and send immediately.

Step A: In Outlook, select “File”, “Info”

Step B: On the “Account Information” screen, select “Manage Rules and Alerts”

Step C: On the “Rules and Alerts” screen, select “New Rule”

Step D: On the "Rules Wizard" screen, go the "Start from blank rule" section and select "Apply rule on messages I send"

Step E: On the next "Rules Wizard" screen, go to "Step 1: Select condition(s)" and check "on this computer only"

Step F: On the next "Rules Wizard" screen, go to the "Step 1: Select actions(s)" section and...

check "defer delivery by a number of minutes"
select "a number of" minutes hyperlink... under Step 2 on the screen
enter the number of minutes to delay sending in the popup menu

Note: 1 minute is the default. I set my delay for 4 minutes as a personal preference. Set your delay to however many minutes you would like. You can always go back and modify the delay (or remove it altogether).

Step G: On the next "Rules Wizard" screen, go to the "Step 1: Select exceptions(s)" section and...

check "except if the subject contains specific words"
select the "specific words" hyperlink in Step 2...”
enter the specific word(s) string in the "Search Text" menu

Note: In this example, I used the keyword "<send>". In the following examples, I added a couple of additional key words, used to bypass the sending delay.

Step H. Enter the specific word or phrases string in the "Search Text" menu on which to apply the delay exception

Step I: This step is a recap of the last wizard screen, confirming the choices that you just made.

Step J: On the next "Rules Wizard" screen, you can name, finalize and review the new rule in 3 easy steps…

Step K. Accept the final notification screen and you are done.

Updated Spatial Features in the SQL Azure Q4 2011 Service Release

2011-12-13T22:37:21Z

The Q2 2011 Service Release of SQL Azure introduced the updated spatial data platform from SQL Server 2012. SQL Server 2012 spatial features are being released in SQL Azure in increments, beginning with Q2 2011 Service Release. With the introduction of the Q4 2011 Service Release for SQL Azure, a new TechNet Wiki post, Updated Spatial Features in SQL Azure Q4 2011 Service Release, is now available, providing the definative guide to the new SQL Server 2012 spatial features making their appearance in SQL Azure.

The Q4 2011 Service Relase enables the following spatial features relative to the Q2 2011 Service Release:

Spatial aggregates (CollectionAggregate(),ConvexHullAggregate(), UnionAggregate()), and geometry::EnvelopeAggregate() now work correctly. Geography::EnvelopeAggregate does not work because it returns a circular subtype which is not yet supported in SQL Azure (see the post for more details). Additionally, the system view, sys.spatial_reference_systems, previously missing from SQL Azure, is now available.

For more information on the new SQL Server 2012 spatial features, please see the TechNet Wiki: New Spatial Features in SQL Server Code-Named "Denali" Community Technology Preview 3.

Thanks,

- Ed

Potential Side-Effects of Improved Spatial Precision in SQL Server 2012 and the SQL Azure September 2011 Service Release

2011-11-15T23:07:14Z

In Microsoft SQL Server 2012 (previously known as SQL Server Code-Name “Denali”), all constructions and relations are now done with 48 bits of precision, compared to 27 bits used in SQL Server 2008 and SQL Server 2008 R2. This can result in differences that range from how individual coordinates (vertices) in spatial objects appear (rounding) to how computational results are produced in different versions of the database server for certain spatial operations.

Recently, SQL Azure has been upgraded in all data centers to incorporate the new SQL Server 2012 spatial library. This upgrade, known as the September 2011 Service Release, thus enables spatial operations in SQL Azure to be the computationally identical with SQL Server 2012.

Because of this new feature, results from spatial operations in SQL Azure September Refresh and SQL Server 2012 can differ from results produced in their respective prior versions. This technical note reviews the effects of the precision change and assists in identifying potential migration issues..

New Spatial Features in SQL Server Code-Named “Denali” Community Technology Preview 3

2011-08-08T18:53:53Z

We have just released a new white paper, New Spatial Features in SQL Server Code-Named “Denali”. This white paper covers the spatial improvements added to both CTP1 and CTP3 and becomes the definitive spatial guide to this release of SQL Server.

You can download the new server from this location: Microsoft SQL Server code-named 'Denali' CTP3 and try out the new spatial features.

Spatial Support Coming to the SQL Server Entity Framework

2011-06-23T16:25:36Z

Some of you may not have heard that spatial support is coming in the next version of the SQL Server Entity Framework. Here is the first paragraph from a post on this subject:

“One of the highly-anticipated features coming in the next version of Entity Framework is Spatial support. The team has been hard at work designing a compelling story for Spatial, and we would love to get your feedback on it. In this post we will cover:

The basics of SQL Server’s spatial support
Design goals
Walkthrough of design: CLR types, Metadata, Usage, WCF Serialization, Limitations
Questions (we want to hear from you!)

This entry will not cover the tools experience as we want to focus on what is happening under the hood. We will blog about the tools design at a later time. For now, be sure that we plan on shipping Spatial with Tools support.”

You can read the full post here: http://blogs.msdn.com/b/efdesign/archive/2011/05/04/spatial-types-in-the-entity-framework.aspx

Spatial Aggregates in SQL Server Denali

2011-04-27T21:20:38Z

SQL Server Code-named “Denali” is currently available as a Community Technology Preview (CTP). Denali introduces 4 new aggregates to the the suite of spatial operators in SQL Server:

Union Aggregate
Envelope Aggregate
Collection Aggregate
Convex Hull Aggregate

All aggregates are implemented as static methods which work for either the Geography or the Geometry data types. As an example, consider the following geography aggregate:

Geography::UnionAggregate(geog)

and its counterpart for geometry:

Geometry::UnionAggregate(geom)

The following introduces each aggregate with a short definition, a code sample and a visual illustrating what goes into the aggregate and what is returned. While aggregates are applicable to all classes of spatial data (points, linestrings, etc.) they are most effectively described and illustrated with polygons, which are used in the following discussion.

Union Aggregate

Definition: Combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.

SELECT Geography::UnionAggregate(geog)
FROM Counties
WHERE name_1 = 'Washington';

Envelope Aggregate

The unlike other aggregates, the Envelope Aggregate exhibits different behavior depending on whether is is applied to a geography or a geometry type. For the geometry type, a “traditional” rectangular polygon is calculated which closely bounds the selected input objects. For the geography type, a circular object is calculated which loosely bounds the selected input objects. Furthermore, the circular object is defined using the new CurvePolygon feature.

Envelope Aggregate for the Geometry Type

Definition: Returns a bounding rectangular polygon object, which encloses one or more spatial objects.

SELECT Geometry::EnvelopeAggregate(geom)
FROM Counties
WHERE name_1 = 'Washington';

Envelope Aggregate for the Geography Type

Definition: Returns a bounding circular object as a CurvePolygon which encloses one or more spatial objects.

SELECT Geography::EnvelopeAggregate(geog).STCurveToLine()
FROM Counties
WHERE name_1 = 'Washington';

Note the STCurveToLine() method in the sample code, above. This new method is used to output spatial objects in a manner which older visualization systems can digest.

Collection Aggregate

Definition: Returns a geometry collection with one geometry part for each spatial object(s) in the selection set.

SELECT Geography::CollectionAggregate(geog)
FROM Counties
WHERE name_1 = 'Washington';

Convex Hull Aggregate

Definition: Returns a convex hull polygon which encloses one or more spatial objects.

SELECT Geography::ConvexHullAggregate(geog)
FROM Counties
WHERE name_1 = 'Washington';

Technorati Tags: SQL Server,Denali,spatial,aggregates,union,envelope,collection,convex hull

Spatial Support for Persisted Computed Columns In Denali CTP1

2011-01-25T22:55:21Z

A few weeks ago, in the whitepaper, New Spatial Features in SQL Server Code-Named “Denali” Community Technology Preview 1, new spatial support for persisted computed columns was presented. This post will elaborate on this new feature, describing how it is different from prior support in SQL Server 2008.

In SQL Server 2008 you could create a column in a table defined by a spatial method. For instance, you could create a column to store and persist the area of a polygon using the STArea() method, for instance, as long as that method did not return a spatial type (Geometry or Geography):

CREATE TABLE LOCATION (
ID INT PRIMARY KEY,
GEOM GEOMETRY,
AREA AS GEOM.STArea() PERSISTED
);

Note that the computed column, AREA, will be of type FLOAT.

In SQL Server Denali CTP1, this functionality is supported and augmented with the ability to persist a computed spatial column (Geometry or Geography) in the table definition. For example, lets say that you wanted to define the centroid of the polygon in the GEOM column in the table DDL. In Denali CTP1 (but not in SQL Server 2008) you can define this as follows:

CREATE TABLE LOCATION (
ID INT PRIMARY KEY,
GEOM GEOMETRY,
AREA AS GEOM.STArea() PERSISTED,
CENTROID AS GEOM.STCentroid() PERSISTED
);

Note that the computed column, CENTROID, will be of type GEOMETRY.

Here is how this table looks in SSMS:

Technorati Tags: SQL Server,spatial,2008,Denali,CTP1,computed,persisted,column

Database Compatibility Levels: Getting Existing Spatial Databases Ready For Denali

2011-01-17T16:52:54Z

With the availability of the new SQL Server code-named “Denali” CTP1 release, some of you will be moving existing SQL Server 2008 databases into the new server. In order to enable the new spatial features in Denali, you will need to update the database compatibility level for existing SQL Server 2008 databases. For Denali, the compatibility level is 110 and can be set with the following T-SQL:

--Set database compatibility level to SQL Server Code-Named "Denali"
ALTER DATABASE <database name>
SET COMPATIBILITY_LEVEL = 110;

You can get information on the compatibility level of all databases in a given SQL Server instance with the following stored procedure:

EXEC sp_helpdb

For more information on database compatibility levels please see: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Technorati Tags: compatibility level,SQL Server 2008,spatial,Denali

New Functions Added To The SQL Server Spatial Tools CodePlex Project

2010-12-30T17:00:32Z

Two new functions have been added to the SQL Server Spatial Tools CodePlex Project:

FilterArtifactsGeography
FilterArtifactsGeometry

These new functions, as the names imply, are designed to remove unwanted components from spatial objects. For instance, consider the output from the Reduce() method on a Geography object which results in a GeometryCollection consisting of a MultiPolygon and many Point components. The FilterArtifactsGeography function can be used to remove the Point components and return only the MultiPolygon.

As one would imagine, these functions also allow the removal of LineStrings and Empty geometries. Arguably, however, the most interesting feature of the new functions is their ability to remove Polygons – specifically very thin polygons (often called “slivers”). Slivers can creep into spatial objects from a variety of ways, most commonly from union operations on polygons which do not precisely match each others adjoining edges. The polygon filtering feature of these new functions employs a simple but very effective algorithm which allows a fair degree of control over which Polygon components are removed from the input object. Here is the formula:

ring.STArea < ringTolerance * ring.STLength

where: ringTolerance is a value supplied by the user
ring.STLength is calculated by the function
and is the circumference of the ring

In practice, this allows very thin polygon rings (slivers) to be detected and removed while leaving more typically shaped polygon rings alone. The presumption is, of course, that slivers are undesirable but non-sliver rings are desirable.

Here is the more formal description of the FilterArtifactsGeography() function:

SqlGeography FilterArtifactsGeography(SqlGeography geography, bool filterEmptyShapes, bool filterPoints, double lineStringTolerance, double ringTolerance)

Selectively filter unwanted shapes from input geography, with the following arguments:

geography SQLGeography – Input geography object.
filterEmptyShapes boolean – A value of 1 will remove all empty shapes while a value of 0 will not remove any empty shapes.
filterPoints Boolean – A value of 1 will remove all point/multipoint shapes while a value of 0 will not remove any.
lineSTringTolerance double – Remove all linestring/multilinestring shapes shorter than the provided tolerance. A value of 0 will not remove any linestring/multilinestring shapes.
ringTolerance double – Remove all polygon rings thinner than provided tolerance (e.g. ring.STArea< ringTolerance * ring.STLength). A value of 0 will not remove any rings.

The FilterArtifactsGeometry function is identical in description with the substitution of geometry for geography. Both functions will always return the simplest OGC construction possible.

Here is a pseudo-code example of the FilterArtifactsGeography function in use:

FilterArtifactsGeography(@g, 1, 1, 4, 2)

Here is an example using the FilterArtifactsGeometry function:

        1: DECLARE @g GEOMETRY

       2: SET @g = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (

       3:                                      LINESTRING (51 51, 99 99), 

       4:                                      MULTILINESTRING ((52 50, 70 50),(80 50, 100 50)),

       5:                                      POINT (50 50),

       6:                                      POINT EMPTY,

       7:                                      MULTIPOINT ((30 30),(31 31), (32 32)),

       8:                                      POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0),

       9:                                               (10 80, 20 80, 20 90, 10 90, 10 80),

      10:                                               (10 10, 90 10, 90 11, 10 11, 10 10)))'

      11:                                    ,4326)

      12: SELECT @g

Here is the resulting object after using the FilterArtifactsGeometry() function to remove empty components, Points/MultiPoints, LineStrings/MultiLineStrings and thin polygon rings:

       1: DECLARE @g GEOMETRY

       2: SET @g = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (

       3:                                      LINESTRING (51 51, 99 99), 

       4:                                      MULTILINESTRING ((52 50, 70 50),(80 50, 100 50)),

       5:                                      POINT (50 50),

       6:                                      POINT EMPTY,

       7:                                      MULTIPOINT ((30 30),(31 31), (32 32)),

       8:                                      POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0),

       9:                                               (10 80, 20 80, 20 90, 10 90, 10 80),

      10:                                               (10 10, 90 10, 90 11, 10 11, 10 10)))'

      11:                                    ,4326)

      12: SELECT dbo.FilterArtifactsGeometry(@g,1,1,200,1.1)

POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0), (10 80, 20 80, 20 90, 10 90, 10 80))

Note: The current SQL Server Spatial Tools CodePlex Project is designed for use with SQL Server 2008 (including R2). Some functions in this project will not perform correctly in Denali CTP1, including the two functions described in this post. A new version of this project will be created which will be Denali-specific.

Technorati Tags: SQL Server,spatial,CodePlex,Spatial Tools,artifacts

FME Webinar: Practical Techniques for Loading Data into SQL Server

2010-12-09T19:40:43Z

One of the most often asked questions about SQL Server Spatial is “How do I load data into the spatial types?”. Safe Software has long provided solutions for this dilemma with their excellent ETL tool, Feature Manipulation Engine (FME). On December 14th, Safe will be presenting an FME Webinar entitled “Practical Techniques for Loading Data into SQL Server”. This webinar will cover such topics as:

Support for multiple spatial columns in a table
Loading spatial data into SQL Azure Spatial
The new, low cost, FME Desktop SQL Server Data Loading Edition
The FME Extension for SSIS

I would encourage anyone with an interest in this topic to register at http://fme.safe.com/forms/SQLServerWebinar.

Additionally, there is another page, http://www.safe.com/c/load-data-into-sqlserver.php, which includes links to an overview movie as well as links to the webinar.

Lastly, you can get more information on FME solutions for SQL Server on Safe’s SOLUTIONS FOR MICROSOFT SQL SERVER USERS page.

A Short Discussion On The ShortestLineTo() Method

2010-11-19T20:03:32Z

In a recent post to the SQL Server Spatial Forum, the subject of finding the closest point from one geometry to another resurfaced. While this was a difficult problem with SQL Server 2008, the new “Denali” CTP has introduced a method, ShortestLineTo(), which provides a solution to this problem.

Taking the geometries provided in the forum post by elangeo on Nov 16, 2010, @h and @g, I created a new geometry, @sl which contains the “shortest line” between the two geometries:

DECLARE @h GEOMETRY = 'POINT(175346.325596772 202579.692494631)';

DECLARE @g GEOMETRY = 'POLYGON((175373.37654079497 202663.47566268593, 175309.02370874584 202626.29556666315, 175328.32617276162 202595.99009463936, 175328.33046075702 202595.99284664169, 175335.22780476511 202600.4390546456, 175335.27100476623 202600.37697464228, 175335.33071676642 202600.29115064442, 175342.50012476742 202604.9433106482, 175343.06434877217 202605.3093906492, 175355.01686077565 202585.1888146326, 175355.22690878063 202584.8352146335, 175346.57487677038 202579.83649462834, 175350.90825277567 202572.54171062633, 175351.08706877381 202572.240654625, 175346.8919967711 202569.81697462127, 175345.81129277498 202569.19259062409, 175345.50646077097 202569.01646262407, 175352.43746877462 202558.13460661471, 175378.08418879658 202517.86843058839, 175378.47298879176 202517.2579985857, 175387.97513280064 202521.08609458804, 175392.46217280626 202523.75739059225, 175415.91702082008 202537.72103860229, 175429.48124483228 202545.79636660591, 175439.01698883623 202552.97799861059, 175438.9791008383 202553.04180661216, 175425.85634882748 202575.13236662746, 175373.37654079497 202663.47566268593), (175387.31574080139 202542.21063860506, 175387.92713280022 202542.56340660527,175385.82249280065 202546.25569460541, 175392.09526080638 202549.76961461082, 175391.75484480709 202550.37588660792, 175400.52566081286 202555.39905461296, 175402.95503681153 202551.16513460875, 175403.52956481278 202551.49115061015, 175408.33698881418 202543.07399860397, 175392.2850208059 202533.85300659761, 175387.31574080139 202542.21063860506))';

DECLARE @sl GEOMETRY = @h.ShortestLineTo(@g);

Here is an illustration of how the geometries appear (note that I have buffered the point, @h, to make it more visible and located it with a red arrow).

If I zoom in on the point, you can see the new “shortest-line-to” linestring:

If the resulting linestring is displayed as Well-known Text (WKT), you can observe that the ShortestLineTo() method returns two coordinates in the “from, to” order. In this case, from the point @h, to the polygon @g:

SELECT @sl.STAsText();
--Result: LINESTRING
-- (175346.325596772 202579.692494631, “from” point
-- 175346.57487677038 202579.83649462834) “to” point

Thus, the second coordinate (“to” point), 175346.57487677038 202579.83649462834, is the nearest point on the polygon @g from the point @h. If you always contstruct your queries this fashion, you can reliably use the following “method” to retrieve the “closest point”:

SELECT @h.ShortestLineTo(@g).STPointN(2);

It should be noted that the ShortestLineTo() method does NOT “snap” between the nearest vertices on the input objects. This can be illustrated with the following simple example:

DECLARE @c GEOMETRY = 'CIRCULARSTRING(1.5 1, 1.6 1.1, 1.5 2)';
DECLARE @g GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @g
UNION ALL
SELECT @c.ShortestLineTo(@g)

To emphasize this point (no pun intended), I’ve drawn in the defining vertices on the following illustration:

The resulting points on the shortest-line-to linestring are unique and not part of the input object definitions:

SELECT @c.ShortestLineTo(@g).ToString();
--Result: LINESTRING (1.740312423743285 1.5, 2.2 1.5)

The careful reader will wonder how I drew the circularstring in the above illustration since circular arcs are not supported in the Spatial results tab in SSMS currently. To do this, I converted the circularstring @c into a linestring using the new STCurveToLine() method, as follows:

DECLARE @c GEOMETRY = 'CIRCULARSTRING(1.5 1, 1.6 1.1, 1.5 2)';
DECLARE @s GEOMETRY = @c.STCurveToLine();
DECLARE @g GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @s
UNION ALL
SELECT @g
UNION ALL
SELECT @c.ShortestLineTo(@g)

If there are many solutions to the closest point between two geometries, the ShortestLineTo() method will pick one of them in an unpredictable way. Consider the following two polygons whose nearest points to each other result in multiple solutions:

DECLARE @g1 GEOMETRY = 'POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))';
DECLARE @g2 GEOMETRY = 'POLYGON((2.2 1, 3.2 1, 3.2 2, 2.2 2, 2.2 1))';

SELECT @g1
UNION ALL
SELECT @g2
UNION ALL
SELECT @g1.ShortestLineTo(@g2)

Lastly, if the distance between the two spatial objects being compared is 0 then ShortestLineTo() will return an empty geometry.

DECLARE @g1 GEOMETRY = 'POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))';
DECLARE @g2 GEOMETRY = 'POLYGON((2 1, 3 1, 3 2, 2 2, 2 1))';
DECLARE @sl GEOMETRY = @g1.ShortestLineTo(@g2);

SELECT @g1
UNION ALL
SELECT @g2
UNION ALL
SELECT @sl

SELECT @g1.ShortestLineTo(@g2).ToString();
--Result: LINESTRING EMPTY

To learn about other new spatial features in the “Denali” CTP1 release, please see the post: http://blogs.msdn.com/b/edkatibah/archive/2010/11/09/new-spatial-features-in-sql-server-code-named-denali-ctp1.aspx.

Technorati Tags: Technorati Tags: Spatial,SQL Server,Denali,CTP1,ShortestLineTo,STCurveToLine,Nearest Point,Closest Point

New Spatial Features in SQL Server Code-Named “Denali” CTP1

2010-11-09T17:44:39Z

We have just released a white paper, New Spatial Features in SQL Server Code-Named “Denali” Community Technology Preview 1, as your definitive guide to the spatial improvements in this release of SQL Server.

Of course, you can’t take advantage of the new spatial features without the new server. You can now download the new server, Microsoft SQL Server code-named 'Denali' - Community Technology Preview 1 (CTP1), today.

Also, don’t forget that the serialization format for the spatial types has been updated in Microsoft SQL Server CLR Types Serialization Formats [MS-SSCLRT] for those of you who need to know even more.

Let us know how you like the new spatial features.

Loading ANSS Earthquake Data Into SQL Server 2008

2010-04-20T17:49:09Z

The Advanced National Seismic System (ANSS) maintains a comprehensive catalog of worldwide seismic events (typically earthquakes) which is a good source of spatial data for SQL Server 2008. The ANSS Composite Catalog (formerly called the CNSS Earthquake Catalog) provides a search capability which allows the download of seismic events, from 1898 to present, using various search terms. In this post I will cover the following topics:

Download ANSS seismic data

Prepare the downloaded ANSS data

Create a database table for the ANSS data

Load the ANSS data

Add, update and index the spatial data column

Sample spatial queries on the ANSS data

DOWNLOAD ANSS SEISMIC DATA

It should be noted that the ANSS catalog contains a huge amount of data which should be carefully subsetted in order to avoid asking for too much information and unnecessarily tying up valuable computing resources attempting to service the request.

For this exercise, I have chosen to download a small subset of the available data. To start the download process, let’s go to the catalog search page.

Under the “Select earthquake catalog” section make sure that the “ANSS composite catalog (1898-present)” is selected (this is the only choice, currently). Then select the radio button for “Catalog in CSV format”. This will format the output data as comma separated values.

Next, go to the “Select earthquake parameters” section. In this example, I want all events from January 1, 2000 forward, so I set the Start date,time to 1 second before midnight on the day before (1999/12/31/,23:59:59). Since no “End date, time” was specified, the query will return the latest events. Likewise with the other fields which are unset (events with all magnitudes and all depths will be returned since they have not been set). The region specified by the Min/Max Latitudes/Longitudes will return the area covering all of the States of California and Nevada. Lastly, for Event Types:, I selected “All Events”.

Note that you can specify additional search parameters including a polygon within which to search. It is interesting to note that the ANSS documentation states that “[This] polygon must NOT cross the -180/180 degree longitude boundary, since the definition of the polygonal region becomes ambiguous”. Once this data is in the SQL Server Server GEOGRAPHY data type, such queries are easily supported.

Lastly, in the “Select output mechanism”, choose the "Send output to an anonymous FTP file…” radio button. I’ve set a rather high Line limit on output (500,000 records) since I expect a large number of events to be returned. When ready, select the “Submit request” button.

When your search is completed, you will receive a response in your web browser similar to the following:

Your search parameters are:

----------------------------------------------------------------------------------------------------------------

Your search parameters are:

    * catalog=ANSS
    * start_time=1999/12/31,23:59:59
    * end_time=2010/04/14,22:27:47
    * minimum_latitude=31.910759
    * maximum_latitude=42.192383
    * minimum_longitude=-124.755360
    * maximum_longitude=-113.17570
    * event_type=A

Output has been placed in anonymous ftp.
    host: www.ncedc.org
    file: /outgoing/userdata/web/catsearch.15725
    URL:   ftp://www.ncedc.org/outgoing/userdata/web/catsearch.15725
    Size: 454847 lines (34187332 bytes)
File will be automatically deleted in 2 days.

----------------------------------------------------------------------------------------------------------------

Note that the size entry returned a value (454,847 ) which is smaller than the Line limit on output - a value of 500,000. This indicates that we have retrieved all of the desired events.

To retrieve the output file (in this case called “catsearch.15725”) use your favorite ftp tool.

PREPARE THE DOWNLOADED ANSS DATA

With file containing the selected events downloaded, the first thing I like to do is to rename the file to indicate its type, in this case a “csv”. So, the file “catsearch.15725” was renamed to “catsearch.15725.csv”. I also like to save the search parameters associated with the file, so I created a file, “README_catsearch.15725.txt” and saved the search parameters from the web page. Here is a summary of the workflow, so far:

a. Rename the catsearch.15725 file to catsearch.15725.csv

b. [optional] Create a text file, README_catsearch.15725.txt, to store the search parameter metadata.

The ANSS downloaded data is now formatted as a comma-separated, UTF-8 encoded text file. Each record is terminated with newline character ('\n'). SQL Server 2008 does not support UTF-8, expecting such data to be in UTF-16 encoding with each record delimited with a carriage return, newline combination ('\r\n'). Additionally, it needs a Byte Order Marker (BOM) at the beginning of the file. For this exercise, I chose to use the Windows text editor UltraEdit Professional to perform the conversion. Here is the workflow I used with UltraEdit:

c. Open the catsearch.15725 .csv file in UltraEdit.

Click the “No” button when asked if you want to convert to DOS format:

d. Choose File –> Save As

e. Set File name: to “catsearch.15725.utf16.csv”

f. Set Save as type: to All Files, (*.*)

g. Set Line Terminator: to DOS Terminators – CR/LF

h. Set Format: to UTF-16

Here are how steps e – h are shown in the Save As dialog box:

i. Choose Save

ULTRAEDIT NOTE ON LARGE FILE HANDLING: When files sizes get very large (several million records typically), it is useful to configure UltraEdit appropriately prior to editing. You may find more information on how to perform such a configuration here:

http://www.ultraedit.com/support/tutorials_power_tips/ultraedit/large_file_handling.html

DATA NOTE: I found an occasional anomaly* in the ANSS data which will cause input rows to fail to be inserted into SQL Server. Additionally, the technique used to load this data, BULK INSERT, fails to correctly write these rows out to the specified ERRORFILE* (see Update, below). Because of this, I recommend that you locate and fix these anomalies prior to data loading.

Specifically, there are instances in the DATETIME field of the input data where at least one of the components has a value of “60” in the time string. Here is an example:

SQL Server will not accept this value and rejects the row during insert. To fix this particular problem (shown in the example, above), in which the seconds component is set to 60, it is necessary to increment the minutes component by 1 and set the seconds component to 00. Since there were only 5 such occurrences out of 454,000+ records, I went ahead and made the changes in UltraEdit directly (using the search string “:60”). I will look into other techniques for handling these errors and possibly publish my findings in a future blog post.

Additionally, I found another issue with the data (1 single record) which had a value of 24 for the hours time component. This record was rejected by BULK INSERT with the following error message:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 67082, column 1 (DateTime).

You can search for the string “ 24:” to locate such data. Note that “fixing” this data entails reving the associated data to the next day.

----------------------------------------------------------------------------------------------------------------

* I talked with Bob Beauchemin about both the ANSS data time anomalies and the truncated BULK INSERT ERRORFILE entries. Bob was unable to reproduce either issue: the ANSS data he downloaded (which contained the data I downloaded) did not have any of the time faults that I found and there was not problem with the ERRORFILE records. Bob was using SQL Server 2008 R2 November CTP as I was also, so I’ll have to do a bit more research on this issue.

----------------------------------------------------------------------------------------------------------------

Update: I found the source of my confusion regarding BULK INSERT supposedly dropping the date component of the datetime string in the error file. When I opened the error file in Excel (it’s a .csv file, so it’s naturally associated with Excel), here is what I get:

Notice that the date component is dropped (and so is the hour component of the time string, btw). Also notice that Excel attempts to fix the time data but does fully complete the job.

When I open the same error file in a text editor, I get the expected error output:

2000/09/13 06:26:60.00,36.5225,-115.2721,2.88,1.20,ML,6,,,0.13,NN, 
2001/10/08 24:00:07.62,37.1190,-113.3735,1.16,2.59,Mc,9,180,49,0.65,UU, 
2002/07/10 01:40:60.00,37.4114,-117.1425,0.00,0.88,ML,13,,,0.08,NN, 
2004/09/21 04:16:60.00,37.9981,-118.6963,6.57,0.18,Mb,7,109,4,0.06,NN,1463 
2005/09/25 17:20:60.00,39.4529,-119.8317,3.06,0.36,ML,8,123,3,0.10,NN,1665 
2005/11/07 06:25:60.00,39.2235,-120.0856,8.82,0.37,ML,12,85,9,0.07,NN,1685

So, confusion over – there is nothing wrong with BULK INSERT error files.

----------------------------------------------------------------------------------------------------------------

CREATE A DATABASE TABLE FOR THE ANSS DATA

Here is the T-SQL to create a table called Earthquakes...

CREATE TABLE Earthquakes( 
  DateTime  DATETIME NOT NULL, 
  Latitude  FLOAT NULL,  
  Longitude FLOAT NULL, 
  Depth     FLOAT NULL, 
  Magnitude FLOAT NULL, 
  MagType   VARCHAR(12) NULL,  
  NbStation INT NULL, 
  Gap       FLOAT NULL, 
  Distance  INT NULL, 
  RMS       FLOAT NULL, 
  Source    VARCHAR(12) NULL, 
  EventID   VARCHAR(12) NULL 
); 
GO

For information on the column definitions, please see http://www.ncedc.org/ftp/pub/doc/cat1/catlist.1.

Note that the EventID column appears to be of type INT – that is until you come across a value such as the one below:

Consequently, I had no choice but to make this a character field in the CREATE TABLE DDL.

LOAD THE ANSS DATA

To load the data, you can use the following T-SQL for the BULK INSERT command:

BULK INSERT Earthquakes 
  FROM 'C:\Data\ANSS\catsearch.15725.utf16.csv' 
    WITH( 
      DATAFILETYPE = 'widechar', 
      FIRSTROW = 2, 
      FIELDTERMINATOR = ',', 
      ROWTERMINATOR = '\n', 
      MAXERRORS = 50, 
      ERRORFILE = 'C:\Data\ANSS\catsearch.15725.utf16.error.csv' 
    ); 
GO 
--(454846 row(s) affected)

The DATAFILETYPE is set to ‘widechar’ to support the UTF16 data format. The first record in the file contains the names of the fields, thus we need to start the data load with the second record (FIRSTROW = 2). Since this is a comma-separated value file, the FIELDTERMINATOR must be set to ‘,’. The row terminator is ‘\r\n’ but BULK INSERT demands that ROWTERMINATOR be set to ‘\n’ (go figure). I’ve set MAXERRORS to 50 from the default of 10 and specified an ERRORFILE to hold errant rows.

ADD, UPDATE AND INDEX THE SPATIAL DATA COLUMN

In order to accommodate SQL Server 2008 spatial data, I added a column of type GEOGRAPHY (named Location) to the Earthquakes table as follows:

ALTER TABLE Earthquakes 
  ADD Location GEOGRAPHY NULL; 
GO

With the Location column added to the table, I can now update the Location column using the Point constructor static method:

UPDATE Earthquakes 
  SET Location = GEOGRAPHY::Point(latitude,longitude,4326); 
GO 
-- (454846 row(s) affected) (00:00:16)

The value of 4326 in the Point constructor is the spatial reference identifier (SRID). This SRID indicates that the geographic coordinate system is WGS84. While not specified in the ANSS on-line documentation, this a reasonable assumption for global data such as this.

In order to create a spatial index, you must have a primary key on the table. The following T-SQL creates an identity column, ID, as a primary key:

ALTER TABLE Earthquakes 
  ADD ID BIGINT IDENTITY 
    CONSTRAINT ID_PK PRIMARY KEY(ID); 
GO

With that done, a spatial index can now be created on the Location column:

CREATE SPATIAL INDEX location_hhhh1_sidx  
  ON Earthquakes(Location) 
    USING GEOGRAPHY_GRID 
    WITH ( 
      GRIDS = (HIGH, HIGH, HIGH, HIGH), 
      CELLS_PER_OBJECT = 1, 
      PAD_INDEX = ON 
    ); 
GO 
--Results: 00:00:35 seconds

Since this is point data, setting the GRIDS parameters all to HIGH will typically yield the best performance.

SAMPLE SPATIAL QUERYIES ON THE ANSS DATA

Query 1: Plot all of the earthquakes with a magnitude > 3. Highlight the large earthquakes by creating a buffer polygon around their epicenter. In this case, the earthquake data was drawn over the county polygons for States of California and Nevada.

SELECT Location FROM Earthquakes WHERE Magnitude > 3 
UNION ALL 
SELECT Location.STBuffer(50000) FROM Earthquakes WHERE Magnitude > 7 
UNION ALL 
SELECT Geog FROM Counties 
  WHERE NAME_1 = 'California' or NAME_1 = 'Nevada'

Here is the same data plotted on a Bing Maps background using the Report Builder in the SQL Server 2008 R2 November CTP:

Note that the 7.2 magnitude earthquake (the largest in this dataset), which occurred below Mexicali on April 4, 2010, is displayed with a white star symbol.

Query 2. For the next query exercise, I wanted to take a look at the earthquake distribution relative to my house in Contra Costa County, California.

DECLARE @p GEOGRAPHY = GEOGRAPHY::Point(38.12345,-121.12345,4326); 
DECLARE @g GEOGRAPHY = 
  (SELECT Geog FROM Counties 
     WHERE NAME_1 = 'California' and NAME_2 = 'Contra Costa'); 
SELECT Location, DateTime, Magnitude, Depth 
  FROM Earthquakes 
    WHERE Location.STIntersects(@g) = 1 
UNION ALL 
SELECT @g, NULL, NULL, NULL -– Contra Costa Country polygon 
UNION ALL 
SELECT @p.STBuffer(750),NULL, NULL, NULL -– make my house location visible 
UNION ALL 
SELECT @p,NULL, NULL, NULL; -– plot the location of my house as a point 
-- (2925 row(s) affected) (00:00:04)

Note that my house is nicely remote from most earthquake activity. Was this by clever analysis or dumb luck?

Here is the same query in visualized in SSRS map. If you look closely, you can see my house location as a small purple triangle with the label “Spatial Ed” underneath the symbol.

Query 3. Let’s perform a query that’s a little more intensive spatially. Let’s find the 50 earthquakes with a magnitude greater than 2.0 nearest to my house. Then, let’s find the nearest earthquake with a magnitude greater than 3.0.

The technique to do a nearest neighbor query in SQL Server 2008 relies upon a numbers table, so let’s create that table:

--------------------------------------------------------- 
--Create Numbers Table 
--------------------------------------------------------- 
SELECT TOP 100000 IDENTITY(int,1,1) AS n INTO numbers 
  FROM master..spt_values a, master..spt_values b 
CREATE UNIQUE CLUSTERED INDEX idx_1 ON numbers(n); 
GO

Now I can query for the top 50 earthquakes nearest to my home’s location with a magnitude greater than or equal to 2.0:

-- Ed’s house (not the real location ;-) 
DECLARE @input GEOGRAPHY = GEOGRAPHY::Point(38.12345,-121.12345,4326); 
DECLARE @start FLOAT = 10000; -- meters 
WITH NearestNeighbor AS 
( 
SELECT TOP 50 WITH TIES *, b.Location.STDistance(@input) AS dist 
   FROM NUMBERS n 
     JOIN Earthquakes b WITH(INDEX(location_hhhh1_sidx)) 
       ON b.Location.STDistance(@input) < @start*POWER(CAST(2 AS FLOAT),n.n) 
     WHERE n <= 20 AND Magnitude >= 2.0 
     ORDER BY n 
) 
SELECT TOP 50 location, DateTime, magnitude, dist 
  FROM NearestNeighbor 
ORDER BY n, dist

Here is the query to find the nearest earthquake to my location with a magnitude greater or equal to 3.0:

DECLARE @input GEOGRAPHY = GEOGRAPHY::Point(38.12345,-121.12345,4326); 
DECLARE @start FLOAT = 10000; -- meters 
WITH NearestNeighbor AS 
( 
SELECT TOP 1 WITH TIES *, b.Location.STDistance(@input) AS dist 
   FROM NUMBERS n 
     JOIN Earthquakes b WITH(INDEX(location_hhhh1_sidx)) 
       ON b.Location.STDistance(@input) < @start*POWER(CAST(2 AS FLOAT),n.n) 
     WHERE n <= 20 AND Magnitude >= 3.0 
     ORDER BY n 
) 
SELECT 1 location, DateTime, magnitude, dist 
  FROM NearestNeighbor 
ORDER BY n, dist

This returns the following:

Location:  POINT(-122.1113 37.8965) 
DateTime:  2007-03-02 04:40:00.750    
Magnitude: 4.2          
Distance:  4084.75488358986

The map, below, is symbolized as follows:

Yellow triangle (in center of map): Spatial Ed’s house
Yellow circle (near top of map): earthquake >= 3.0 nearest house location
Red circles: 50 nearest earthquakes >= 2.0 nearest house location

Technorati Tags: SQL Server 2008,R2,Spatial Data,ANSS,CNSS,seismic,earthquake,bulk insert,T-SQL,Reporting Services,Report Builder,nearest neighbor,UltraEdit,numbers table

Spatial Data Support Coming To SQL Azure

2010-03-21T23:49:24Z

Last Wednesday (March 17th), Dave Robinson of the SQL Azure Team announced upcoming support for spatial data in SQL Azure. You can view the announcement at the MIX Conference in the video of Dave’s presentation, Building Web Applications with Microsoft SQL Azure (go to 18:40 on the timeline for the start of the spatial data portion of his talk).

Dave reports that spatial will be available in the SU3 release of SQL Azure, tentatively scheduled for this June.

I’ve been using SQL Azure with spatial support for a couple of weeks now and it works just like the spatial data support in SQL Server 2008 – same spatial data types, spatial methods and spatial indexes. It works in SQL Server Management Studio just like you would expect (see below).

Query performance is excellent. In the above example, the query executed in under a second against a table containing over 1.8 million rows (GeoNames points-of-interest for the United States).

The only real differences that I have found between SQL Server and SQL Azure are true for all data:

1. You need to have a clustered index on the table you are inserting data into.

Note that this can be satisfied by a primary key, which in turn is a requirement for creating a spatial index. You can view this a good thing for spatial ;-)

2. You may need to break your data loads up into chunks to prevent the connection to SQL Azure from timing out.

If you are loading data from an existing SQL Server database, a great tool is the SQL Azure Migration Wizard found on CodePlex. It currently offers some minor complaints about spatial data when you run it (spatial indexes in particular) but does the right thing anyway.

If I’m not mistaken, SQL Azure will become the first pure cloud database to offer spatial support. It will be interesting to watch how this new feature is put to use…

Technorati Tags: Spatial,Spatial Data,SQL Azure,SQL Server,SQL Server 2008,Migration Assistant

Inside Microsoft SQL Server 2008: T-SQL Programming – Source Code and Spatial Data for Chapter 14

2009-12-23T01:14:04Z

In the previous post, the content for Chapter 14, Spatial Data, was presented. This post will discuss the sample code and the spatial data associated with the chapter.

SOURCE CODE. The Source Code for Chapter 14 - Spatial Data is located in the zip file:

TSQLProgramming20090901.zip (457,765 B)

Within the zip file the source code is contained in the file:

Chapter 14 – Spatial Data.sql (156,256 B)

SPATIAL DATA. The spatial data for the chapter contains a selection of US-based data and provides the basis for many of the code examples used in the chapter.

For downloading ease, the spatial data is archived as a single zip file and which is subsequently split into 7 segments.

This is the view of the download site at Solid Quality Mentors:

   Tuesday, December 08, 2009  5:50 PM        45456 ReadMeZip.rtf
  Thursday, November 05, 2009  3:37 AM    100431872 Sample_Data.z01
  Thursday, November 05, 2009  6:43 PM    100431872 Sample_Data.z02
  Thursday, November 05, 2009  8:02 PM    100431872 Sample_Data.z03
  Thursday, November 05, 2009  9:10 PM    100431872 Sample_Data.z04
  Thursday, November 05, 2009 10:17 PM    100431872 Sample_Data.z05
    Friday, November 06, 2009  8:26 AM    100431872 Sample_Data.z06
    Friday, November 06, 2009  1:20 PM     89577422 Sample_Data.zip
 Wednesday, October 21,  2009 10:35 AM       457765 TSQLProgramming20090901.zip

After downloading all 7 of the Sample_Data.xxx files, you open the split zip files by opening the file with the .zip extension (Sample_Data.zip). Don't try to open any of the files with the numbered extensions - WinZip (and other "zip" programs) won't recognize them as zip files.

After restoring the Sample_data.zip archive, the resulting Sample Data folder contains the companion data for Chapter 14, Spatial Data. This folder is comprised of the following files:

BTS_shapefile.zip
Data_Dictionary.rtf
Readme_First.rtf
Sample_Data_Content_Attribution.rtf
SAMPLE_TEXT.txt
Sample_USA.zip

BTS_shapefile.zip. [44,382 MB compressed] This the zip archive contains the source shapefile for Highways table in the Sample_USA database.

Data_Dictionary.rtf. This document contains the metadata describing the tables contained in the Sample_USA database.

Sample_Data_Content_Attribution.rtf. This document describes the sources of data provided with this distribution and the license agreements, where applicable.

SAMPLE_TEXT.txt. This file contains tab-delimited data, including latitude and longitude, points-of-interest data, and is used as the basis for an example in Chapter 14. This data is derived from the GeoNames database.

Sample_USA.zip. [631,582 KB compressed, 2.33GB uncompressed] Contains the SQL Server 2008 backup file, Sample_USA.bak, containing the tables and registered assemblies used as the basis for coding examples in Chapter 14. The backup file is intended to be used to restore the database, Sample_USA to a SQL Server 2008 instance.

Tables:

dbo.CensusBlockGroups – [polygon] US Census Block Groups (212,942 rows)
dbo.Counties – [polygon] US Counties (3,146 rows)
dbo.CountiesFIPSCodes – [tabular] US Federal Information Processing Codes for States, Counties (3,141 rows)
dbo.GeoNames – [point] GeoNames points-of-interest for US (1,892,290 rows)
dbo.GeoNamesFeatures – [tabular] – Codes for GeoNames features (664 rows)
dbo.Highways – [linestring] US Highways (8,362 rows)
dbo.Nums – [tabular] Numbers table (20 rows)
dbo.States – [polygon] – US States (51 rows)
dbo.StatesFIPSCodes – [tabular] US Federal Information Processing Codes for States (51 rows)
dbo.ZIPCodes – [polygon] – US Census-based ZIPCodes (49,146 rows)

Programmability:

Assemblies registered to the database:

SQLSpatialTools – CodePlex SQL Server Spatial Tools project assembly

transformer – Assembly created in the Chapter 14 code examples

UnionAgg – Assembly created in the Chapter 14 code examples

The following images illustrate the spatial content for the tables with spatial columns in the Sample_USA database. The State of Nevada is used for the examples, but all data extends to the full USA.

CensusBlockGroups table Counties table Geonames table*

Highways table* States table ZIPCodes table

* the data in these images are clipped to less than 5000 objects, the limit of Management Studio’s spatial display capabilities and is unioned with the outline of the State of Nevada for context.

Inside Microsoft SQL Sever 2008: T-SQL Programming, Chapter 14, Spatial Data

2009-12-18T00:55:37Z

In a shameless bit of self-promotion, I’d like to call attention to the new book, Inside Microsoft SQL Server 2008: T-SQL Programming (Microsoft Press).

You can get further information on the book at Itzik’s blog or by going to Microsoft Press, but I wanted highlight Chapter 14 on spatial data in this post. Isaac Kunen and I collaborated on this chapter and together turned out 81 pages of action packed reading… ;-). While this chapter is designed to be a stand alone treatise on SQL Server Spatial, it is also a nice compliment to Alastair Aitchison’s book, Beginning Spatial with SQL Server 2008.

There are three main components to this chapter:

1. The chapter content, presented here by the table of contents

2. Source code for the chapter examples

3. Sample spatial data for use with the source code

Here is the table of contents for the chapter:

Chapter 14
Spatial Data
    Introduction to Spatial Data
    Basic Spatial Data Concepts
      Vector Data and the OGC Simple Features Type Model
      Planar and Geographic Coordinates
      Spatial Reference Identifiers
      Standards
      Working on the Ellipsoid
    Data
      Common Forms of Data
      Finding Spatial Data
      Loading Spatial Data
      Sample Spatial Data
    Getting Started with Spatial Data
      Creating a Table with a Spatial Column
      Well-Known Text
      Constructing Spatial Objects from Strings and Inserting into a Table
      Basic Object Interaction Tests
      Basic Spatial Operations
        Intersecting Spatial Objects
        Union of Spatial Objects
        Generalization of Spatial Objects
      Proximity Queries
        Distance between Spatial Objects
        Spatial Buffers
        Comparing Spatial Buffers and Distance-Based Calculations to Test
          Proximity
      The GEOGRAPHY Type
        The Geography Type and SRIDs
        The Geography Type and Coordinate Ordering
        Coordinate Systems and Units of Measure
        The Extent of Geography Objects
    Spatial Data Validity
      Data Validity Issues with Geometry Data
    Measuring Length and Area
      Comparing Length Measurements between GEOMETRY and GEOGRAPHY Instances
      Comparing Area Measurements between GEOMETRY and GEOGRAPHY Instances
    Indexing Spatial Data
      Spatial Index Basics
      SQL Server Spatial Indexes
      Using Spatial Indexes
      Geography Indexes
      Query Plans
      Integration with Spatial Methods
    Using Spatial Data to Solve Problems
      Loading Spatial Data
        Shape2SQL
        MapPoint 2009 Add-In for SQL Server
        Loading Spatial Data from Text Files
        Common Workflow Patterns
          Using the GEOMETRY MakeValid Method
          Forcing Polygon Ring Orientation
        Moving Geometry Data to Geography Data
        Using MakeValidGeographyFromGeometry
      Finding Site Locations within Geographic Regions
        Find High Schools with 2 KM of Interstate 5 in King County,
          Washington State
        Find Schools Within 4 KM of the Intersection of Interstate 5 and
          Interstate 405 in King County, Washington State
      Nearest Neighbor Searches
        Find the Nearest GeoNames Data around a Point Location
        Find Zip Codes around a Point Location
      Spatial Joins
      Processing Spatial Data
        Processing the Highways Table in the Sample_USA Database
        The WorkFlow
    Extending Spatial Support with CLR Routines
      Types on the Client
      User Defined Aggregate: Union and Dissolve
      Sinks and Builders: Linear Transformations
    Conclusion

My next post will cover the associated source code for the examples and the sample spatial data.

Technorati Tags: Inside Microsoft SQL Server 2008,T-SQL Programming,SQL Server 2008,T-SQL,Spatial,Spatial Data,Table of Contents,TOC

My Overheating IBM ThinkPad T60p Laptop

2009-11-13T22:27:53Z

My faithful old IBM T60p laptop has been with me for years. Over time, and every so slowly, it has been running hotter and hotter (especially when running complex spatial queries on SQL Server 2008 or generating map-based reports using the new SQL Server 2008 R2 Report Builder… ;-). A few weeks ago it got so hot that I had to take it off my lap … and then things went from bad to worse. Here’s the tale…

I decided that it was time to upgrade to Windows 7. I ordered a new Seagate 7200rpm 320GB hard drive for the laptop. I took the old internal hard drive out and put it into a NexStar3 USB2.0/eSATA 2.5-inch enclosure …

… and proceeded to do a fresh install of Windows 7.

The Windows 7 install went flawlessly. Even the most difficult (anticipated) parts of the install – new drivers for printers and other devices - went very smoothly. In my case, Windows 7 found all of the drivers automatically over the Net including the driver for the eSATA Express Port card necessary to connect to my NexStar3-enclosed hard drive with all my data on it. So far so good.

Then bad things begin to happen – for no apparent reason, the laptop started to shut down all by itself. After having this happen a few times (Windows 7, btw, recovered very nicely each time), I concluded that the shut downs occurred when the machine got very, very hot – perhaps a heat related shutdown? I did a web search to see if others were having this problem. I appeared to be all alone. I speculated that it might be one of the following reasons:

1. My use of an underpowered 65W power supply (its supposed to be a 90W supply but that brick is much larger and I had consequently been using the smaller power supply for the last couple of years).

2. The new operating system, Windows 7. I imagined that Windows 7 was using the CPU or GPU (or both) just a little bit harder than Vista and this was pushing the laptop “over the edge”.

After swapping out the 65W power brick for the proper 90W unit, it was still overheating and shutting down. I figured it had to be Windows 7. Desperate, I sought advice from the hardware technician who supports the SQL Server engineering team. He had a very simple suggestion:

1. Shut the machine down

2. Use compressed air to blow out accumulated dust from the CPU/GPU ventilation system in the upper left-hand corner of the laptop.

I was skeptical but, having nothing to lose, gave it a try. I liberally blasted both cooling ports on the laptop (see illustration, below)

Several of the air blasts dislodged a noticeable amount of dust. At the time it didn’t seem like enough to make any significant difference…

I powered up the laptop. Even after several hours of continuous use the machine was running cool and behaving perfectly. It’s now several days later and the laptop is running like new. Windows 7 is a delight. I installed SQL Server 2008 R2 on it easily and moved all of my spatial databases from the external hard drive to the laptop. I even got up the nerve to try the new Office 2010 beta distribution and again no problems.

The technician did say that it still might warrant a physical inspection of the cooling ports and further dust/cruft removal but this simple trick worked miracles on this particular machine.

Here is a cartoon, which just about sums it up.

Technorati Tags: laptop,overheating,IBM,T60p

A Spatially-Enabled Dinner In Austin, Texas: Some Observations Outside the Office

2009-10-07T19:02:44Z

Not having posted for a while, I thought I'd take a few minutes and provide some thoughts on spatial after successfully negotiating dinner here in Austin, Texas (October 6, 2009). I'm in town for the Texas GIS Forum, one of my favorite conferences, and always seek out barbeque when I can. A quick check on Bing Maps verified that my hotel was very close to Rudy's Bar-B-Q. I remembered that Rudy's was on a one-way road, next to a freeway, and wanted to verify my route using Live Search on my Windows Mobile Phone. The route that it provided seemed counter-intuitive: it wanted to take me across the freeway and have me reverse my route at an overpass (see below).

Bing Maps Route

I chose a more straight-forward route and finally coerced Live Search on my mobile to guide me that way (after repeated, "reroute from current location?" messages...). An added bonus was that Rudy's had an entrance off of my newly found route and I got there in a very direct fashion.

When I got back to the hotel, I decided to see how Google Maps handled the same route request. Google did the right thing and provided an almost ideal route (see below), though it is curious that Research Blvd, which parallels Highway 183 on both sides, is not shown - curious. It too missed the back entrance to the restaurant, but that's a minor detail.

Google Maps Route

When I got to Rudy's, I fired up the GPS-Photo tool on my mobile phone and took a series of images.

Entrance to Rudy's on Research Blvd.

View from parking lot

Outdoor seating is available...

For dinner, I had the lean brisket, white bread, beans, potato salad and an IBC Cream Soda - all for $12.19. The foot was great but the beans needed some spicing up. I added a bit of their famous barbeque sauce (below) to made the beans quite tasty... It was 91 degrees outside and quite humid (from my West Coast environment viewpoint) at 6:00 PM in the evening. Perfect barbeque weather.

Geocoded Barbeque Sauce (latitude 30.414167, longitude: -97.7467, WGS84)

When I got back to the hotel, I loaded each of the GPS-enabled images from the mobile phone into my favorite photo application: SpatialAlbum. I'll blog more about this application at a later point, but, in short, it allows you to load GPS-enabled digital images, using a drag-an-drop interface, into SQL Server 2008. It uses a lot of SQL Server 2008 goodies: spatial, sparse columns, filestreams, the new datetime2 type and overlays all spatial data on top of Bing Maps.

SpatialAlbum application

Here is the location of the photos I took using the Bing Maps feature of SpatialAlbum:

Rudy's location on Bing Maps

The following are some Bing Maps displays in SpatialAlbum showing various view/featues:

Synoptic view in the Aerial mode

Bird's eye view from the south

Bird's eye view showing back entrance...

Bird's eye view from the north showing parking lot

Bird's eye view of the main entrance off Research Blvd.

And there you have it - more information than you ever wanted on an obscure topic. Maybe I should do a WikiPedia page on Rudy's...

Technorati Tags: SQL Server 2008 R2,spatial,sparse columns,datetime2,filestream,Bing Maps,Google Maps,Bird's eye,SpatialAlbum,Austin,Texas,Rudy's,barbeque,IBC,Texas GIS Forum

Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 2

2009-06-06T08:09:33Z

In a long past post, Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 1b (August 2008), I discussed techniques for dealing with invalid data and the geography data type. In my last post, I listed the new geography validation functions in the SQL Server Spatial Tools project on CodePlex. In this post, I'm going to use three of these new functions:

IsValidGeographyFromGeometry
MakeValidGeographyFromGeometry
MakeValidGeographyFromText

For this exercise, I'm going to use the Zillow Neighborhood Boundaries . Since the neighborhood data (polygons) are delivered as shapefiles, I used the well respected Shape2SQL data loader from SharpGIS. Note that there are 42 individual state-based shapefiles which will need to be individually downloaded and then loaded into SQL Server. Since the goal of this exercise is to end up with geography features, the data must be in geographic coordinates to start with. The projection file (.prj) associated with each shapefile confirmed this, containing the following information:

GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

This is EPSG:4269, so that's the SRID I used in Shape2SQL.

My goal is to load this data into a column of type geography so it would make sense to load the data directly into a geography column. I'll save you the suspense and tell you right now that that some of this data will not load directly into a geography column - big surprise, right? Consequently, I loaded each shapefile's spatial data directly into a table, ZillowNeighborhoods, with a geometry column named GEOM. After the data loading was completed, I added a column of type geography named GEOG:

ALTER TABLE ZillowNeighborhoods
ADD GEOG geography NOT NULL

Being bold, I next attempted to use the new MakeValidGeographyFromGeometry function to update the new geography column:

UPDATE ZillowNeighborhoods
SET GEOG = dbo.MakeValidGeographyFromGeometry(GEOM)

Well, suffice-it-to-say, it wasn't quite that easy, even with our new fangled function, which returned the following error message:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "MakeValidGeographyFromGeometry":
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.

So, what to do next? Why not use the IsValidGeographyFromGeometry function so identify the candidate group of which geometries contained invalid data from a geography-perspective:

SELECT ID FROM ZillowNeighborhoods WHERE dbo.IsValidGeographyFromGeometry(GEOM) = 0
--Results:
--3499
--4319
--4328
--4428
--4458
--5182
--5201
--5214
--5224

Now I needed to figure out which of these geometries could not be converted, so I used the following query, once for each ID:

SELECT dbo.MakeValidGeographyFromGeometry(GEOM) FROM ZillowNeighborhoods WHERE ID = 3499

The queries which failed allowed me to isolate the problem geometries to the following rows (identified by the ID field):

3499, 4328, 5224

Before I go any further, let's update the geography column, sans the problematic polygons:

UPDATE ZillowNeighborhoods
SET GEOG = dbo.MakeValidGeographyFromGeometry(GEOM)
WHERE ID NOT IN (3499, 4328, 5224)

Now, let's investigate the problem geometries to see if we can determine what the issues are that prevent conversion. Here is the first troublesome geometry (ID = 3499):

SELECT GEOM FROM ZillowNeighborhoods WHERE ID = 3499

Here is the returned geometry in the Spatial results tab in Management Studio:

A careful visual analysis didn't find any obvious defects in the geometry.

Then, I decided to fiddle with the original geometry using the Reduce method with very small tolerances. Interestingly enough, I was able to get the geometry to convert to a geography using the following query:

SELECT dbo.MakeValidGeographyFromGeometry(GEOM.Reduce(.0000001))
FROM ZillowNeighborhoods WHERE ID = 3499

So what is happening here? First of all, the tolerance used in the Reduce method is very small. So how many vertex points were removed from the original geometry by Reduce? It turns out that 6 vertices were removed. A quick discussion with the SQL Server spatial developers revealed that during current validation processing some very close vertices can shift slightly and cause edges to cross/overlap (remember that geographic edges are not straight lines...). In the next release of SQL Server (SQL11), we have changed the underlying methodology and eliminated this issue. For now, we will have to workaround this issue using Reduce with very small tolerances.

By chance, I happened to look at the comments on my August 2008 post, referenced above, and found that I was not the first one to discover this technique:

-----------------------------------------------------------------------

# Craig Tadlock said on February 16, 2009 9:36 PM:
This is an excellent post. It works for 99.9% of the geometries Ive had to deal with. For the other .1%, try adding this..

Reduce the geometry value by a very small amount 1st. I'm not actually sure what this does internally, but it's solved most my issues.

Example...

select geography::STGeomFromWKB(geom.MakeValid().Reduce(.00000001).STUnion(geom.STStartPoint()).MakeValid().STAsBinary(), 4326) from tl_2008_us_csa

-----------------------------------------------------------------------

So, my hat's off to you, Craig, a true invalid geography pioneer!

-----------------------------------------------------------------------

NOTE: I've subsequently done a little more investigation and found that Alastair (author of the first published SQL Server Spatial book, Beginning Spatial with SQL Server 2008) had already figured this trick out in his excellent post, Fixing Invalid Geography Data (Oct. 18, 2008). Nothing like "discovering" what everybody else already knew ;-)

-----------------------------------------------------------------------

To complete the investigation, let's see if we can isolate the vertexes which were removed by Reduce. To do this I buffered the difference polygons between the original and generalized polygons using the following query:

--Find problem areas and mark with a buffer polygon
DECLARE @g0 GEOMETRY
DECLARE @g1 GEOMETRY

SELECT @g1 = GEOM.Reduce(.00000001) FROM ZillowNeighborhoods WHERE ID = 3499
SELECT @g0 = GEOM FROM ZillowNeighborhoods WHERE ID = 3499

SELECT @g0.STDifference(@g1).STBuffer(.001)

Using Safe Software's Feature Manipulation Engine's (FME) Universal Viewer, I've created the following visualization where I’ve overlaid the original and generalized geometry over each other with the modified areas indicated by the buffer regions generated with the above query. I then marked the obvious vertexes which were removed by the Reduce in the Windows Paint program. The remaining vertexes where so close to other vertexes that it was not possible to actually identify them, though it is clear where they are.

Needless-to-say, the generalized polygon with this tiny tolerance did not materially change the polygon's basic geometric quality. So I would posit that this technique is a valid method. To complete this sequence, I updated the row where ID=3499 with the following query:

UPDATE ZillowNeighborhoods
SET GEOG = dbo.MakeValidGeographyFromGeometry(GEOM.Reduce(.00000001))
WHERE ID = 3499

So what about the other two polygons which would not convert (ID's: 4328, 5224)? Well it turns out the "Reduce" technique works for these too. But there is a twist ... it wouldn't be any fun if I was done, right?

It turns out that when run through the Reduce-based technique, each of these geometries produces GeometryCollections:

-----------------------------------------------------------------------

NOTE: The SQL Server Spatial Team has entertained many inquiries on why Reduce, under certain circumstances, would take higher dimension objects (such as polygons) and create lower dimension objects (such as lines and points) from them (most other spatial databases maintain the dimensionality of the spatial object when generalizing). The following examples are a good illustration of how to capitalize on the behavior of Reduce when it encounters extremely thin polygons.

-----------------------------------------------------------------------

Here is the Well-Known Text (WKT) for ID = 5224 from the following query:

SELECT GEOM.Reduce(.0000001).STAsText() FROM ZillowNeighborhoods WHERE ID = 5224

GEOMETRYCOLLECTION (LINESTRING (-86.7028556317091 36.198983579874039, -86.702864557504654 36.198975399136543), LINESTRING (-86.72271192073822 36.195485174655914, -86.7227138876915 36.195484921336174), LINESTRING (-86.725671902298927 36.1651765704155, -86.7256792485714 36.165166437625885), POLYGON ((-86.724177449941635 36.164932250976562, -86.72216959297657 36.165002837777138, -86.720163896679878 36.165325194597244, ... , -86.725447744131088 36.164976820349693, -86.724177449941635 36.164932250976562)))

So what's with the LINESTRING's? Let's take a look.

First, let's create geometry instances from each of the LINESTRINGs. Since the LINESTRINGs are very small, let's buffer them to help make the area where there occur along the polygon visible:

DECLARE @g1 VARCHAR(MAX) = 'LINESTRING (-86.7028556317091 36.198983579874046, -86.702864557504654 36.19897539913655)'
DECLARE @g2 VARCHAR(MAX) = 'LINESTRING (-86.72271192073822 36.195485174655914, -86.7227138876915 36.195484921336167)'
DECLARE @g3 VARCHAR(MAX) = 'LINESTRING (-86.725671902298942 36.1651765704155, -86.7256792485714 36.165166437625885)'

SELECT GEOMETRY::STGeomFromText(@g1,4269).STBuffer(.001)
UNION ALL
SELECT GEOMETRY::STGeomFromText(@g2,4269).STBuffer(.001)
UNION ALL
SELECT GEOMETRY::STGeomFromText(@g3,4269).STBuffer(.001)
UNION ALL
SELECT GEOM FROM ZillowNeighborhoods WHERE ID = 5224

Here is the view from Management Studio:

Let's "zoom" in on a single problem area:

-----------------------------------------------------------------------
-- Isolate one of the problem areas which is identified by the linestring ...
-----------------------------------------------------------------------
-- Define linestring
DECLARE @g1 VARCHAR(MAX) = 'LINESTRING (-86.7028556317091 36.198983579874046, -86.702864557504654 36.19897539913655)'
-- Clip the original geometry instance by intersecting a buffer around the linestring with the original geometry instance
SELECT GEOMETRY::STGeomFromText(@g1,4269).STBuffer(.0001).STIntersection(GEOM)
FROM ZillowNeighborhoods WHERE ID = 5224
UNION ALL
SELECT GEOMETRY::STGeomFromText(@g1,4269).STBuffer(.00001)

Here is how this looks in Management Studio:

So the LINESTRING is a "spike". The "line" in the brown buffer region is actually an extremely thin extension of the base polygon. It turns out that the rest of the LINESTRINGs are also spikes. The last geometry (ID = 4328) was likewise festooned with spikes. A quick recap on why spikes cause validation issues, from above: "...during current validation processing some very close vertices can shift slightly to cause edges to cross/overlap".

To create the final geometry objects, ready for conversion to geography, simply extract the POLYGON WKT from the GEOMETRYCOLLECTIONS returned by the Reduce-based processing and create geometry instances which can then be used to update the original geometry after conversion by MakeValidGeographyFromText (note the use this new function):

DECLARE @geom VARCHAR(MAX) = POLYGON ((-86.724177449941635 36.164932250976562, -86.72216959297657 36.165002837777138, ..., -86.725447744131088 36.164976820349693, -86.724177449941635 36.164932250976562))'

UPDATE ZillowNeighborhoods
SET GEOG = dbo.MakeValidGeographyFromText(@geom,4269)
WHERE ID = 5224

Let's fix the last neighborhood polygon, ID=4328. A quick check shows that the core neighborhood polygon is actually a MULTIPOLYGON:

SELECT * FROM ZillowNeighborhoods WHERe ID = 4328

A quick check of the structure of the MULTIPOLYGON with the following query...

SELECT dbo.MakeValidGeographyFromGeometry(GEOM.Reduce(.00000001)).STAsText()
FROM ZillowNeighborhoods
WHERE ID = 4328

...reveals that there are 4 polygons (expected) and 1 line (a "spike") in the GEOMETRYCOLLECTION. I removed the LINESTRING from the WKT and, using the following query, updated the row:

DECLARE @geom VARCHAR(MAX) = 'GEOMETRYCOLLECTION (POLYGON ((-73.8411675542593 40.795716315507896, ..., -73.8411675542593 40.795716315507896)), POLYGON ((-73.853304713964462 40.7884576357901, ..., -73.853304713964462 40.7884576357901)), POLYGON ((-73.85657873749733 40.78686719387769, ..., -73.85657873749733 40.78686719387769)), POLYGON ((-73.85907343775034 40.785239797085531, ..., -73.85907343775034 40.785239797085531)))'
DECLARE @geog GEOGRAPHY
SELECT @geog = dbo.MakeValidGeographyFromText(@geom,4269)
UPDATE ZillowNeighborhoods
SET GEOG = @geog
WHERE ID = 4328

This completes the exercise, updating the ZillowNeighborhoods table with a column of type geography.

Technorati Tags: SQL Server,2008,Spatial,MakeValidGeographyFromGeometry,MakeValidGeographyFromText,IsValidGeographyFromGeometry,Shape2SQL,SharpGIS,CodePlex,Geography,Validation,Zillow,Neighborhoods,Safe Software,Feature Manipulation Engine,FME,Universal Viewer

Update to the SQL Server Spatial Tools project on CodePlex

2009-06-05T20:15:55Z

As Isaac noted in his most recent post, the SQL Server Spatial Tools project on CodePlex has been updated with several new geography functions. This post provides additional detail on the new features:

IsValidGeographyFromGeometry
IsValidGeographyFromText
MakeValidGeographyFromGeometry
MakeValidGeographyFromText
ConvexHullGeography
ConvexHullFromText

Here are descriptions for each:

bool IsValidGeographyFromGeometry(SqlGeometry geometry)
Check if an input geometry can represent a valid geography without throwing an exception. This function requires that the geometry be in longitude/latitude coordinates and that those coordinates are in correct order in the geometry instance (i.e. latitude/longitude not longitude/latitude). This function will return false (0) if the input geometry is not in the correct latitude/longitude format, including a valid geography SRID.

bool IsValidGeographyFromText(string inputWKT, int srid)
Check if an input WKT can represent a valid geography. This function requires that the WTK coordinate values are longitude/latitude values, in that order and that a valid geography SRID value is supplied. This function will not throw an exception even in edge conditions (i.e. longitude/latitude coordinates are reversed to latitude/longitude).

SqlGeography MakeValidGeographyFromGeometry(SqlGeometry geometry)
Convert an input geometry instance to a valid geography instance. This function requires that the WKT coordinate values are longitude/latitude values, in that order and that a valid geography SRID value is supplied.

SqlGeography MakeValidGeographyFromText(string inputWKT, int srid)
Convert an input WKT to a valid geography instance. This function requires that the WKT coordinate values are longitude/latitude values, in that order and that a valid geography SRID value is supplied.

SqlGeography ConvexHullGeography(SqlGeography geography)
Computes ConvexHull of input geography and returns a polygon (unless all input points are collinear).

SqlGeography ConvexHullGeographyFromText(string inputWKT, int srid)
Computes ConvexHull of input WKT and returns a polygon (unless all input points are collinear). This function does not require its input to be a valid geography. This function does require that the WKT coordinate values are longitude/latitude values, in that order and that a valid geography SRID value is supplied.

Technorati Tags: SQL Server,2008,Spatial,ConvexHull,MakeValid,IsValid,Geography,functions

"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 5

2009-05-19T01:27:55Z

The Report Builder, a component of Reporting Services, described in this post will be available as a separate download when the upcoming CTP2 release of SQL Server 2008 Release 2 (R2) is made available to the public, currently scheduled for August 2009.

This was to be the last post in this series but it's too interesting to stop here. As of this writing, I'm planning one additional post in this series which will expand on the new mapping capabilities of SQL Server Reporting Services. Please note that these new capabilities will be introduced in CTP2 of the upcoming SQL Server 2008, R2 release. Today's post will introduce the new SQL Server Report Builder and its new mapping capabilities. Here is the workflow presented so far:

Locate data source and download (Part 1)
Load the data into SQL Server (Part 1)
Remove unwanted features, simplify Alaska and Hawaii spatial features (Part 2)
"Reposition" Alaska and Hawaii cartographically (Part 3)
Locate and load State-based population data (Part 4)
Use the data in the new SQL Server Report Builder
More SQL Server Report Builder

So that you don't have to immediately scroll to the end of this post to see the final result, here is a reduced scale version of the final map report, noting, of course, the repositioned Alaska and Hawaii features ;-):

For those of you interested in the Report Builder workflow to create this map, please keep reading.

When the new Report Builder is first opened, a design canvas is presented, allowing you to select on of three report wizards: Table, Chart or Map. Since we have just finished adjusting a set of spatial data, the Map wizard looks like the ticket:

When Map is selected, you will be presented with a wizard which will take your through the creation of your first map report.

The first wizard menu presents you with a selection of spatial data sources. For this post we are going to use SQL Server spatial data:

The next step is to create a new data source connection:

Selecting "New..." brings up the next screen:

We have the option to create a new data source name but for this exercise, we won't change the default name. Selecting the "Build..." button displays the Connection Properties menu:

Here we will define our database server instance, authenticate the connection and connect to a database:

Hitting "OK" brings up the next panel. Since this looks good, we'll hit "OK":

This looks good, so we'll hit "Next >":

The query designer is now presented. We'll select "Edit As Text":

Here is the basic query we intend to use:

SELECT STATEFP, GEOM FROM US_STATES_INSET

This initial query selected columns STATEFP and GEOM with the goal of using the State FIPS Codes in the STATEFP column as the join column with the US_STATES_POPULATION table. The join column in the US_STATES_POPULATION table is the STATES column, also containing FIPS Codes for the States. A quick review of the two join columns revealed a basic incompatibility despite the fact that they were both defined as character columns:

STATEFP	STATES	NAME
01	1	Alabama
02	2	Alaska
04	4	Arizona
05	5	Arkansas
06	6	California
08	8	Colorado
09	9	Connecticut

to make the two join columns compatible, we can cast the character columns to integer. Here is the rewrite for the current query:

SELECT CAST(STATEFP AS INT) AS STATEFP, GEOM FROM US_STATES_INSET

We will use this query in the query designer. Hitting "!" button will execute this query, with the results displayed in the bottom window, below:

Selecting the "Next >" button brings up the following menu:

This all looks good, so we will select "Next >" here also and bring up the visualization menu, where we will choose "Color Analytical Map":

Choosing "Next >" brings up the "Choose the analytical dataset" menu. Our analytical data will come from the US_STATES_POPULATION table, se we nee to select the "Create a dataset" radio button:

Choosing "Next >" brings up the menu panel which allows us to choose a connection to the new dataset. Since the US_STATES_POPULATION table is in the same database as the spatial table (US_STATES_INSET), we can use the existing connection, DataSource1:

Choosing "Next >" bring us to the query designer screen for the analytical data:

As with the query designer for the spatial data, we well choose "Edit As Text" and use the following query:

SELECT CAST(STATE AS INT) AS STATE, POPESTIMATE2008 FROM US_STATES_POPULATION

Our intent is to join the two tables and display the 2008 population estimates (POPESTIMATE2008 column) for each state on the resulting map. As will recall, we are casting the State FIPS Code to integer to guarantee join compatibility on the join columns.

After selecting "Next >", we are offered the opportunity to specify the join columns:

Checking the "Key" box allows us to specify the analytical fields in the US_STATES_POPULATION table query we just specified - in this case Map Binding Field: STATEFP, Analytical Field: STATE:

Select "Next >" to bring up the color theme and data visualization menu. Here we select the data field (analytical field) which will be symbolized by different polygon fill colors based on, in this case, population:

Selecting "Finish >>" brings up the design canvas. In the version of the Report Builder that is being used for this post, there is a bug which prevents the map display from rendering the underlying spatial data correctly. In order to remedy this, we need to set the coordinate system for the map display (It currently defaults to the geography data type. Since the GEOM column in the US_STATES_INSET table is of type geometry, the design-time display is not correct):

To set the correct coordinate system, left-mouse-click on the design canvas to select and then right-click with the mouse to bring up the contextual menu. What we are looking for are the "Viewport Properties..."

Select the "Viewport Properties..." allows us to set the Map coordinate system to Planar (X,Y). In future versions of the Report Builder, the coordinate system will be automatically detected.

Selecting "OK" completes the task and the design canvas correctly illustrates the United States inset map. Note that the map is not yet rendered and the design canvas map display is simply a placeholder.

In the design canvas, we can adjust the map a bit, moving the color scale bar to the right and adding a title. When done, we can render the final map by choosing "Run" from the main menu ribbon of the Report Builder:

When rendering is completed, the following report is produced:

In the next post, Part 6, we will explore some additional capabilities of the Report Builder to finish off the series.

Technorati Tags: SQL Server 2008,SQL Server 2008 R2,CTP-2,CTP2,Reporting Services,Report Builder,SSRS,Map,Spatial,SQL Server Reporting Services

"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 4

2009-05-18T07:53:36Z

In the previous posts I indicated that this would be the last post in this series but it turned out that I was a bit optimistic. In order to make the final Report Builder post relevant, some interesting data for analysis was needed. In this post we describe where to find and how to load State-based population data - our "interesting data".

Locate data source and download (Part 1)
Load the data into SQL Server (Part 1)
Remove unwanted features, simplify Alaska and Hawaii spatial features (Part 2)
"Reposition" Alaska and Hawaii cartographically (Part 3)
Locate and load State-based population data
Use the data in the new SQL Server Report Builder

LOCATING STATE POPULATION DATA

Our search for population data by State was satisfied at the US Census Bureau Population Estimates site. There are a number of data files which are available, but I chose Population, Population change and estimated components of population change: April 1, 2000 to July 1, 2008 (NST-EST2008-alldata).

This data is delivered as commas-separated-value (CSV) ASCII. This data was opened in Excel and saved as Text (Tab delimited).

LOADING STATE POPULATION DATA

Now we are going to use FME to do something for which it is not well known - loading data with no spatial component.

ADDING THE STATE POPULATION DATA AS THE SOURCE DATASET

In FME-speak, all ASCII data is format "Comma Separated Value (CSV)". Consequently, we choose CSV as our format - never mind that our data is tab separated values. Next we locate the data using the "..." menu button. After both the Format: and the Dataset: text fields are populated, choose the "Settings..." button.

The "Settings..." button brings up a new menu panel which allows us to correctly define our field separator as "tab". Since our ASCII file contains field names, we want to make sure that this record is not interpreted as data. In fact, we will use the field names as the columns names in the final database table. The CSV File Preview provides visual verification that we are correctly interpreting the file structure.

The source data now appears on the workbench canvas:

ADDING THE DESTINATION DATASET

The destination dataset will be our new database table which we will name us_states_population. Here is the first menu panel that appears when we add a new Destination Dataset:

Our first task is to choose the correct output format. We have two potential choices for SQL Server. In this case, since the new database table will not have a spatial column, we want the Microsoft SQL Server Non-spatial (MSSQL_ADO) format:

Note: If we chose the Microsoft SQL Server Spatial (MSSQL_SPATIAL) format, the data load would work correctly but would have a spatial column defined in the output table, populated with NULL values.

Next, we want to select the "Settings..." button. This allows us to define our server instance, dataset and authentication. The use of the term "Dataset" is a bit confusing for database users. What FME is asking for, in this case, is the database name (in our case, temp_spatial):

With the destination defined, hit "OK" to continue...

FME next asks if you would like to add a new feature type to the dataset. This can be interpreted as "Would you like to add a new table to the database?", so we need to answer Yes:

The Feature Type Properties (a.k.a. Table Properties) panel asks for the Feature Type Name. We supply the name of the database table to hold the population data: us_states_population

We hit OK at this point and the destination end point appears on the workbench canvas:

Next, we connect the source and the destination and the upper connection triangles turn green, indicating success. Now, right click on the destination data and choose "Copy Attributes From Feature Type...":

The Copy Attributes menu panel will now display. Choose, "Copy Attributes From:" the only option will be the correction option, in this case:

The workbench canvas now appears as follows. It is unclear why FME choose to rename the STATE column but it did (to STATE00). This causes the implicit connections between source and destination columns to be undefined (yellow connection triangle on source and red connection triangle on the destination).

To associate the STATE (source) and STATE00 (destination) columns, drag a line between the two:

The final task is to check the data types and definitions associated with each column. Here are the original definitions:

The SUMLEV and STATE00 columns are both defined as float. While we are not interested in the SUMLEV column, the STATE00 column needs to be defined as char 2 for compatibility with the STATEFP column in the us_states table, to which we will join this table later on:

LOAD THE STATES POPULATION DATA

Execute the FME workspace to load the data into a SQL Server table, us_states_population. The last step is to rename the STATE00 to STATE in Management Studio.

We are now ready to create our first map report in the Report Builder. This will be described in Part 5.

Technorati Tags: Non-spatial,data,population,Census,loading,Safe Software,FME 2010 Beta,FME,Feature Manipulation Engine,SQL Server,2008,non-spatial

"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 3

2009-05-16T17:04:54Z

In the previous posts we have covered items 1,2 and 3 of the workflow. In this post we address item 4.

Locate data source and download (Part 1)
Load the data into SQL Server (Part 1)
Remove unwanted features, simplify Alaska and Hawaii spatial features (Part 2)
"Reposition" Alaska and Hawaii cartographically
Use the data in the new SQL Server Report Builder

SQL SERVER SPATIAL TOOLS ON CODEPLEX

Now that we have our spatial data suitably prepared, we need to move the Alaska and Hawaii spatial features to new locations. In order to do this, we need the help of a new function which we have published on the SQL Server Spatial Tools site at CodePlex.com. This function, AffineTransform, is one of several new functions, aggregates and data types which we have made available with source code. It is beyond the scope of this post to describe SQL Server Spatial Tools, but if you are not familiar with it, you should take a look.

REGISTERING THE SQL SPATIAL TOOLS ASSEMBLY

SQL Spatial Tools is delivered as an assembly (SQLSpatialTools.dll) which must be registered on the database you wish to use. The registration process is delivered as a register.sql file which needs two modifications (in red) before it is run:

-- Insert your database
use [Your_Database]
go

-- Enable CLR
sp_configure 'clr enabled', 1
reconfigure
go

-- Insert the path to the SQLSpatialTools assesmbly here
create assembly SQLSpatialTools from 'C:\Apps\SQLSpatialTools.dll'
go

...

REPOSITIONING ALASKA AND HAWAII, PASS 1

With SQL Server Spatial Tools registered to your database, the AffineTransform function is now available for use in T-SQL. The AffineTransform is a static function with 3 parameters: Translate, Scale and Rotate. Setting the initial values for these parameters, especially scale and rotate is based on an educated guess. The Scale and Translate parameters are addressable via independent X and Y component values. The Rotate parameter has a single value, with positive numbers rotating the object in a counter-clockwise direction, negative numbers in a clockwise direction. Displaying the grid lines (Show grid lines in Management Studio) can be used to assist in setting the X and Y Translate values.

Here is the T-SQL for the first pass at repositioning Alaska and Hawaii:

USE [Your_Database]
GO

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset2 WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -2500000).Apply(@a)
SET @a = AffineTransform::Scale(.75, .75).Apply(@a)
SET @a = AffineTransform::Rotate(15).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset2 WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(2500000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(2, 2).Apply(@h)
SET @h = AffineTransform::Rotate(15).Apply(@h)

----------------------------------------------------------------
-- Display Original and Repositioned Features
----------------------------------------------------------------
SELECT @a -- Repositioned Alaska
UNION ALL
SELECT @h -- Repositioned Hawaii
UNION ALL
SELECT GEOM FROM us_states_inset2 -- Original Feature Positions
GO

The red arrows show the displacement of the Alaska and Hawaii features, after the first pass, below:

REPOSITIONING ALASKA AND HAWAII, PASS "n"

I'd like to say that I was able to achieve final positioning of Alaska and Hawaii in 2 passes but it did take a few more iterations. Here is the T-SQL for my final pass, achieving the desired positioning of Alaska and Hawaii:

USE [Your_Database]
GO

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -4500000).Apply(@a)
SET @a = AffineTransform::Scale(.5, .5).Apply(@a)
SET @a = AffineTransform::Rotate(27).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(5000000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(1.5, 1.5).Apply(@h)
SET @h = AffineTransform::Rotate(30).Apply(@h)

The original location and the final repositioned location of Alaska and Hawaii are shown below:

UPDATE FINAL POSITIONS IN THE TABLE

With Alaska and Hawaii in final position, the last step is to update the geometry columns (GEOM) for the Alaska and Hawaii rows with the new geometry values:

----------------------------------------------------------------
-- Reposition Alaska
----------------------------------------------------------------
DECLARE @a GEOMETRY
SELECT @a = GEOM FROM us_states_inset WHERE STUSPS = 'AK'
SET @a = AffineTransform::Translate(-1500000, -4500000).Apply(@a)
SET @a = AffineTransform::Scale(.5, .5).Apply(@a)
SET @a = AffineTransform::Rotate(27).Apply(@a)

----------------------------------------------------------------
-- Reposition Hawaii
----------------------------------------------------------------
DECLARE @h GEOMETRY
SELECT @h = GEOM FROM us_states_inset WHERE STUSPS = 'HI'
SET @h = AffineTransform::Translate(5000000, -400000).Apply(@h)
SET @h = AffineTransform::Scale(1.5, 1.5).Apply(@h)
SET @h = AffineTransform::Rotate(30).Apply(@h)

----------------------------------------------------------------
-- Update Alaska Geometry Instance With New Position
----------------------------------------------------------------
UPDATE us_states_inset
SET GEOM = @a
WHERE STUSPS = 'AK'

----------------------------------------------------------------
-- Update Hawaii Geometry Instance With New Position
----------------------------------------------------------------
UPDATE us_states_inset
SET GEOM = @h
WHERE STUSPS = 'HI'

----------------------------------------------------------------
-- Display Table
----------------------------------------------------------------
SELECT * FROM us_states_inset

And there you have it - Alaska and Hawaii neatly tucked under the southwest states, ready for use in the new mapping function in SQL Server Reporting Services.

Technorati Tags: SQL Server,2008,Spatial,CodePlex,SQL Sever Spatial Tools,Affine,Transformation,Transform

"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 1

2009-05-10T07:35:13Z

I was recently presented with the problem of taking a US States dataset, in geographic coordinates, and creating a database table whose spatial column would render in a similar fashion to the map, below, with the Alaska and Hawaii insets.

The ultimate goal was to use the resulting table in the new map control of the upcoming Report Builder in SQL Server Reporting Services (SSRS).

APPROACH

To illustrate how do this, I choose to use Safe Software's Feature Manipulation Engine (FME 2010 Beta) and SQL Server 2008. Safe Software aficionados will point out that this could have been done completely in FME. Likewise, this also could have been done completely in SQL Server 2008, though it would have been much more difficult. The use of both tools to solve this problem represents a reasonable real-world approach.

WORKFLOW

Here is the basic workflow:

Locate data source and download
Load the data into SQL Server
Remove unwanted features, simplify Alaska and Hawaii spatial features
"Reposition" Alaska and Hawaii cartographically
Use the data in the new SQL Server Report Builder

In this post we will cover workflow items 1 and 2. In subsequent post we will address the other workflow items.

DATA SOURCE

The original data came from the US Census Bureau's collection of geographic data:

http://www2.census.gov/geo/tiger/TIGER2008/

Specifically, the data was the Tiger Line 2008 US State ShapeFile (tl_2008_us_state) found at:

http://www2.census.gov/geo/tiger/TIGER2008/tl_2008_us_state.zip

Here is how the input Shapefile appears in the FME Universal Viewer:

DATA LOADING

Data loading was accomplished using the FME Workbench, using the following procedure:

Identify the tl_2008_us_state.shp Shapefile as the input data source
Project the spatial data into an Albers Equal Area projection suitable for the conterminous United States
Writing the data to a SQL Server 2008 table using the geometry data type

Add a data source (Source Data), specifying the format as ESRI Shape, the dataset as tl_2008_us_state.shp and the coordinate system as EPSG: 4269 (NAD 83).

For visual appeal, an Albers Equal Area projection is chosen using the ESRIReprojector transformer. I wanted to use the Reprojector transformer but, to date, have not figured out how to locate an existing projection for the North America (-96 central meridian, etc.).

No we get to the important part - SQL Server Spatial, our format for the Destination Dataset. Choose settings, next.

The setting menu panel allows you to setup the database connection and specify the output spatial column and name:

Selecting OK bring you back to the Add Destination Dataset where you can select OK to move on...

I always find this next part in FME a wee bit confusing, but before we get into that, answer "Yes" to this menu.

When FME asks, in a database context, if you would like to add a new feature type to the data set, what is really trying to say is, "what would like to name the new database table?". In this case our new table table (FME Feature Type Name) will be set to us_states.

Hit "OK" and we now can see the (almost) completed FME workspace. Note that the destination end point is not yet "connected" to the Shapefile attribute data. You can tell this by the yellow connector on the ESRIReprojector transformer and the fact that no attributes are list below destination endpoint:

Right-clicking on the destination endpoint brings up a contextual menu which provides a number of choices. You want the "Copy Attributes From Feature Type" option:

In this case, you have only one choice and it's the correct one (what a coincidence ;-):

The workflow is now complete and the data load sequence is ready to run:

To run the workflow, select the green triangle "run" button from the FME menu:

During the run process, FME writes out all actions to a log file and displays the log to the screen. In this case, the log noted the following:

MS SQL Server (Spatial) Writer: Failed to write SRID because the feature's coordinate system could not be converted to an EPSG number

Let's take a look at the new table, us_states, in SQL Server Management Studio, to see how the SRID value is treated. Here is how the spatial data displays using the "Spatial results" tab after a SELECT * FROM us_states query:

To check the SRID values of each geometry instance we can use the following query:

SELECT geom.STSrid AS SRID FROM us_states

From this we can ascertain that when FME cannot figure out the proper EPSG-based SRID to assign to geometry instances, it does nothing and allows SQL Server to use its default SRID value of 0.

Technorati Tags: SQL Server,2008,spatial,Safe Software,Feature Manipulation Engine,FME,Universal Viewer,data loading,Shapefile,shape file,SRID,EPSG

"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 2

2009-05-10T07:29:32Z

In the previous post we covered items 1 and 2 of the workflow. In this post, we will cover item 3:

Locate data source and download
Load the data into SQL Server
Remove unwanted features, simplify Alaska and Hawaii spatial features
"Reposition" Alaska and Hawaii cartographically
Use the data in the new SQL Server Report Builder

The challenge now is to remove unwanted spatial features which are not relevant to the Reporting Services map we want to create. Additionally, we will simplify the Alaska and Hawaii spatial features.

Let's define the features that we wish to complete remove:

American Samoa
Commonwealth of the Northern Mariana Islands
Virgin Islands of the United States
Guam
Puerto Rico

To remove these features, we will use standard T-SQL queries:

DELETE FROM us_states WHERE NAME = 'American Samoa'
DELETE FROM us_states WHERE NAME = 'Commonwealth of the Northern Mariana Islands'
DELETE FROM us_states WHERE NAME = 'Virgin Islands of the United States'
DELETE FROM us_states WHERE NAME = 'Guam'
DELETE FROM us_states WHERE NAME = 'Puerto Rico'
GO

Now that we are done with the mundane, let's look at the interesting problem of taking the Alaska and Hawaii spatial features and simplifying them by removing the of out lying islands in the Aleutian Islands and the Hawaiian archipelago. The image, below, shows the features we wish to remove:

This sounds simple enough until we realize that the Alaska and Hawaii spatial features are multipolygons and the features that we want to remove are part of the geometry instances. To make these features accessible, we have to separate the multipolygons into their individual polygon components. To do this we will use FME, both the Workbench and the Universal Viewer. Here is the workflow:

The data source is the us_states database table. An AttributeFilter transformer is specified to isolate the Hawaii multipolygon based on the STUSPS attribute:

The output of the AttributeFilter is then sent to a Deaggregator transformer to separate the Hawaii multipolygon into individual polygons. The output from the Deaggregator transformer is sent to a Visualizer, below:

In the Visualizer, we can identify each unique polygon which makes up the Hawaii spatial feature using the FME attribute, _part_number. Using this approach, we determined that we want to keep _part_numbers 1,2,3,4,6 and 7.

The Alaska problem is the reverse since there are so many parts to the multipolygon. In this case we will identify the polygons which we want to remove: _part_numbers 22, 18, 37, 29, 6, 12, 27, 31, 10, 48, 47, 13, 28, 9, 17, 3, 34, 39 and 44.

This workflow takes the separated (disaggregated) Hawaii and Alaska multipolygons and applies attribute filter transformers to identify specific component polygon elements. For the Hawaii sub-flow, the new RangeFilter transformer (FME 2010 beta) is used since polygon parts could be easily identified by two ranges. For the Alaska sub-flow, the polygon components to be removed are listed in the AttributeFilter transformer. The <UNFILTERED> channel is then chosen, returning the remaining Alaska polygon components. For both sub-flows, a final Aggregator transformer is used to reassemble the polygon components into multipolygon instances. All 3 sub-flows (Alaska, Hawaii and <UNFILTERED>) from the first AttributeFilter are channeled to the output database table, us_states_inset. This new table with the simplified Alaska and Hawaii spatial features is illustrated below from SQL Server Management Studio:

With the spatial features suitably simplified, we are now ready for the next step, "repositioning" the Alaska and Hawaii spatial features as insets. This will be described in Part 3.

Technorati Tags: SQL Server,2008,Spatial,Feature Manipulation Engine,FME,AttributeFilter,RangeFilter,Disaggregator,Aggregator,Transformer,FME 2010 Beta