Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to Spatial SQL PowerPoint Presentation
Download Presentation
Introduction to Spatial SQL

Introduction to Spatial SQL

205 Vues Download Presentation
Télécharger la présentation

Introduction to Spatial SQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Introduction to Spatial SQL Matt Fancher GIS Specialist Public Utilities Commission of Ohio 2012 Ohio GIS Conference September 19 - 21, 2012 | Hyatt Regency Hotel | Columbus, Ohio

  2. Structured Query Language

  3. SQL can select data from a database SQL can insertdata in a database SQL can update data in a database SQL can deletedata from a database

  4. “I want to SELECT some information FROM a particular source WHERE certain criteria are met.”

  5. SELECT column1, column2 FROM table1 WHERE criteria are met

  6. “I want to SELECT some information FROM a combination of sources WHERE certain criteria are met.”

  7. SELECT table1.column, table2.column FROM table1 JOIN table2 ON table1.id = table2.id WHERE criteria are met

  8. DECLARE @variable AS data type

  9. SET @variable = some value

  10. SET @variable = some object

  11. SELECT column1, column2 FROM table1 WHERE column1 = @variable

  12. Spatial SQL

  13. Double Numeric Time Char Integer Text Money Geography Geometry Decimal Blob Long Binary Real Bit Float Varchar Date Short Datetime

  14. Well Known Text

  15. Point(x y) • Line(x1 y1, x2 y2, … , xnyn) • Polygon(x1 y1, x2 y2, … , xnyn, x1 y1)

  16. STPointFromText(‘Point(x y)’, SRID) • STLineFromText(‘Line(x1y1, x2 y2, … , xnyn)’, SRID) • STPolygonFromText(‘Polygon(x1y1, x2 y2, … , xnyn, x1 y1)’, SRID)

  17. Geography::STPointFromText(‘Point(x y)’, SRID) • Geometry::STPointFromText(‘Point(x y)’, SRID)

  18. “ I want to INSERT a new record • INTO a database table • using this list of VALUES.”

  19. INSERT INTO LocationTable • VALUES(101, geography::STPointFromText(‘Point(x y)’, SRID))

  20. Expose Spatial Properties • Test Spatial Relationships • Perform Spatial Operations

  21. Spatial Properties STX & STY STLength() STArea() STCentroid() STEnvelope()

  22. SELECT LakeName • FROM Lakes • WHERE Shape.STArea > 1000000

  23. Spatial Relationships STIntersects() STDisjoint() STDistance() STTouches() STWithin()

  24. SELECT Congress.District, County.County • FROM Congress • JOIN County ON Congress.Shape.STIntersects(County.Shape) = 1

  25. Spatial Operations STIntersection() STUnion() STDifference() STBuffer() STConvexHull()

  26. DECLARE @Township as Geometry; • DECLARE @City as Geometry; • DECLARE @Difference as Decimal; • SELECT @Township = Shape FROM Township • WHERE Name = ‘Orange Township’; • SELECT @City = Shape FROM City • WHERE Name = ‘Columbus’; • SELECT @Difference = @Township.STDifference(@City).STArea;

  27. Application Process: Google Maps Geocode Service Address Latitude/Longitude Coordinates SQL Statements Display on Page Result Set

  28. SELECTElectric_Company, Company_Type, Choice FROMElectric_Company_Table WHEREShape.STIntersects(geography::STPointFromText('POINT(<longitude> <latitude>)', 4269)) = 1; SELECTMunicipality_Name FROMMunicipal_Utility_Table WHEREShape.STIntersects(geography::STPointFromText('POINT(<longitude> <latitude>)', 4269)) = 1; SELECTTelephone_Company, Telephone_Exchange FROMTelephone_Exchange_Table WHEREShape.STIntersects(geography::STPointFromText('POINT(<longitude> <latitude>)', 4269)) = 1; SELECTCounty_Name FROMCounty_Table WHEREShape.STIntersects(geography::STPointFromText('POINT(<longitude> <latitude>)', 4269)) = 1;

  29. SQL Solution: --First create a point from the user’s input DECLARE @Point AS geometry; SET @Point = geometry::STPointFromText('POINT(<x_coord> <y_coord>)', 0); --Then buffer the point by a specified distance DECLARE @Buffer AS geometry; SET @Buffer = @Point.STBuffer(<linear_distance>); --Finally execute a select statement to estimate the population in the buffer SELECT ROUND(SUM(PopDen * Shape.STIntersection(@Buffer).STArea() / 27878400),0) ASPopulationEstimate FROM Census_Block_2010 WHEREShape.STIntersects(@Buffer) = 1;

  30. The End

  31. T-SQL Script Implementation: Web Server ColdFusion Script Web Map Built on Google Maps API Request T-SQL Statements Response Request Result Set MS SQL Server Database

  32. Loading Spatial Data • Shape2sql:

  33. SELECT The "Persons" table: SELECT LastName, FirstNameFROM Persons The result-set will look like this:

  34. WHERE The "Persons" table: SELECT * FROM Persons WHERE City='Sandnes' The result-set will look like this:

  35. JOIN The "Persons" table: The "Orders" table: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.P_Id = Orders.P_IdORDER BY Persons.LastName The result-set will look like this: