Transcript UK mapping with SSRS 2008 R2

UK MAPPING WITH SSRS 2008 R2
[email protected]
IN THE NEXT HOUR
• Start 9:30
• UK Mapping with SSRS 2008 R2
• Open Data
• Point Code Open
• Boundary line
• Spatial data types
• Creating corporate mapped data
• End 10:25 Q/A
TO MAP UK DATA WE NEED
• Map outlines
• Administrative boundaries ( North East ; Yorkshire ; York)
• Address translation to latitude and longitude
• Post codes?
• Road names ?
• Towns?
• Sales data
• SSRS 2008 R2 Express ( and above)
• Other Countries www.gadm.org
OPEN DATA
• Code-point Open
(20mb csv)
• Postcodes, grid reference
• 1:50 000 Gazetteer
(11mb csv)
• Towns and areas , grid reference
• OS Locator
(35 MB text)
• Road names, grid reference
• Boundary Line
(250mb ESRI)
• Local government Administrative boundaries ( ward , district, county ..)
• Several others image based
UK POST CODES
A simple Guide
POSTCODES CAN BE SPATIAL DATA
YO 10 5 DD
• Area
184
• District
• Sector
• Unit
3,000
10,000
1,600,000
YO10 5DD
• UK. Postcodes are 7 characters long
• Inward code always last 3 characters
• Outward code 2 – 4 characters
A Unique Key for 1- 100 Addresses
OPENDATA
Where can I find post code location data?
http://www.ordnancesurvey.co.uk/oswebsite/products/code-pointopen/index.html
IS THE UNITED KINGDOM ROUND OR FLAT
http://www.ordnancesurvey.co.uk/oswebsite/gps/
OSGB36 ( NATIONAL GRID )
IF THE EARTH IS NEITHER FLAT OR ROUND
THEN IS IT A CUBE?
http://www.puzl.co.uk/earth-cube-p-79.html
ONE SIZE DOES NOT FIT ALL?
•
Many standards
•
SELECT *
•
FROM
•
sys.spatial_reference_systems
SOME MYTHS*
• A point on the ground has a unique latitude and
longitude
• A horizontal plane is a level surface
• The true coordinates of a ground point do not change
• There are exact mathematical formulae to change
between coordinate systems
• * a guide to coordinate systems in Great Britain v1.9 Mar 2008 D00659 © Crown copyright Page 3 of 46
PRECISION AND ACCURACY
OS & QUEST FREEWARE
REFERENCES
•
http://www.naturalearthdata.com/
•
http://www.mathsisfun.com/accuracy-precision.html
COPYRIGHT
• OS OpenData™ is covered by either Crown Copyright, Crown
Database Right, or has been licensed to the Crown.
• Mapping :
•
Contains Ordnance Survey data © Crown copyright and database right 2010
• Where you use Code-Point® Open data,
•
Contains Royal Mail data © Royal Mail copyright and database right 2010.
UK ADMINISTRATION
BOUNDARIES
But Which One?
Scotland
Wales
Northern
Ireland
Council Areas
Unitary
Authorities
Local
Government
Districts
HTTP://WWW.STATISTICS.GOV.UK/GEOGRAPHY/ENGLAND.ASP
England
Govenment
Office Regions
North
Yorkshire and
Humberside
East Midlands
East Anglia
..
HTTP://WWW.STATISTICS.GOV.UK/GEOGRAPHY/ENGLAND.ASP
Government
Office Region
Greater
London
Counties
Metropolitan
Counties
London
Boroughs
Nonmetropolitan
districts
Metropolitan
Districts
Unitary
Authorities
SHAPE FILES
• Shape file ESRI standard
• Open data - Boundary line.
• Administrative boundaries
• Fwtools (free)
• Convert between SRID
• Shape2sql (free)
• Import shape files into sql
Q&A
• Robert Edgson
• [email protected]
• http://EdgsonRobert.wordpress.com
•