BLUE SKY THINKINGSQL Azure Geospatial Mashup

ABOUT ME▪ Thomas Sykes▪ Data Platform Consultant for Quorum in Edinburgh▪ Using SQL Server since version 7.0▪ MCDBA, MCITP and MCSE in SQL Server▪ MCS in Azure Implementation and Hyper-V▪ Certified Technical Trainer

THIS SESSION▪ SQL Azure with Geospatial Data▪ Azure Websites with Bing Maps▪ Integrate another live data source

HOLIDAY CLIMBING

AZURE SQL DATABASE▪ Windows Azure SQL Database

▪ Microsoft’s PaaS offering for SQL Server Database Engine▪ With V12 is much closer to our IaaS or On-Premise Database Engine▪ Scalable with various options from Basic development to Mission Critical Premium

AZURE SQL DATABASE▪ Connect straight from Management Studio once you’ve allowed access▪ Database size based on DTU’s▪ Actively developing

AZURE SQL DATABASE

NEW AZURE SQL DATABASE

AZURE WEB HOSTING▪ Azure Websites - Microsoft’s Web Hosting Offering

▪ Various Tiers from Free, Shared, Basic, Standard Tier▪ Scalable -More servers/Bigger servers▪ Staged Publishing/Deployment slots

▪ http://<site>-<slot name>.azurewebsites.net/

AZURE WEB HOSTING

NEW AZURE WEB HOSTING

SQL SPATIAL▪ A spatial data type, such as geography (or geometry) can be indexed with a spatial index▪ Various functions supported by spatial indexes▪ We will use the STDistance function

▪ Returns the shortest distance between two geography points

DATA - GEOSPATIALS▪ Get started with free digital map data

▪ OS Locator - A searchable gazetteer of road namesWe need current postcode’s▪ Code-Point Open - All of the current postcode

DATABASE – POSTCODESFor the postcodes table the obvious candidate for clustered index is the postcode field

DATABASE – HILLSFor the Hills table a natural unique index would Latitude and Longitude

DATABASE

Spatial Reference System Identifier (SRID) WGS84 — SRID 4326

"The World Geodetic System (WGS) is a standard for use in cartography, geodesy, and navigation. It comprises a standard coordinate system for the Earth, a standard spheroidal reference surface (the datum or reference ellipsoid) for raw altitude data, and a gravitational equipotential surface (the geoid) that defines the nominal sea level." - Wikipedia

DATABASELoch Ness Centre Exhibition Experience at IV63 6TU

DATABASEUsing nearest neighbour in SQL Azure we get the results:

DEMO

BING MAPS - RENDER

DATA – WEATHER

DataPoint is a way of accessing freely available Met Office data feeds in a format that is suitable for application developers. UK 3-hourly site-specific forecast -A quality controlled forecast out to 5 days for more than 5,000 locations. The DataPoint service is operating as BETA

SCOTTISH WEATHER ICON

DATA – WEATHER – STATION DATA

We can either get an XML stream or download the weather station locations as below;

DATA – WEATHER - GET STATIONDECLARE @CurrentLocation geographySET @CurrentLocation = geography::Point(@lat,@long, 4326)SELECT TOP(1) ID,name,ROUND(GeoLocation.STDistance(@CurrentLocation)/1000,2) AS [Distance (Km)]FROM [dbo].[WeatherPlaces] WITH(INDEX([ix_GeoLocation]))WHERE GeoLocation.STDistance(@CurrentLocation)IS NOT NULLORDER BYGeoLocation.STDistance(@CurrentLocation) ASC

DATA – WEATHER

DATA – DISPLAY WEATHER

THE SITE - VIDEO

USEFUL LINKS▪ Azure

▪ http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/▪ http://azure.microsoft.com/en-us/pricing/details/sql-database/▪ http://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-scale-get-started/

▪ OS▪ http://www.ordnancesurvey.co.uk/business-and-government/products/opendata-products.html

▪ Met Office - DataPoint▪ http://www.metoffice.gov.uk/datapoint

CONTACT▪ Thomas Sykes▪ Blog: sqltomato.com▪ twitter: @sqltomato▪ email:tom@sqltomato.com

THANK YOU