SQL Server GeoSpatial Del Robinson Spatial data types

SQL Server Geo-Spatial Del Robinson

Spatial data types • Geometry – flat plane – uniform 2 dimensional (Euclidean) – position is x, y (any value) • Geography – curving surface of an ellipsoid of the earth – position is lat, long • where latitude is +-90 (N-S) and longitude is +-180 (E-W) • They are. NET (CLR) data types – so they have methods and properties – OGC (Open Geospatial Consortium) methods + MS Extensions

Spatial Object Types • Simple Spatial Object Point Line Polygon • Collection Spatial Object Multi Point Multi Line Multi polygon Example in Well Known Text POINT (1 1) LINESTRING EMPTY LINESTRING(1 1, 2 2) POLYGON EMPTY POLYGON( (1 1, 3 3, 3 1, 1 1) ) Interior regions possible Example in Well Known Text MULTIPOINT( (2 3), (7 8) ) MULTILINESTRING EMPTY MULTILINESTRING((1 1, 1 1), (1 1, 3 5) ) MULTIPOLYGON EMPTY MULTIPOLYGON ( (1 1, 3 3, 3 1, 1 1), (0 0, 0 1, -1 1, 0 0) ) Interior regions possible • 2012 Additions – Circular String / Compound Curve / Curve Polygon

Spatial Objects Methods / Properties • Properties – use. syntax, and returns number – my. Geo. Column. STX / STY / Lat / Long • Methods – use. syntax – my. Geo. Column. STLength() Method Name STAs. Text STArea STLength STContains(spatial) STIntersects(spatial) STBuffer(x) Output String Float Bool Spatial Comment Well Known Text (WKT) representation of object Area within a polygon Length of line or perimeter of polygon True if the 2 nd object is entirely contained within the first (geometry only) True if the 2 spatial objects overlap New spatial object which is x larger than before. Point becomes circle etc STIntersection(spatial) STUnion(spatial) STDifference(spatial) Spatial New spatial object which is the overlapping part of the 2 shapes New spatial object which is the union of the 2 shapes New spatial object which is the first object minus the second object • MS Extended methods - Point / Parse + many others • Static Methods – use : : syntax, and returns object – geometry: : STGeom. From. Text(‘POINT(1 0)’ , 0) – geography: : STPoint. From. Text(‘POINT(170 -45)’ , 4326) – Different functions for WKT, WKB, GML

Definition in SQL Server • Create Table CREATE TABLE My. Table ( My. New. Column geography ) • Procedures – variables and table variables – Declare @pt geometry = Point(1, 2, 0) – Declare @loc geography – Declare @points table (id int, pt geometry)

Demo – Points, Lines, Polygons • Lessons – – – Geometry examples should look like school maths Geography Point() takes lat, lon, SRID WKT is always X, Y so for geography that is lon, lat Geography distances are hard to estimate Expect to get. NET errors

Generating your spatial data Address eg 285 St Asaph St Geocoding process Lat Long Coordinates -43. 53632, 172. 64338 Grid Coordinates NZGD 2000 eg 1571230 5179560 Conversion process UPDATE My. Table set my. Geo = Point(Lat, Long, 4326) GPS data eg Car, Phone, Garmin Geography column populated

Spatial Indexes • Essential for performance – Complicated explanation – Specific query usage • geography 1. STDistance(geography 2) <= number – Check usage with Show. Plan – table scan + calculation • Geometry – index requires a bounding box to constrain area – Supports several spatial methods • Geography – Only supports STIntersects / STEquals / STDistance

Index Usage - Showplan output • Performance – 500 k rows – Good (<0. 2 s): WHERE geopoint. STDistance(@geo) <1000 WHERE @geo. STDistance(geopoint) <1000 – Bad (17 s) Table Scan: WHERE 1000 > geopoint. STDistance(@geo)

Demo – NZTA Crash data queries • Open Data from the NZTA site as CSV • 500, 000+ crashes over 15 years • Simple queries – Crashes near a point – Crashes near a line – Crashes inside a regular polygon

Demo - Google Maps • Show data points on a map, no programming • Very simple page using copy and paste • Simple page using static file – JSON format • Use Google Maps Routing • • User selects route Get line segments for route Send line co-ords to SQL Server finds points which are close to the line

Demo – Spatial Clustering • View of crash data “hotspots” – Many crashes at or near the same point

Statistical Geographic Hierarchy 46 k+ Meshblocks, 2000+ Area Units There also health & police districts

Demo – loading Stats NZ boundaries • Koordinates. com – Provider of many datasets. CSV export option • Boundary files are available on Stats NZ site in – ESRI geodatabase – ESRI shapefiles – Map. Info TAB • Annual files or Census-based files – Use Census 2013 if matching to Census statistics • Census 2013 meshblock datasets – http: //www. stats. govt. nz/Census/2013 -census/datatables/meshblock-dataset. aspx

Features by Version • SQL 2008 – original release. Geography is half hemisphere • SQL 2012 – Support for circular strings, compound curves, curve polygons. + more methods – Support for Full Globe geography • Vertex order important for polygons – New option for SRID – the “round” earth – 104001 • SQL 2014 – No specific changes? • SQL 2016 – ? ? No specific changes ? ?

References / Links • Contact del@omegatech. co. nz • Microsoft information – Spatial Data Types https: //msdn. microsoft. com/en-us/library/bb 933790. aspx – Geography methods https: //msdn. microsoft. com/en-us/library/bb 933802. aspx – Geometry methods https: //msdn. microsoft. com/en-us/library/bb 933973. aspx • OGC – Open geospatial consortium – http: //www. opengeospatial. org/ • Statistics NZ – administrative boundaries – http: //www. stats. govt. nz/browse_for_stats/Maps_and_geography/Geographicareas/digital-boundary-files. aspx#annual – http: //www. stats. govt. nz/browse_for_stats/Maps_and_geography/Geographicareas/geographic-area-files. aspx • Koordinates. com – downloadable maps • Google Maps api – https: //developers. google. com/maps/documentation/javascript/tutorial