1 / 37

Where’s My Data?

Where’s My Data?. Spatial queries in SQL Server 2008 SQL Bits III – 13 th September 2008. New data types geometry and geography Spatial references Spatial operations Spatial indexes Case study. What’s covered in this talk. 80-90% of all data has a spatial element

tender
Download Presentation

Where’s My Data?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Where’s My Data? Spatial queries in SQL Server 2008 SQL Bits III – 13th September 2008

  2. New data types geometry and geography Spatial references Spatial operations Spatial indexes Case study What’s covered in this talk

  3. 80-90% of all data has a spatial element • Where are your customers? • Where are your assets? • Where are potential customers? • Where are the flood risks? • Where are your complaints coming from? • Where are the accident black-spots? • Where are crimes happening? Why Should I Care About Spatial Data?

  4. Could Fudge the Queries • Postcodes in Glasgow • Zoned • Historical reasons • G5 adjacent to G42 • G40 in an island • Postcodes designed for delivering letters

  5. The data analysis engine • No useful rendering engine • Virtual Earth • Map Point • Other GIS systems • OGC Standards compliance • Plus some “extension” methods of their own What Does SQL Server 2008 Provide?

  6. Geometry • X/Y coordinate on a planar grid • British National Grid • Works well to ~750,000km2 • Different projections

  7. Geography • Geodetic coordinates • Covers larger areas • International datasets • Approximation • Earth actually flattened sphere (oblate spheroid) • Different models • Airy 1830 (used by OS) • WGS84 (used by GPS)

  8. Spatial Reference Identifier • All spatial data has an SRID • SRIDs must match for spatial operations • Null returned if SRIDs don’t match • Geometry can have an SRID of 0 • Not Geography. SRID

  9. Types of spatial Data • Point • LineString • Polygon • GeomCollection • MultiPolygon • MultiLineString • MultiPoint From BOL

  10. Create POINT Data

  11. SELECT a.Name AS StartVenue, b.Name AS EndVenue, a.Location.STDistance(b.Location) / 1000.0 As Distance FROM Venue AS a INNER JOIN Venue AS b ON a.Id < b.Id ORDER BY a.Id, b.Id Finding Distances

  12. A linestring is a series of coordinates • 1 dimension • Defines a linear object • Road • Railway line • River LINESTRING

  13. Can use • STGeomFromText • STGeomFromWKB • STLineFromText • STLineFromWKB • Parse Creating Linestrings

  14. Create LINESTRING Data

  15. Geography uses SI Units Geometry uses the units of the planar system Geometry Lengths & Distances A ? units The square of the hypotenuse is equal to the sum of the square of the other two sides Distance from A to B: √(32+42) = 5 3 units B 4 units Not to scale

  16. Intersecting Lines

  17. A series of coordinates in a closed ring • First and last coordinate are the same • 2 dimensions • Defines an area POLYGON

  18. Geography Polygon Orientation • Interior is everything inside an anti-clockwise ring • Everything on the left-hand side of the perimeter line.

  19. Geography Polygon Orientation The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.

  20. Can use • STGeomFromText • STGeomFromWKB • STPolygonFromText • STPolygonFromWKB • Parse Creating Polygons

  21. Creating polygons

  22. Estate Agent • Filter by price, # bedrooms, type – EASY! • Filter by location? • Until now very vague Fictional Case Study

  23. Common Spatial Questions Near a motorway junction Near my work Near a railway station Near a good school Inside the city Outside the city

  24. The Data • Railway data • Stations • Routes

  25. Simple Spatial Queries

  26. Doesn’t Always work • Edinburgh - Glenrothes (via Kirkcaldy) Name DistKM -------------------------- ---------------- Edinburgh Waverley Station 0 Haymarket 1.89298770395887 South Gyle 6.95446540329848 Burntisland 12.086984317701 Dalmeny 12.49585147351 Kinghorn 13.1439998546632 Aberdour 13.3392361220632 North Queensferry 14.3833962761344 Dalgety Bay 15.0082794626365 Inverkeithing 15.7316327831032 Kirkcaldy 17.9484646860063 Glenrothes With Thornton 23.7022936117453

  27. Looking for a home

  28. Display the properties

  29. Spatial Indexing • Decomposes space into 4 levels of grid • Level 1 is the top • Cells are uniform in a level • A level can be a 4x4, 8x8 or 16x16 grid • 8x8 by default

  30. Table must have a primary key Primary key cannot subsequently be changed. Not on views Maximum of 249 Spatial indexes per column Restrictions

  31. Why have multiple indexes on one column • Where geometry/ geography sizes vary • e.g. Rail routes • Small: Suburban lines • Large: Intercity lines

  32. Supports • STIntersects • STEquals • STDistance • One Geography must be a point • Both sides of the spatial operation must have the same SRID Spatial Indexing

  33. Must specify boundary of spatial area • Additional methods supported • STContains • STOverlaps • STTouches • STWithin Spatial Indexing on a Geometry

  34. CodePlex project • More spatial methods • Aggregations • Scripts http://www.codeplex.com/sqlspatialtools And There’s More!

  35. What I’ve not mentioned • GML • Import and export • M and Z • Can store • Cannot operate. • Other spatial Operations • Geometry has more! • Visualisation • .NET application integration • Data Importing

  36. Slide Deck on my website • http://www.colinmackay.net • Blog posts on Spatial Data • http://blog.colinmackay.net Get More Information

  37. Questions ?

More Related