1 / 53

Introduction to Spatial SQL

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. S tructured Q uery L anguage. SQL can select data from a database SQL can insert data in a database

braden
Download Presentation

Introduction to Spatial SQL

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. 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:

More Related