Transcript The Business Opportunity Increased user-sophistication Ubiquitous geographical technology • More data sources • More kinds of data • Integrated systems and mash-ups • GPS • Online mapping services • Availability.

The Business Opportunity
Increased
user-sophistication
Ubiquitous geographical
technology
• More data sources
• More kinds of data
• Integrated systems and mash-ups
• GPS
• Online mapping services
• Availability of geographical data
Real-estate
development and
analysis
Scientific research
study design and
analysis
Market segmentation
and analysis
Customer-base
management and
development
Environmentalrelated data impact,
analysis, and
planning
Financial and
economic analysis in
communities
Government-based
planning and
development analysis
POINT
MULTIPOINT
LINESTRING
MULTILINESTRING
POLYGON
COLLECTION
MULTIPOLYGON
Tree, Pole, Hydrant,
Valve
Road, River,
Railway, Pipeline
Cadastre, Park,
Administrative
Boundary
Graphics, Markups
To match geometry
MakeValid will convert them to valid objects
FULLGLOBE spatial object is available
“small” holes can be created
Point
STBuffer
geo STDistance
geo STIntersects
geography data type
• Geodetic (“Round Earth”) geospatial model:
• Ellipsoidal coordinates (Lat, Long, Z, M)
• Define points, lines, and areas with longitude and latitude
• Account for planetary curvature and obtain accurate “great
circle” distances
geometry data type
• Planar (“Flat Earth”) geospatial model
• projected ‘flat map’ (X, Y, Z, M)
• Define points, lines, and areas with coordinates
• Use for localized areas or non-projected surfaces such as
interior spaces
The Mercator projection did not come out until 1569. Please play along 
Well-Known-Binary:
0x0103000000010000000500000000000000004CDDC00
0000000004C0D4100000000004CDD4000000000004CD
D4000000000C07A104100000000004CDD40000000000
0BD0F41000000000088134100000000004CDDC000000
000004C0D41
Well-Known-Text:
'POLYGON(( -30000 240000, 30000 30000, 270000
30000, 260000 320000, -30000 240000))'
GML:
<Polygon xmlns="http://www.opengis.net/gml">
<exterior><LinearRing><posList>
-30000 240000 30000 30000 270000 30000 260000
320000 -30000 240000
</posList></LinearRing></exterior>
</Polygon>
GEOMETRYCOLLECTION (POLYGON ((228869.875 314609.75,
228870.82875722542 314611.91763005778, 228852.73214285716
314634.03571428568, 228869.875 314609.75)), LINESTRING
(218999.875 310799.75, 218890.89169195751 310666.54817905917),
POLYGON ((214987.51136363635 305790.84090909094, 215609.875
305879.75, 215639.875 306329.75, 214987.51136363635
305790.84090909094)), POLYGON ((214949.875 305759.75,
214987.51136363635 305790.84090909094, 214922.60227272729
305781.56818181818, 214949.875 305759.75)), …..
CIRCULARSTRING
(0 50, 90 50, 180 50, 270 50, 0 50)
If a circular linestring encloses an area, a
curve polygon can be created
CURVEPOLYGON (CIRCULARSTRING
(0 50, 90 50, 180 50, 270 50, 0 50))
Coordinate pair order is important
for the geography type. This set
of coordinates is ordered according
to the “left foot rule” for exterior
rings.
Both types provide static and instance methods
Calculate distances, find intersections, etc.
Find streets that intersect the Microsoft campus
SELECT StreetName
FROM Streets
WHERE Streets.StreetGeo.STIntersects(@ms) = 1
COMPOSITION
OPERATIONS
STUnion
STIntersection
STDifference
STSymDifference
RELATION OPERATIONS
STIntersects
STContains
STWithin
STTouches
STOverlaps
CONSTRUCTIONS
STConvexHull
STBuffer
STReduce
BufferWithCurves
ShortestLineTo
NUMERICAL
OPERATIONS
STDistance
STLength
STArea
geometry\geography STCurveN(int n)
Int STNumCurves()
geometry\geography STCurveToLine()
geometry\geography CurveToLineWithTolerance(int tolerance, bool relative)
geometry\geography BufferWithCurves(double range)
geometry\geography ShortestLineTo(geometry\geography other)
Bool CompatibleWithDbCompatibilityLevel(int compatLevel)
Bool STIsValid()
Geography MakeValid()
Bool STContains(geography other)
Bool STWithin(geography other)
Bool STOverlaps(geography other)
Geography STConvexHull()
Bool ReorientObject(geography other)
BufferWithCurves()
This method will construct the
resulting polygon with circular
arcs, often resulting in a
dramatically smaller spatial
object.
DECLARE @g GEOGRAPHY =
GEOGRAPHY::STGeomFromText('CIRCULARSTRING(0 50, 45 50,
90 50)',4326)
DECLARE @b GEOGRAPHY = @g.BufferWithCurves(500000)
SELECT @b.STNumPoints()
--Number of vertices: 11
DECLARE @g GEOGRAPHY =
GEOGRAPHY::STGeomFromText('CIRCULARSTRING(0 50, 45 50,
90 50)',4326)
DECLARE @b GEOGRAPHY = @g.STBuffer(500000)
SELECT @b.STNumPoints()
--Number of vertices: 257
ReorientObject
• This method will reverse the order of
coordinates which define polygon
rings.
• This method is a NoOp on line strings,
circular arcs and points.
• This method does only work with the
GEOGRAPHY type.
geography::Point(lat, lon, 4326)
geography
geography::Point(lat, lon, 4326)
geocoder
webgis.usc.edu/Services/Geocode/About/GeocoderList.aspx
aggregates
geo.data.gov
www.openstreetmap.org
www.census.gov/geo/www/cob
datamarket.azure.com
Shape2SQL
MapPoint Add-in for SQL Server
SAFE FME
ESRI ArcGIS
Pitney Bowes EasyLoader
Take advantage of
comprehensive spatial
support
• geometry and
geography
data types
• Methods for spatial
operations
• On premise and in
the cloud
Build high
performance spatial
solutions
• Store spatial data
with business data
• Spatial indexing
Extend and integrate
geospatial
applications
• Industry-standards
compatibility
• Comprehensive
programmatic
library
@Desinderlase,
SQL Server 2012
Eval Copy
Hands-On Labs
mva
Get Certified!
@sqlserver
@teched_europe
http://europe.msteched.com
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
http://europe.msteched.com/sessions