sql saturday spatial data ss2008 michael stark copy
Post on 16-Apr-2017
778 Views
Preview:
TRANSCRIPT
SQL SERVER 2008Spatial Geography
Implemented in ASP.net and Virtual Earth
Michael StarkStarkNetwork.com
Michael@starknetwork.comblog.starknetwork.com
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OutlineThis session is …a quick tour of the spatial features of SQL Server 2008.
This session is NOT …
An in-depth study of the spatial features of SQL Server 2008
An in-depth explanation of ListView Control
An in-depth explanation of Virtual Earth
Background Overview Spatial in SQL 2008 Creating a spatial Query in
SSMS Spatial Query in ListView
control Spatial Query wired up to
Virtual Earth 21 Other ideas for mapping Implementation Issues
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OverviewTyler’s Article
SQL Server Magazine
SQLMAG.com
Instant Doc ID 100528
Tyler Chessman's article in SQL Server Magazine's December 2008 issue is my primary source of information to implement my spatial solutions. Visit sqlmag.com and search for instant doc ID 100528. I have a link on my blog to the article.
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OverviewWhat is Spatial Data?
Example of Spatial Data
SQL Server’s answer
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.
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
GEOMETRYGEOGRAPHYCLR Data Types
No need to enable CLR
Many Methods
Open Geospatial Consortium
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.
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OGC MethodsOGC Open Geospatial Consortium
SQL Server 2008 supports the Open Geospatial Consortium (OGC) methods on geometry instances.
These methods make it very easy to perform geospatial tasks that were once very difficult.
STArea STAsText STBoundary STBuffer STCentroid STContains STConvexHull STCrosses STDifference
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OGC MethodsOGC Open Geospatial Consortium
SQL Server 2008 supports the Open Geospatial Consortium (OGC) methods on geometry instances.
These methods make it very easy to perform geospatial tasks that were once very difficult.
STDimension STDisJoint STDistance STEndPoint STEnvelope STEquals STExteriorRing STGeometryN STGeometryType
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OGC MethodsOGC Open Geospatial Consortium
SQL Server 2008 supports the Open Geospatial Consortium (OGC) methods on geometry instances.
These methods make it very easy to perform geospatial tasks that were once very difficult.
STInteriorRing STIntersection STIntersects STIsClosed STIsEmpty STIsRing STIsSimple STIsValid STLength
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OGC MethodsOGC Open Geospatial Consortium
SQL Server 2008 supports the Open Geospatial Consortium (OGC) methods on geometry instances.
These methods make it very easy to perform geospatial tasks that were once very difficult.
STNumGeometries STNumInteriorRing STNumPoints STOverlaps STPointN STPointOnSurface STIsSimple STRelate STSrid
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
OGC MethodsOGC Open Geospatial Consortium
SQL Server 2008 supports the Open Geospatial Consortium (OGC) methods on geometry instances.
These methods make it very easy to perform geospatial tasks that were once very difficult.
STStartPoint STSymDifference STTouches STUnion STWithin STX STY
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
DEMONSTRATIONList of functions to demonstrate
I will demonstrate STGeomFromText STDistance AsGML STasText STGeometryType STNumPoints. The demonstration will
return a list of stores within a provided distance of a given point.
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
STDistance
STDistance( Point , SRID )
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.
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
SQL Statement 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 dataaddr.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_milesfrom dbo.Address addr Whereaddr.SpatialLocation.STDistance(@CodeGEOG)/1609.344 < 50 order by distance_in_miles
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
SSMSA demonstration of running a spatial query
DEMO
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
Into ActionA very quick run through
Wire-up Spatial Query to List View Control Virtual Earth
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
List View Control
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
Virtual Earth
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
21 WaysO’Brien lists 20 wayshttp://maps.live.comhttp://dev.live.com/virtualearthhttp://blogs.msdn.com/VirtualEarth3D/And more
I add one more way – Weather .com
John O'Brien of the GEOWebGuru.com has compiled a list of 20 ways to use VirtualEarth. See it here http://www.geowebguru.com/articles/86-twenty-ways-to-use-the-virtual-earth-platform-as-a-developer-
Visit Weather.com to see this flash and VirtualEarth in actionhttp://www.weather.com/weather/map/interactive/33626?from=36hr_topnav_undeclared
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
ImplementationIssues
VE Implementation works fine in development
Returns File Not Found when deployed to IIS
You will find that the VE and Handler implementation returns a File Not Found Error when deployed to a web server.
In IIS6 you right click on your App, go to properties, click Virtual Directory Tab, Configuration, Mapping Tab, Add, point the Executable to c:\windows\microsoft.net\framework\v2.0.50727\aspnet_isapi.dll and add the appropriate Extension ("georss") .
michael@starknetwork.com
SQL Saturday Tampa FloridaJanuary 23, 2010
ConclusionThis session was …a quick tour of the spatial features of SQL Server 2008.
This session was NOT …An in-depth study of the spatial features of SQL Server 2008An in-depth explanation of ListView ControlAn in-depth explanation of Virtual Earth
Background Overview Spatial in SQL 2008 Creating a spatial Query in
SSMS Spatial Query in ListView
control Spatial Query wired up to
Virtual Earth 21 Ways to implement VE Deployment Issues
SQL SERVER 2008Spatial Geography
Implemented in ASP.net and Virtual Earth
Michael StarkStarkNetwork.com
Michael@starknetwork.comhttp://blog.starknetwork.com
top related