SQL 2008 Spatial Samples, Part 1 of 9 - How to Learn SQL Spatial

Article
11/01/2008

This post covers how to get started with SQL Spatial methods & the best way to test things for yourself.

One of the goals in this series of articles is to give you code you can cut, paste & try. I hope you find this handy.

Overview

Visual Studio is better than SSMS because:

One of the best ways to learn is to play with the product. I've found that the Intellisense support in Visual Studio (VS) is much more mature than that provided in SQL Server Management Studio (SSMS). This is especially true for the SQL Spatial Objects, where the TSQL people don't get offered a drop down list of all the valid methods (spelt & cased correctly) nor does it offer the parameters, their data types etc.

So I'll describe how a DBA (non-Programmer) can still take advantage of VS to write TSQL queries.

SSMS is better than Visual Studio because:

But the reverse is also true. SSMS includes a Spatial Results Tab, which makes it really easy to see what you are doing with the spatial data. Much easier than in Visual Studio where you need to write your own bug free rendering routines, or use some 3rd party map control.

So use both:

Thus both tools compliment each other really well.

How to do I get get SQL Spatial?

Option 1: It is included with SQL Server 2008 (all editions)
If you've installed any edition of SQL Server 2008, you have everything you need. Clearly SQL Server 2008 Developer Edition is the simplest, but you can download SQL Server 2008 Express & it works too.

Option 2: Download the libraries & call them within your Visual Studio code. (or whatever development environment you prefer to write .Net managed code)

What if I don't have any software?

You can get it free

If you've installed any edition of SQL Server 2008, you have everything you need. But interestingly you don't need to have a copy of SQL Server in order to use the spatial capabilities. It is also made available as a separately installed library. It is part of the Microsoft SQL Server System CLR Types which are free to download & use. They are found on the Microsoft SQL Server 2008 Feature Pack, October 2008. (Note: This is refreshed every 3-4 months, with new capably, utilities & interoperability tools. So if October 2008 was a while ago I suggest you look for the latest drop of the SQL Server feature pack. Also worth knowing that not everything in the Feature Pack is free, many items require a licensed version of SQL Server in order to be useful. Or at least a SQL Client Access License (CAL) to connect to a server.)

So it is possible to install just the System CLR Types & use the spatial data types & methods within objects in your program. You do not need any edition of SQL Server, just keep everything in memory. Perhaps that will be sufficient, for your use. You could even store them in a file as Binary, GML or Text if you wanted.

But if you do need to persist them to disk I'd suggest at least use the Free edition of SQL, SQL Server 2008 Express. Not only does SQL prove to be a better way to store spatial information, it adds Spatial Indexes. These are a key reason for SQL being blindingly fast compared to other relational databases's spatial offerings. see Boston Geographic Information Systems's Cross Compare SQL Server 2008 Spatial RC0, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6 Note: This paper was written prior to the release of SQL. While they are complementary about it in their feature comparison, they under-state its features. Unfortunately they assumed (incorrectly) that no additional capability would be available in the released product. But still they do mention it is fast. Make sure you create indexes on your spatial data, if you plan on using any of the Spatial "conditional" methods in your code ie: STDisjoint().

UPDATE: Further research shows that the "Spatial Results tab" feature didn't make it into the SQL Express SSMS tool. The Express Tools are a Web download which was not updated when we shipped SQL Server 2008 Express . The download works for both SQL 2005 & SQL 2008. I'm told that we plan to add this feature when we refresh the tools. I don't have a date for that, nor has anyone told me it is definitely in. So for now, the Developer Edition is really the go. Sorry.

Don't have a copy of Visual Studio? You can get that a free version of that here Visual Studio Express.

Why would I buy it?

SQL Server 2008 Developer Edition is ~$100. Like Visual Studio Professional or Team Suite editions it includes a lot of other tools & features that will make you more productive, especially if you are producing a product that needs to scale. eg: SQL Profiler, Intellisense & similar features that let you detect & correct coding errors quickly.

Tip: Learn SQL Spatial Methods & their Parameters via Visual Studio Code environment

Even if not writing a program.
- Use VS2008 Intellisense to suggest syntax.
- Paste the case sensitive Method names into TSQL queries in SSMS.
Why?
- SSMS Intellisense is of limited value.
  - Mostly works but:-
    - Doesn’t suggest possible Method names.
    - Sometimes puts Red Underlines on valid syntax
- Books Online getting better but lacks pictures & has minimal code examples. Which is why I wrote this series of articles.

So while I was learning I had a blank Visual Studio project open behind SSMS. I'd tab to it, type in a period "." at the end of my Geography variable & see how to spell what I wanted & what its parameters are. Then I'd try it in SSMS to see what it looked like.
Note: Thankfully SSMS is very forgiving. It automatically uses MakeValid to display shapes that would normally cause a .NET exception. It displays the WKT string in the results tab. So sometimes poorly written queries &/or invalid data will cause an exception in your code. Yet SMSS manages to run them OK.

How to use SQL Spatial within your VB.NET or C# code.

1. Add a Reference to Microsoft.SqlServer.Types to your project.

2. Optionally add Imports (vb) or Using (c#) statement in your code.

Imports Microsoft.SqlServer.Types

3. Create Variables as needed. eg:
    Dim myGeom As SqlGeometry
   Dim myGeog As SqlGeography

   Dim myGmBuild As SqlGeometryBuilder
   Dim myGgBuild As SqlGeographyBuilder
4. Retrieve as a DataTable (see below)

5. Use Points, or Convert to WPF or GDI+ Lines/Shapes or some 3rd party control to display

Sample to return Geographic data type fields from SQL Server.

       Imports Microsoft.SqlServer.Types
        :

       Dim dt As New DataTable(strTableName)
      ' ===< Retrieve all PK or Unique Columns >=====
      Using connSQL As New SqlConnection(myConnectionString1)
          connSQL.Open()
          '--- Get Data from SQL ---
          Dim cmd As SqlCommand = New SqlCommand(SQLQueryExGeo, connSQL)
          cmd.Connection = connSQL
          Try
              Dim myReader As SqlDataReader = cmd.ExecuteReader
              dt.Load(myReader, LoadOption.OverwriteChanges)
          Catch ex As SqlException
              'TODO: Improve on this error handling
              MessageBox.Show(ex.Message)
          End Try
      End Using

As you can see there is nothing different to any other SQL Query. If your display control supports it, just databind it to the datatable. eg:

 '====< Bind Results to Map Control >======
' Set the data source.
MapControl1.DataSource = dt

or if you prefer, loop thru the rows in your table (dtGeo) & cast them to the appropriate data type.

' Load Geometry Shapes

For i As Integer= 0 To dt.Rows.Count - 1

    Dim geo As New SqlGeometry()

    geo = CType( dtGeo.Rows(i).Item(GeomIndex), SqlGeometry)

      :

Next

Tips: For writing TSQL Queries in SQL Server Management Studio

Declare Variables as Geometry or Geography
Initialise them with WKT values
- Note: New SQL 2008 syntax lets you Declare & "Set" in one statement.
Display them in one statement with UNION ALL. Why?
- This lets you see all the shapes in the one Map.
Use UNION ALL & not just UNION
- UNION implies Duplicates be removed eg: DISTINCT. This results in an error as DISTINCT isn’t currently supported with Geo Types. To explicitly compare 2 shapes you need to use the .STEquals() method.

 --===< Sample Code, just Paste into SQL Management Studio (SSMS) >===

 --< Create 2 Variables & see them in the Spatial Results Tab >--
DECLARE @g geometry = 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))';
DECLARE @h geometry = 'POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))';
SELECT @g
UNION ALL -- Must use the ALL keyword
SELECT @h

More info

For more info see SQL Server 2008 Books Online Geography Spatial Type & Geometry Spatial Type

Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

Technorati Tags: SQL Server Spatial,Spatial,TSQL,.NET

Comments

Anonymous
November 01, 2008
PingBack from http://www.tmao.info/sql-2008-spatial-samples-part-1-of-9-how-to-learn-sql-spatial/
Anonymous
November 02, 2008
Adding spatial capability to your app might be simpler than you think. This is not a comprehensive coverage
Anonymous
November 16, 2008
The comment has been removed
Anonymous
November 26, 2008
Speaker: Eric Nelson http://blogs.msdn.com/ericnel Location: NxtGenUG Southampton Thanks to all those
Anonymous
December 17, 2008
>>Further research shows that the "Spatial Results tab" feature didn't make it into the SQL Express tool... I hope you don't mind me plugging www.Geoquery2008.com as an option for anyone using the free/Express version of SQL 2008 and wanting to get some sort of visualization happening. Yes, it is still quite rough in places - it has been in beta-stasis for a while now - but expect some new features and bug fixes over the holiday break... Craig (ACP Magazines...)
Anonymous
December 24, 2008
where do i get all 9 labes on 'Dave's Spatial Data Series'?
Anonymous
February 19, 2009
hey Dave, thanks for the write-up. I've completed part 1... where do I find parts 2 - 9? thx, vic
Anonymous
February 19, 2009
Hi Vic, Look to the TAGS section (currently on the Right Side up the top). Then click on the word SPATIAL. Not only will it list all 9 articles in this tutorial, but it will also pull up a few other posts I hope you will find handy. Enjoy, Dave
Anonymous
December 08, 2010
Hi Dave, Thanks a lot for writing all the 9 parts of your spatial samples, this is a very useful resource to start with SQL Server 2008 ! Thx,

Partager via