The Business Opportunity

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

Real-estate development and analysis Customer-base management and development Scientific research study design and analysis Environmental related data impact, analysis, and planning Financial and economic analysis in communities Market segmentation and analysis Government-based planning and development analysis

POINT LINESTRING POLYGON COLLECTION MULTIPOINT MULTILINESTRING 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

STBuffer Point

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:

-30000 240000 30000 30000 270000 30000 260000 320000 -30000 240000

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 RELATION OPERATIONS CONSTRUCTIONS NUMERICAL OPERATIONS STUnion STIntersection STDifference STSymDifference STIntersects STContains STWithin STTouches STOverlaps STConvexHull STBuffer STReduce BufferWithCurves ShortestLineTo 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 Bool

STWithin

(geography other)

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::Point(lat, lon, 4326)

geocoder

webgis.usc.edu/Services/Geocode/About/GeocoderList.aspx

www.idvsolutions.com/products_spatialWiki.aspx

aggregates

www.geodata.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

[email protected]

http://sqlblog.com/blo gs/michael_rys/

Michael Rys

@SQLServerMike

mva

http://northamerica.msteched.com

www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn