1 / 60

Spatialite

Spatialite. http://www.gaia-gis.it/gaia-sins/. Created by Alessandro Furieri (Italy) First release published in 2008 SpatiaLite is an open source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities.

iman
Download Presentation

Spatialite

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. Spatialite http://www.gaia-gis.it/gaia-sins/ • Created by Alessandro Furieri (Italy) • First release published in 2008 • SpatiaLite is an open source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities. • SpatiaLiteis smoothly integrated into SQLite to provide a complete and powerful Spatial DBMS (mostly OGC-SFS compliant).

  2. SQLite http://www.sqlite.org/ • Created by Richard Hipp • First release published in 2000 • SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. • A single lightweight library implementing the full SQL engine • standard SQL implementation: almost complete SQL-92 • no complex client/server architecture • no installation, no configuration

  3. What do you need to get started? • Spatialite • Spatialite_GUI Program • http://www.gaia-gis.it/gaia-sins/ • SQLite • SQLite Manager (Firefox plugin) • Install extension from Firefox browser

  4. Resources SQLite: http://www.sqlite.org/ -File based relational database Spatialite: http://www.gaia-gis.it/gaia-sins/ -SQLite extension for gis functionality GDAL: http://www.gdal.org/ -Set of libraries for ETL operations with multiple GIS formats QGIS: http://www.qgis.org/ -Open Source GIS application OSGeo4W: http://trac.osgeo.org/osgeo4w/ -Installer for suite of open source GIS software and tools GIS

  5. Spatialite GUI Application

  6. SQLite database files

  7. DEMO • Spatialite GUI • Shapefile loader • GDAL Command line • Load shapefile, file geodatabase

  8. Loading Shapefile in Spatialite GUI Load Shapefile

  9. Using GDAL to load data into Spatialite

  10. Using GDAL to load data into Spatialite -Load shapefile into Spatialitedatabase ogr2ogr –update –append –f SQLite C:\GISData\NDIC\ndic.sqlite C:\GISData\NDIC\shapefiles\Horizontals_Lines.shp Horizontals_Lines–nlnHorizontals_Lines Shapefile directory to Spatialite (windows): for %f in (/GISData/NDIC/*.shp) do ( ogr2ogr -update -append -t_srs ESPGS:4267 -f SQLite C:\GISData\NDIC\ndic.sqliteGISData\NDIC\%f -nlnlayers) -Load file geodatabase feature class into Spatialite database ogr2ogr –update –append –f SQLite C:\GISData\NDIC\ndic.sqlite MTND_CadNSDI.gdbPLSSFirstDivision –nlnLandgrid_PLSS_Sections

  11. DEMO • Firefox SQLite Manager • Spatialite GUI

  12. Using Firefox SQLite Manager -Basic query

  13. Spatialite GUI -Basic query

  14. Spatialite GUI -query with spatial function

  15. Using Firefox SQLite Manager -Trying to use spatial functions in query

  16. SQLite vs Spatialite new database structure Spatial Metadata Empty

  17. Pros • Cross-platform • No custom installation to use • Single file for transport • Simple access to SQL queries • Large spatial function library • Transform geometries within sql query • Fast prototyping of geodatase modeling and analysis • Cons • Only simple multi-editor environment, no conflict management • Less performant than server based RDBMS

  18. Who is Spatialite for? • Non-Enterprise user • Users with reasonable size datasets • User wanting to get started with SQL • Users needing another GIS tool in their belt • User familiar with SQL wanting access to spatial functions • User who needs functionality of RDBMS but does not have access to servers

  19. Who is Spatialite NOT for? • Enterprise user who needs maximum performance • Users who need a robust multi-editor environment with conflict management • User who have very large datasets

  20. DEMO • Spatialite GUI • Query examples

  21. Spatialite GUI -Basic query

  22. Spatialite GUI -query with spatial function

  23. SQL Server Non-Spatial Queries SQL Server MGMT Studio – Midpoint on Linestring USE database IF OBJECT_ID('tempdb..#NDIC_HORZ_LIST') IS NOT NULL DROP TABLE #NDIC_HORZ_LIST SET NOCOUNT OFF -- Create temp table to hold the OBJECTID and apply filter on wells without enough points SELECT lineID = identity (int,1,1), OBJECTID INTO #NDIC_HORZ_LIST FROM database.dbo.API12_PATHS CREATE CLUSTERED INDEX idx_NDIC_HORZ_LIST_lineid ON #NDIC_HORZ_LIST(lineID) CREATE INDEX idx_NDIC_HORZ_LIST_objectid ON #NDIC_HORZ_LIST(OBJECTID) DECLARE @midPoint geometry DECLARE @point1X numeric(25,20) DECLARE @point1Y numeric(25,20) DECLARE @point2X numeric(25,20) DECLARE @point2Y numeric(25,20) DECLARE @xDiff numeric(25,20) DECLARE @yDiff numeric(25,20) DECLARE @workingLineLength1 numeric(25,20) = 0 DECLARE @workingLineLength2 numeric(25,20) = 0 DECLARE @lineLength numeric(25,20) DECLARE @iint DECLARE @lineIDint DECLARE @lineOID INT DECLARE @line geometry DECLARE @workLine geometry DECLARE @lastSegment geometry DECLARE @lineFromString NVARCHAR(MAX) SET @lineID = 1 -- Outer loop to iterate over lines in table, using the counts from the temp table WHILE @lineID <= (SELECT COUNT(*) FROM #NDIC_HORZ_LIST) BEGIN -- Assign OBJECTID FROM Temp table to variable SET @lineOID = (SELECT OBJECTID FROM #NDIC_HORZ_LIST WHERE lineID = @lineID) -- Assign SHAPE From table to geometry variable SET @line = (SELECT SHAPE FROM database.dbo.API12_PATHS WHERE OBJECTID = @lineOID) SET @lineLength = @line.STLength() / 2 SET @lineFromString = 'LINESTRING(' SET @i = 1 -- Loop through points in line until the working length is longer than half the line length WHILE @workingLineLength2 < @lineLength BEGIN -- For first point add start point from line IF @i = 1 BEGIN SET @lineFromString = @lineFromString + CAST(@line.STStartPoint().STX AS VARCHAR(10)) + ' ' + CAST(@line.STStartPoint().STY AS VARCHAR(10)) + ')' END ELSE BEGIN -- Strip the trailing ) from the line string text SET @lineFromString = LEFT(@lineFromString, LEN(@lineFromString) - 1) SET @lineFromString = @lineFromString + ',' + CAST(@line.STPointN(@i).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i).STY AS VARCHAR(10)) + ')' SET @workLine = geometry::STGeomFromText(@lineFromString, 4267) SET @lastSegment = geometry::STGeomFromText('LINESTRING(' + CAST(@line.STPointN(@i - 1).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i - 1).STY AS VARCHAR(10)) + ',' + CAST(@line.STPointN(@i).STX AS VARCHAR(10)) + ' ' + CAST(@line.STPointN(@i).STY AS VARCHAR(10)) + ')', 4267) SET @workingLineLength2 = @workLine.STLength() IF @workingLineLength2 > @lineLength BEGIN SET @point1X = @line.STPointN(@i - 1).STX SET @point1Y = @line.STPointN(@i - 1).STY SET @point2X = @line.STPointN(@i).STX SET @point2Y = @line.STPointN(@i).STY SET @xDiff = (@point2X - @point1X) * ((@lineLength - @workingLineLength1) / @lastSegment.STLength()) SET @yDiff = (@point2Y - @point1Y) * ((@lineLength - @workingLineLength1) / @lastSegment.STLength()) SET @midPoint = geometry::STGeomFromText('POINT(' + CAST(@point1X + @xDiff AS VARCHAR(25)) + ' ' + CAST(@point1Y + @yDiff AS VARCHAR(25)) + ')',4267) INSERT INTO database.dbo.API10_SHL(OBJECTID,SHAPE) SELECT ROW_NUMBER() OVER(ORDER BY t2.OBJECTID), @midPoint FROM database.dbo.API12_PATHS AS t2 END SET @workingLineLength1 = @workingLineLength2 END SET @i = @i + 1 END SET @lineID = @lineID + 1 END Multiple While Loop Operations SLOWER

  24. Spatialite GUI -query with spatial function

  25. Spatialite GUI -attach to another sqlite database SQL Query GUI

  26. Spatialite GUI -Query using join to attached database

  27. Spatialite Spatial Functions

  28. Using Spatialite in ArcMap • Read-only access was added at 10.2 • Can use geoprocessing tools with layers but with limitations • Using layers for geoprocessing input and output to different format seems to work pretty well. • Can write results to sqlite database, but pretty buggy at 10.2.1 • Recommend using as read-only access at current version • Cannot be published to ArcGIS Server • Cannot be edited through ArcMap

  29. DEMO • Spatialite functions • SQL in Spatialite GUI • View layers in ArcMap

  30. Spatialite GUI • Interpolated point along line • Use as midpoint

  31. ArcMap - Midpoints

  32. Spatialite GUI • Line offset • Geometry transformation inline function

  33. ArcMap • Line offset

  34. Spatialite GUI Single Sided Buffer

  35. Spatialite GUI Buffer

  36. ArcMap Single Sided Buffer

  37. ArcMap Single Sided Buffer vs Buffer

  38. Spatialite GUI • Convex Hull vs Concave Hull

  39. ArcMap • Convex Hull vs Concave Hull Convex Hull Concave Hull

  40. Using Spatialite in QGIS • Read/Write Access • Direct Editing of features • DBManager – Execute queries on SQLite database inside QGIS

  41. DEMO • Spatialite editing in QGIS • SQL in QGIS DB Manager

  42. Spatialite Editing -Connecting to Spatialite database

  43. Spatialite Editing -Creating new table SQL Query Editor

  44. Spatialite Editing -Add Geometry Column

  45. Spatialite Editing -Add new table to map

  46. Spatialite Editing -Editing new layer

  47. Spatialite Editing -Query new feature in Spatialite

  48. Spatialite Editing -View new feature in ArcMap

  49. QGIS Query Layer Single Sided Buffer

  50. QGIS Single Sided Buffer

More Related