Microsoft Spatial – SQL Server Spatial & Virtual Earth

Microsoft Spatial – SQL Server Spatial & Virtual Earth Matti Seikkula & Pete Smith e-Spatial www.e-spatial.co.nz

Why Microsoft Spatial? • Your data enabled with location intelligence with client-, middle- and server-tier tools • Spatial data analysis and mining of your data with spatially-enhanced T-SQL • Detailed vector data from SQL Server Spatial via presentation layer • Graphical representation of your data tied to statistical and geographical datasets • High-quality imagery on presentation layer • 2D and 3D views of location data

Vector data via Virtual Earth

Geographical Themes

Quality Imagery via VE

2D/3D View on Grid dataset Farm

SQLServer Spatial – Key Concepts • Spatial Data types – the Spatial Object • Object definition (using points, lines and polygons) and type • Points – point(X,Y) • (Multi)lines – line(point(x1,y1),point(x2,y2),…) • (Multi)polygons – polygon(line(…),line(…),…) • Geometry collections • Projection and datum • Default style (colour, thickness, pattern) • Spatial indexing – R-tree or Quad-tree (or Bi-tree) • For Fast geographic searches • SQL Server Spatial will use a 4-stage grid (like Quad-tree) • Spatial functions – intersects, touches, centroid (75+)… • To perform spatial analysis • Standards-based SQL extensions

SQLServer Spatial - Syntax

SQLServer Spatial - Concepts

SQLServer Spatial - Example Example 1 -- Flooding – 100-metre buffer on lake -- Mapped as see-through hashed boundary SELECTLake.geometry.STBuffer(100) FROM Lake WHERE Lake.Name = ’My Lake’; My Lake Example 2 -- Private properties affected by flooding -- Mapped as solid pink objects SELECT property.id ,property.geometry FROM Lake , Property WHERE Lake.Name = ’My Lake’ andProperty.geometry.STOverlaps (Lake.geometry.STBuffer(100);

Microsoft Spatial – Components • SQLServer Spatial – storage for spatial data • SSIS – used for uploading/downloading spatial data (one-off) and managing spatial ETL processes • Mapping “Services” – functionality for viewing and analysing vector map data (thematics) • Reporting Services Mapping plug-in – enables publishing vector map viewer functionality within reports • Virtual Earth – presentation layer for viewing 2D/3D mapping data • Visual Studio VE/Map plug-ins – enables use of Virtual Earth engine and/or Map Viewer functionality within .NET projects • But: • No reference mapping data • No address search data, model or engine (e.g. e-SAM,the e-Spatial PAF-based geographical database model)

Spatial in the database • This example map includes: • Properties (polygon) • Addresses (point) • Roads (line) • Postcodes (polygon) • Database tables would be: • Property – 48 rows • Address – 37 rows • Street – 3 rows • Postcode – 1 row

Spatial in the database • Relational data model: • Postcode and propertyboundaries overlap • Street is tied to propertyand overlaps postcodes • More than one addresscan exist on a property • Address belongs to exactly one street • Relational spatial can becomplicated (overlaps) as typically 50+ layers in a map!

Spatial in SQL Server SQL example: “In postcode 4102 show all properties with addresses on Van Asch Road”

Spatial in SQL Server • No need to define many-to-many relationship tables, nor foreign-key links • Data maintenance much easier • Most line-to-point relationships are not spatial (point is not usually on a line), but a spatial relationship can be achieved using a buffer • SQL example: “Show all properties within 10 metres of Van Asch Road” select p.* from street st , property p where st.street_name = 'Van Asch Road' and p.geometry.STIntersects(st.geometry.STBuffer(10))

Spatial in SQL Server • The buffer query applied a 10m buffer to the whole Van Asch Road • This was used in an intersect query on properties to find all touchingthis road buffer • This query returned 6 rows • This is also an example of how spatial queries can be used on a database without necessarily returning a map

Spatial in SQL Server • Buffer example – now include the area of the property • SQL example: “Show all properties and their area in square metres within 10 metres of Van Asch Road” select p.* , p.geometry.STArea() as “AreaSQm2” from street st , property p where st.street_name = 'Van Asch Road' and p.geometry.STIntersects(st.geometry.STBuffer(10)) Order by p.geometry.STArea() desc • The returned properties are also ordered by largest area • We could continue – for example to do an intersect from the properties to surrounding properties (hazardous substance burning on road)

Spatial in SQL Server • Large fire (200m radius) on a given property • Let’s find its nearest neighbours • SQL Example: “Show propertieswithin 200 metres of 110 BeachRoad by shortest distance”| select p.* from street st , address a , property p where a.house_number = 110 and st.street_name = 'Beach Road' and a.street_id = st.street_id and p.geometry.STIntersects(a.geometry.STBuffer(200)) order by a.geometry.STDistance(p.geometry.STCentroid())

Spatial in SQL Server • Nearest-neighbour query: suppose we also have a point dataset including all ATMs in SQL Server • ATMs are in an XY layer (no geometry associated) • SQL example: “Show 5 closest ATMs in our map extent of 600m by 600m for 110 Beach Road (same map as before)” select top 5 b.* from street st, address a , bank_ATM b where a.house_number = 110 and st.street_name = 'Beach Road' and a.street_id = st.street_id and geometry::STGeomFromText('POINT ('||b.XCOORD||' '||'b.YCOORD'||)', 0) .STIntersects(a.geometry.STBuffer(300).STEnvelope()) order by a.geometry.STDistance(geometry::STGeomFromText( 'POINT ('||b.XCOORD||' '||'b.YCOORD'||)', 0).STCentroid())

Contact e-Spatial • Matti Seikkula, CIO • Email matti@e-spatial.co.nz • Mobile 027 566 5625 • Pete Smith, Enterprise Architect • Email pete@e-spatial.co.nz • Mobile 027 438 3834 • Wayne Chapman, Sales Manager • Email wayne@e-spatial.co.nz • Mobile 027 422 1551 • e-Spatial Limited • Level 15, Morrison Kent House • Phone 04 499 3546, Fax 04 499 3547 • www.e-spatial.co.nz

Microsoft Spatial – SQL Server Spatial & Virtual Earth