Transcript Everything you ever wanted to know about getting started

Everything you ever wanted to know
about getting started with the
SQL Server 2008
Spatial Data Types …
… on the cheap!!
Spatial Data - Intro
− In the past few years, spatial information has come into it’s own
as a powerful tool in the battle for better business intelligence.
The question is: how can you and/or your business leverage it?
− This talk will provide a 1000 mile an hour overview of a fairly
common scenario in which SQL Server 2008 Spatial could be
used by many NZ businesses.
− There are some gaps that need filling here and there, mainly
due to my own lack of understanding of the subject matter.
However the overall principal is sound, I have used it, and with
a bit of research on your part, it could work for you too!
− Using the following scenario, I aim to show you how to get
started and realise the spatial data types potential with minimal
cost, by using free (or at least, inexpensive) datasets and
software.
− The idea is that if you were ever interested in spatial data, this
is something you could spend a few Friday arvos setting up and
be able to show the boss some real business benefits as a
result.
Scenario: Mass Marketing Task
− Client asks you to create a dynamic mail/email
marketing campaign for a product/service based on
customer demographic.
− Client is (for example) an insurance company.
− Insurance would be marketed differently depending on
whether the customer lives in a wealthy area or an area
where there are lots of young families.
− Client provides 50K customer addresses, and some
styling directives – you need to come up with a
proposed solution as to how to manage the
demographic component.
− The remaining slides take us through the process of
how to achieve this using SQL Server 2008.
The Data Types
− There are 2 new spatial data types with SQL Server
2008 called Geometry and Geography. They come with
all versions of the product (except Compact?), including
SQL Server Express and are implemented as a CLR
data types in SQL Server.
− Geometry: this data type represents data in a
Euclidean (flat) coordinate system.
− Geography: this data type represents data in a roundearth coordinate system. The SQL Server geography
data type stores ellipsoidal (round-earth) data, such as
GPS latitude and longitude coordinates.
− Together these data types, provide almost all of the
spatial data management and manipulation
functionality of a full blown commercial GIS.
Geocoding
− I.e. getting your customer address data geo-referenced.
− Free geo-coding services. Yahoo, Google, etc. These facilities are
great for getting started with and can provide you with the ability to
run some intriguing experiments with your geo-coded customer
data, before you get more serious about your spatial data.
− Yahoo and Google offer free* RESTful web-service APIs for geocoding purposes, so you also get to try out your skills with XLINQ
or the XML data type!
− Adhoc Geo-coding: many organisations offer a geo-coding service.
For a one-off fee, you provide them with a file of addresses; then
they provide you with a list of coordinates.
− NZ Post GeoPAF: for the more serious geo-coder. This is a
subscription based dataset provided by NZ Post, that lists every
address that has ever had mail delivered to it along with a geospatial coordinate. Includes ‘Meshblock_Identifier’ field …
•
Please do read the fine-print with these services –
there are some caveats for corporate users.
Digital Boundaries
and Census Data:
− This information is freely available from Statistics New
Zealand. Link provided in this slide-stack …
− The digital boundaries data provides a range of
different geographic boundaries for NZ, including
‘Wards’, ‘Regional Councils’ and ‘Meshblocks’.
− We are interested in the Meshblocks as this is what
enables us to attach a geo-spatial component to our
Census data.
Coordinate Systems and SRIDs:
− When you’re loading up a new record that contains a
spatial data type field, you need to specify an SRID.
− Once you have the data, depending on how you got it,
you may need to run a conversion in order to get it into
the same coordinate system.
− For example, the Google Maps API returns data in the
WGS84 (SRID 4326) coordinate system, which is also
the default for the geography data type.
− New Zealand Digital Boundaries data is only available
in NZMG coordinate system.
− The best approach I have found so far for local (NZ –
e.g. with the Census data) use, is to convert everything
into NZMG, then use the geometry data type with ‘0’ as
the SRID.
Digital Boundaries Data Format:
− Format is one of the most common
for Spatial Data: ESRI ‘Shapefile’
format.
− This format is used by the Arc suite
of GIS products and has an open
specification (similar to Office
2007).
− Many people have already written
conversion programs that manage
the transition of Shapefiles into
other formats.
− I used Morten Nielsen’s
‘Shape2SQL‘ to get my Boundary
Data into SQL Server. This is a
closed source, but free product.
Works for me - trust it if you dare!
− Remember to select ‘planar
geometry’ and uncheck ‘set SRID’.
You will definitely want to leave
‘Create Spatial Index’ checked.
Data Formats – cont …
How to get your Geo-Coded data into the database?
-- create a table
CREATE TABLE Your_GeoCoded_Address_Points
(Delivery_Point_ID int, X_Coordinate int, Y_Coordinate int, GeoPoint geometry)
-- pull the data into our spatially enabled table
DECLARE GenericCursor INSENSITIVE CURSOR FOR
SELECT ID, X_Coordinate, Y_Coordinate
FROM Your_Raw_Spatial_Data
OPEN GenericCursor
DECLARE @dpid as int
DECLARE @x as int
DECLARE @y as int
DECLARE @g geometry;
FETCH NEXT FROM GenericCursor INTO @dpid, @x, @y
WHILE @@FETCH_STATUS = 0
BEGIN
SET @g = geometry::STPointFromText('POINT (' + cast(@x as varchar(10)) + ' ' + cast(@y as varchar(10)) + ')', 0)
INSERT INTO Your_GeoCoded_Address_Points (ID, X_Coordinate, Y_Coordinate, GeoPoint) values (@dpid, @x, @y, @g)
FETCH NEXT FROM GenericCursor INTO @dpid, @x, @y
END
CLOSE GenericCursor
DEALLOCATE GenericCursor
-- create your spatial index
CREATE SPATIAL INDEX SIndx_Your_GeoCoded_Address_Points_Geom
ON Your_GeoCoded_Address_Points(GeoPoint)
WITH ( BOUNDING_BOX = ( 2082369, 5356610, 2993453, 6744844 ) )
Now What?
− You’ve done the hard yards - collected your
data into a big neat pile in the database, it’s all
in the same format and is just itching to be
queried …
− Here’s where the power of the spatial data
types starts to shine through. For the following
queries, you’d want to use SSMS 2008 and
keep an eye on the ‘spatial results’ tab …
Pick a ‘Ward’ from the
Meshblock Data:
select * from dbo.MB06_LV2 where ward06 = 00704
Pick a Meshblock from that Ward:
select geom from dbo.MB06_LV2 where id = 30650
Run a ‘Spatial Join’ query
to get your addresses in that
Meshblock:
select GeoPoint.STBuffer(10)
from Your_GeoCoded_Address_Points
where geopoint.STWithin(
(select geom from dbo.MB06_LV2 where id = 30650)) = cast(1 as bit)
Thanks! …, etc …
− …. then take a look at what goodies are
available in the (free, publicly available) 2006
Census dataset.
− I’ll leave the rest to your imagination.
− Finally, if you’re serious about spatial data and
don’t have a GIS background, I suggest you
seek professional assistance before using this
approach to make business decisions …
− My details:
− [email protected]
− [email protected]
Links:
−
−
−
−
−
−
−
−
−
−
−
NZ Digital Boundaries: http://www.stats.govt.nz/statistics-byarea/geography-mapping/download-digital-boundaries.htm
NZ Census Data: http://www.stats.govt.nz/census/censusoutputs/meshblock/default.htm?tab=About
ESRI Shapefile Tech-Spec:
http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
SRIDs: http://msdn.microsoft.com/en-us/library/bb964707.aspx
Meshblocks: http://www.stats.govt.nz/statistics-by-area/geographymapping/default.htm
Morten Nielsen’s Shape2SQL:
http://www.sharpgis.net/page/Shape2SQL.aspx
Spatial Indexes in SQL Server 2008: http://msdn.microsoft.com/enus/library/bb895265.aspx
Introducing SQL Server 2008 – free Microsoft Press E-Book book offer:
http://csna01.libredigital.com/?urss1q2we6
Geography Data type: http://msdn.microsoft.com/enus/library/bb895266.aspx
Geometry Data Type: http://msdn.microsoft.com/enus/library/bb895270.aspx
Manifold GIS: http://www.manifold.net/index.shtml
Website:
www.aucklandsql.com
Mailing list:
[email protected]