SQL Saturday 49 Orlando

There were great attendees to the SQL Saturday presentation “Spatial Queries in SQL 2008”

First, here are the slide,database , SQL, and ASP.Net code in one zip file.

Download from SQLSaturday website

Here are some links mentioned during the presentation.

1) Geocoding addresses using Google See sample on Codeplex.com

2) BING maps SDK AJAX version

3) Bing maps SDK Silverlight version

4) Window Mobile 7 toolkit Download the Tools

Comments Off

SQL Saturday Tampa – Spatial Data

Thank you all for attending my session here are the files to download and get stated with spatial data with SQL Server 2008 and Virtual Earth.

Everything (PPT,ASP.NET,Database)

Just the ASP.Net code

Just database backup

Just the script to build the database

Just the PowerPoint

Bing Maps AJAX SDK

Bing Maps Silverlight SDK

Comments Off

See you at SQL Saturday in Tampa!

See you at the SQL Saturday event in Tampa.

Here is a map to the K Force building

K Force

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server.

This event will be held January 23rd, 2010 at K-Force, 1001 East Palm Avenue, Tampa, FL 33605.  K-Force has really stepped up.  After seeing the issues with the “box wall” and the “bowling alley”, they have secured additional rooms at a venue next door.  The Tampa Code Camp recently took advantage of the expanded facilities and I am hearing great things as a result.

Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.

Registration will open at 8:00 am.  The first session will start at 9:00 am and the last session will conclude at 5:00 pm.  At 5:15 the raffles will start and we should be out of there by 6:00 pm.

Tampa Code Camp 2009

As promised here are the slides from the presentation.

PowerPoint Slides

PDF version on the slides

Comments Off

Get Started using SQL 2008

Since it is 2009 it is about time to get up to speed with SQL 2008.  Here is the link to down load the fully functional express edition.

http://www.microsoft.com/express/sql/download/

SQL Saturday #21 Orlando – Spatial Data in SQL 2008

October 17, 2009

This session is …

a quick tour of the spatial features of SQL Server 2008

PowerPoint slides

PowerPoint as PDF

Tyler Chessman’s article in SQL Server Magazine’s December 2008 issue is my primary source of information to implement my spatial solutions. Visit http://www.sqlmag.com and search for instant doc ID 100528.

Here is a link to the article

What is Spatial Data

• Spatial data represents the shape and physical location of an object.
• The object can be a house, business, sub-division, or a county.
• SQL Server 2008 has two new data types GEOMETRY and GEOGRAPHY.
• GEOMETRY works with flat objects.
• GEOGRAPHY considers the shape of the earth.

CLR Types

• GEOGRAPHY and GEOMETRY are CLR data types.
• You do not need to have CLR enabled on the SQL Server instance.
• Microsoft has provided a long list of OGC methods.
• These are methods that are part of the independent Open Geospatial Consortium list of specifications

STDistance Function explained

• The STDistance function requires an argument of the SQLgeometrytype
• Convert the latitude and longitude to SQLgeometrytype.
• Declare a variable of the geography type.
• Set the variable to geography::STGeomFromText(‘point(lon lat)’,4326).
• Notice that the point is a string.
• Notice there is no comma between lat and lon
• 4326 is an SRID and is used to tell the function what method to use to calculate distance on a not-so-round planet.

STDistance in action

Declare @CodeGEOG geography =

geography::STGeomFromText(‘Point(-95.3410 29.7070)’,4326);

Select top(5) addr.AddressID as id ,

addr.addressline1,addr.city,

addr.SpatialLocation, — Will show the raw data

addr.SpatialLocation.AsGml() as SpationalGML,

addr.SpatialLocation.STAsText() as SpatialText,

addr.SpatialLocation.STGeometryType() as GeoType,

addr.SpatialLocation.STNumPoints() as Points,

addr.SpatialLocation.STDistance(@CodeGEOG) as distance_in_meters,

addr.SpatialLocation.STDistance(@CodeGEOG)/1609.344 as distance_in_miles

from dbo.Address addr

Where

addr.SpatialLocation.STDistance(@CodeGEOG)/1609.344 < 50

order by distance_in_miles

Wire the SQL up to Virtual Earth

The wire up will get it’s own blog entry

Geography

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
DROP TABLE dbo.SpatialTable; 
GO
CREATE TABLE SpatialTable 
( 
id int IDENTITY (1,1), 
GeogCol1 geography, 
GeogCol2 AS GeogCol1.STAsText() 
); 
GO 
INSERT INTO SpatialTable 
(GeogCol1)
VALUES 
(geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326)); 

INSERT INTO SpatialTable 
(GeogCol1) 
VALUES 
(geography::STGeomFromText('POLYGON((47.653 -122.358, 47.649 -122.348, 
47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326)); 
GO

MS SQL Server Management Studio 2008 connecting to 2005 hosted SQL

If you are using SSMS 2008 to connectr to a 2005 database you may get the error “Failed to retrieve data for this request (Microsoft.SqlServer.Management.sdk.sfc)”.   SQL 2008 has features that 2005 doesn’t have.  Some of those features are displayed as columns in the database browser. This will cause the error to occur.   This in turn will prevent you from being able to connect to your database. This is a known issue with Microsoft and occurs when either a database is in offline mode or when SQL Server Management Studio cannot correctly read one or more databases. Therefore, certain properties of a database cannot be retrieved. However there is a work around for this if you follow the instructions below:

1. Close the error message.
2. Press F7 to open the Object Explorer Details pane.
3. Right-click the column headers, and make sure that only the following columns are selected:
   *Name
   *Date Created
   *Policy Health
   *Owner
4. Right click the Databases node, and then click Refresh.

For more information the please view the Microsoft knowledge base.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;956179

SQLSaturday #15 Jacksonville

Spatial data represents the shape and physical location of an object.  For example, the object can be a house, business, sub-division, or a county.  SSQL Server 2008 has two new data types GEOMETRY and GEOGRAPHY.  GEOMETRY works with flat objects. GEOGRAPHY considers the shape of the earth. 

GEOGRAPHY and GEOMETRY or CLR data types. Though to implement them you do not need to have CLR enabled on the SQL Server instance.  Microsoft has provided a long list of OGC methods.  These are methods that are part of the independent  Open Geospatial Consortium list of specifications.

Here is a PDF of my presentation SQLSaturday.pdf (806.55 kb)