USE [SandBox]

GO

/****** Object:  Table [dbo].[Locations]        Script Date: 01/25/2011 20:52:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Locations](

    [LocationID] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](200) NULL,

    [LocationGeom] [geometry] NULL,

    [LocationGeog] [geography] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

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

select * from Locations;

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

truncate table locations;

insert into Locations (Name,LocationGeog) VALUES ('New York,NY',geography::STGeomFromText('POINT(40.728333 -73.994167)',4326));

insert into Locations (Name,LocationGeog) VALUES ('Flushing, NY',geography::STGeomFromText('POINT(40.7653796 -73.8173564)',4326));

insert into Locations (Name,LocationGeog) VALUES ('Port Washington, NY',geography::STGeomFromText('POINT(40.8255556 -73.6986111)',4326));

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

declare @flushing geography;

set @flushing = (select LocationGeog from Locations where LocationID=2);

declare @portw geography;

set @portw = (select LocationGeog from Locations where LocationID=3);

select @flushing.STDistance(@portw);

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

13385.2523762151

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

declare @manhattan geography;

set @manhattan = (select LocationGeog from Locations where LocationID=1);

select Name,LocationGeog.STDistance(@manhattan) from Locations;

           

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

New York,NY    0

Flushing, NY    19766.6406020214

Port Washington, NY    33123.9813405623

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

truncate table locations;

insert into Locations (Name,LocationGeom) VALUES ('New York,NY',geometry::STGeomFromText('POINT(40.728333 -73.994167)',4326));

insert into Locations (Name,LocationGeom) VALUES ('Flushing, NY',geometry::STGeomFromText('POINT(40.7653796 -73.8173564)',4326));

insert into Locations (Name,LocationGeom) VALUES ('Port Washington, NY',geometry::STGeomFromText('POINT(40.8255556 -73.6986111)',4326));

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

declare @flushing geometry;

set @flushing = (select LocationGeom from Locations where LocationID=2);

declare @portw geometry;

set @portw = (select LocationGeom from Locations where LocationID=3);

select @flushing.STDistance(@portw);            

0.133122489640518

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

declare @manhattan geometry;

set @manhattan = (select LocationGeom from Locations where LocationID=1);

select Name,LocationGeom.STDistance(@manhattan) from Locations;

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

New York,NY    0

Flushing, NY    0.180650045236429

Port Washington, NY    0.311135860960412


Another Examples:

-- Retrieve Spatial data

select *,CONVERT(varchar(50),GeoLocation),GeoLocation.Lat,GeoLocation.Long from AddressBook;

-- Insert example

insert into AddressBook (FirstName,LastName,CreatedDate,GeoLocation)

VALUES('Kiichi','Takeuchi3',GETDATE(), geography::STPointFromText('POINT(42.21500 -71.93172)',4236));

-- Get distances from a location and sort them all

declare @myhouse geography;

set @myhouse = geography::STPointFromText('POINT(42.21500 -71.93172)',4236);

select

GeoLocation.STDistance(@myhouse)

from AddressBook

where

GeoLocation.STDistance(@myhouse) < 200000 -- 200 x 1000 meter

and GeoLocation is not null

order by GeoLocation.STDistance(@myhouse)