SpatiaLite Introduction Spatial Is Not Special

1. SpatiaLite Introduction • Spatial Is Not Special Reporter:金玲艳 Report Date:2013/10/24

2. Main Content • 1.Introduction • SQLite • SpatiaLite • Special • 1.4Key Feature • 2.Application • Software library support • Standards • Application Example • 3.Comparation • 4.Spatial Index • 5. Prospect

3. 1.Introduction ArcGIS 10.2 New Point • ArcGIS for Android Offline data editing solution • ESRI added support for SQLite as a geodatabase container. • SQLite transaction mechanism can improve the speed of data bulk insert. • SQLite database transaction mechanisms can improve the efficiency of multiple updates. • Offline data editing operations basically completed, pending future SpatiaLiteincreasingly improved spatial database, the offline editing will become easier

4. 1.1Introduction——SQLite • SQLite is this little adorable single file relational database system that runs equally well on Windows, Linux, Unix, Mac and is easily embeddable in larger apps. • Is Free and Open Soure :Public domain so no restrictions for commercial and embedding in other applications. • We use it in some of our projects for synching data back and forth between a mainstream server database such as PostgreSQL, SQL Server, MySQL and a client offline database that needs to synch differential data back and forth to the mother ship.

5. 1.1Introduction——SQLite • SQLite is intrinsically simple and lightweight: • a single lightweight library implementing the full SQL engine • standard SQL implementation: almost complete SQL-92 • no complex client/server architecture • a whole database simply corresponds to a single monolithic file (no size limits) • any DB-file can be safely exchanged across different platforms, because the internal architecture is universally portable • no installation, no configuration • SpatiaLite is smoothly integrated into SQLite to provide a complete and powerful Spatial DBMS (mostly OGC-SFS compliant). • Using SQLite + SpatiaLite you can effectively deploy an alternative open source Spatial DBMS roughly equivalent to PostgreSQL + PostGIS.

6. 1.2Introduction——SpatiaLite • SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality. • It is similar to PostGIS, Oracle Spatial, and SQL Server with spatial extensions. • Although SQLite/SpatiaLite aren't based on client-server architecture: they adopt a simpler personal architecture. • The whole SQL engine is directly embedded within the application itself. • A complete database simply is an ordinary file which can be freely copied (or even deleted) and transferred from one computer/OS to a different one without any special precaution.

7. 1.2Introduction——SpatiaLite • SpatiaLite extends SQLite's existing spatial support to cover the OGC's SFS specification. • It isn't necessary to use SpatiaLite to manage spatial data in SQLite, which has its own implementation of R-tree indexes and geometry types, but in order to do advanced spatial queries and support multiple map projections, SpatiaLite is needed. • SpatiaLiteis provided natively for Linux and Windows as a software library as well several utilities that incorporate the SpatiaLitelibrary. • These utilities include command line tools that extend SQLite's own with spatial macros, a graphical GUI for manipulating SpatiaLitedatabases and their data, and a simple desktop GIS tool for browsing data. • Being a single binary file WTK, SpatiaLiteis also being used as a GIS vector format to exchange geospatial data.

8. 1.2Introduction——SpatiaLite

9. 1.3Special • All the tables fit into a single file so easy for transport • It has this cute little dblink like feature that allows you to mount external files as virtual tables and join with your SQLite data. • PHP has built-in drivers for SQLite it, .NET has drivers for it you can dump in your bin folder and get going, SharpMap (has a driver for the SpatiaLite version in the works) - thanks to Bill Dollins similar to what it has for PostGIS. (note the drivers unlike other databases contain the engine as well). This is very important to understand since you want your driver to have the SpatiaLite enhancements. • The SQLite core engine is a relatively tiny application that provides you more or less standard ANSI-SQL 92 syntax so with a basic abstraction layer, you can treat this as you would any other relational database.

10. 1.3Special • Favorite - it even supports SQL views • It supports triggers • Has basic ACID/Transactions -- BEGIN/COMMIT; • SpatiaLite sweetens this little database by allowing you to store geometries and query them with spatial functions similar to what you would find in PostgreSQL/PostGIS, Microsoft SQL Server 2008, MySQL, IBM DBII, Oracle Locator/Spatial. • In terms of the model it uses, it seems closest in syntax to PostGIS and in fact modeled after PostGIS and also piggy-backs on GEOS and Proj.4 like PostGIS. In fact the functionality you will see is pretty much the functions you get in PostGIS including their names minus the aggregates and some other things, except you just need to strip off the ST_ and add in a bbox filter, so PostGIS users should feel very much at home.

11. 1.4Key Feature • OGC functions similar to what you find implemented in PostGIS/GEOS. This uses GEOS. • R-Tree Spatial Index if you use SQLite 3.6+, and rudimentary MBR index for lower SQLite • Rudimentary support for curves • Lots of MBR functions (Minimum bounding rectangle), similar to what MySQL 5.1 and below has, but also has real functions as described above for more exact calculations • Unfortunately the current release seems to lack spatial aggregate functions that PostGIS has such as Extent, Union, collect. • Spatial Transformation support using Proj.4 that hmm even SQL Server 2008 lacks

12. 2.Application Software that supports Spatialite • Quantum GIS, native support since Version 1.4 • FME • AutocadMap 2013 • OSGeo Live DVD includes spatialite along with a short tutorial. • Pitney Bowes MapInfo Professional 11.5.2 • Global Mapper • ESRI ArcGIS 10.2

13. 2.1 Software library support • Mapnik - as part of the SQLite driver • GDAL - within the OGR project. GDAL supports reading and writing of Spatialite data [6] • Django (web framework) via the GeoDjangomodul

14. 2.2 Standards • Spatialite supports several open standards from the OGC and has been listed as a reference implementation for the proposed GeoPackage standard.

15. 2.3 Application Example QGIS 1.7-SpatiaLite Layer SQLite/Spatialite layer in gvSIG Mobile

16. 2.3 Application Example VSceneGIS Desktop 0.9.2. Nuevas conexiones al catalogo: SQLite (SpatiaLite), WMS y WFS QGIS Plugin - TimeSeriesPlot– Shorter introduction

17. 3.1Comparation——PostGIS How does PostGIS compare to Spatialite? • PostGIS is a more traditional database, in that it can maintain many levels of organization - schemas, tables, etc - within a single 'data directory' and cit communicates over a port or a socket. • Whereas Spatialite, like SQLite, is a file-based database that doesn't have, for example, a user system. • Both provide basic spatial operations, though PostGIS has more depth there. Spatialite is annoyingly licensed and its license infects software built on it, whereas PostGIS isn't and doesn't.

18. 3.1 Comparation——PostGIS How does PostGIS compare to Spatialite? • PostGIS offers many more features. GeoDjango's documentation has a comparison of the spatial features it supports from each database .Please note that this is not a direct comparison of the databases. Rather it reflects what GeoDjango supports.  However, it will still give you a rough idea of the differences between the two.  • The choice between PostGIS and Spatialite comes down to your particular use case.  Spatialite is probably the best choice when you need a standalone, embedded database.  For example, maybe you are building a mobile or desktop application.  Also, Spatialite may be used during the development of a web-based application. I have used it during the development of GeoDjango applications.  

19. 3.1Comparation——PostGIS How does PostGIS compare to Spatialite? • PostGIS is the better choice if you need high read/write rates, multiple database users, clustering, network accessibility and any other standard database feature.   • Regarding Spatialite's licensing, the library is licensed under the LGPL and the GUI tools are licensed under the GPL (the same license as MySQL).   This means in most cases, you do not need to worry about the viral nature of GPL

20. 3.2 Other DBMS Comparation

21. 3.3 Why SpatiaLite? • SpatiaLite is an open source sourcegeodatabase built on top of SQLite, itself an open source database. As a lightweight, “server-less” database, SQLite is frequently used as an embedded database by other applications, including Firefox, Google Chrome, Skype, iTunes, Clementine (my preferred FOSS music player), and Dropbox (on the client side). • You can’t just copy a database, or a table, from your Postgres server and send it to someone else or use it on another machine. The receiver has to be using Postgres/PostGIS also, and you have to dump/restore the data using database tools, rather than just copying a file using the filesystem. It seems that most people share geographic data using shapefiles, an ESRI specification supported by many open source projects. 

22. 3.3 Why SpatiaLite? Shapefiles have, in my opinion, three strikes against them. • It is a multi-file format, and worse contains an inconsistent number of files, so one shapefile is actually 3 to 12 filesystem files with the same base name (although in practice most shapefiles will contain 5ish filesystem files). • Attribute field names are limited to eight characters because it uses DBF to store attribute data. • Support for the DBF format is declining. It was dropped from 2007 versions of Microsoft Excel and Access, although you can still edit these formats with FOSS tools like OpenOffice/LibreOffice.

23. 3.3 Why SpatiaLite? • In addition, SpatiaLite and the ESRI personal geodatabase format have a major advantage, which is that they allow combining several data sources (like shapefiles) into one database file—one SQLite file or Microsoft Access file.  • SpatiaLite offers a nice one-file solution to organization and portability, and SpatiaLite has a lot of spatial analysis features built in, so that you can do some limited analysis even without a separate GIS software.

24. 3.3 Why SpatiaLite? Spatialite: Speedup your query with spatial indexing • Spatialite, like any good spatial data management system, can build a spatial index for your layers. Using this index in your spatial queries will dramatically shorten the runtime for that query. The latest version of Spatialite offers a nice compact format for using a spatial index. 

25. 4.1Spatial Index——R*Tree

26. 4.2 Spatial Index——R*Tree • SQLite supports a first class R*Tree: anyway, some implementation details surely may seem strongly exotic for users accustomed to other different Spatial DBMS (such as PostGIS and so on). • Any R*Tree on SQLite actually requires four strictly correlated tables: • rtreebasename_node stores (binary format) the R*Tree elementary nodes. • rtreebasename_parent stores relations connecting parent and child nodes. • rtreebasename_rowid stores ROWID values connecting an R*Tree node and a corresponding row into the indexed table. • none of these three tables is intended to be directly accessed: they are reserved for internal management. • rtreebasename actually is a Virtual Table, and exposes the R*Tree for any external access. • important notice: never attempt to directly bungle or botch any R*Tree related table;quite surely such attempt will simply irreversibly corrupt the R*Tree. You are warned.

27. 4.3 SpatiaLite's support for R*Tree • Any SpatiaLite Spatial Index fully relies on a corresponding SQLite R*Tree. Anyway SpatiaLite smoothly integrates the R*Tree, so to make table handling absolutely painless. • Any SpatiaLite's Spatial Index always adopts the following naming convention: • assuming a table named local_councils containing the geometry column. • the corresponding Spatial Index will be named idx_local_councils_geometry • and idx.local_councils.pkid will relationally reference local_councils.ROWID. • Anyway using the Spatial Index so to speed up Spatial queries execution is a little bit more difficult than in other Spatial DBMS, because there is no tight integration between the main table and the corresponding R*Tree: in the SQLite's own perspective they simply are two distinct tables.

28. 5. Prospect Big data SpatiaLite Cloud

29. Thank you