Understanding Spatial Database From Scratch In Open Source Software (QGIS)
In our quest to understand what "spatial database" is, let’s first understand the meaning of the two words "spatial" and "database" that formed the phrase.
WHAT IS SPATIAL?
The word spatial describes how objects fit together in a certain location (space), either among the planets or down here on earth. Spatial is relating to, occupying, or having the character of space.
WHAT IS DATABASE?
A database is an organized collection of data that can easily be accessed, managed, and updated. Database is also viewed as a collection of related information that permits the entry, storage, input, output and organization of data. A database consists of an organized collection of data for one or more uses, typically in digital form.
A database management system (DBMS) serves as an interface between users and their database. A database management system (DBMS) consists of software that operates databases, providing storage, access, security, backup and other facilities (Wikipedia, 2020).
Data are organized into fields (columns/attributes) and records (rows/entries) in most traditional or regular databases. Another name for traditional or regular database is non-spatial database or normal database.
COMMONLY USED DATABASES
There are several examples of database servers available, but the top common once include: SQLite, Oracle, MySQL, PostgreSQL, IBM DB2, MS Access and MsSQL server.
WHAT MAKES A DATABASE "SPATIAL DATABASE"?
The ability of a database to store and access data that represent objects defined in a geometric space makes it a Spatial Database. Spatial databases use specialized software to extend a traditional database to store and query data defined in two-dimensional or three-dimensional space. The spatial extensions allow you to query geometries using Structured Query Language (SQL) in a similar way to traditional database queries. Spatial queries and attribute queries can also be combined to select results based on both location and attributes.
A Spatial database is also referred to as 'geodatabase' or 'geographical database' or 'geospatial database'.
Most of the commonly used (well-known) databases that have an extended support for spatial objects are listed below:-
S/N
|
Database
|
Spatial Extension
|
License
|
1.
|
Oracle
|
Oracle Spatial/Locator
|
Proprietary
|
2.
|
PostgreSQL
|
PostGIS
|
Open
Source
|
3.
|
MsSQL
Server
|
MsSQL
Spatial
|
Proprietary
|
4.
|
IBM
DB2
|
DB2
Spatial
|
Proprietary
|
5.
|
MySQL
|
MySQL
Spatial
|
Open
Source
|
6.
|
MS
Access
|
Not
supported
|
Proprietary
|
7.
|
SQLite
|
SpatiaLite
|
Open
Source
|
Note: If your dataset is extremely large (big data), you may like to consider a database framework called: Hadoop - SpatialHadoop.
Above are all SQL /relational based databases that best work with data that have relationship.
Another category of databases worth mentioning is the NoSQL (Not only SQL) database. NoSQL databases are designed for volume and rapid indexing of unstructured or semi-structured data. They are good at dealing with lots and lots of reading/writing tasks coming in at once in real-time (a common feature found in web-based GIS), something that tends to slow down SQL/relational databases.
Web-based GIS is probably the area that is currently leading in the use of NoSQL databases within GIS industry, as types of real-time data are more typically found in these platforms when compared to the desktop platforms.
Some popular examples of NoSQL database are:-
a) Cassandra
b) Mongodb
c) CouchDB
d) Redis
e) Riak
f) RethinkDB
g) Couchbase (ex-Membase)
h) Hypertable
i) ElasticSearch
j) Accumulo
k) VoltDB
l) Kyoto Tycoon
m) Scalaris
n) OrientDB
o) Aerospike
p) Neo4j
q) HBase
OPEN SOURCE SPATIAL DATABASE
Among the databases listed above, SQLite (SpatiaLite), MySQL (MySQL Spatial) and PostgreSQL (PostGIS) are Open Source Spatial Databases. This means that they are made available with a license in which the copyright holder provides the rights to study, change, and distribute the database to anyone and for any purpose.
SpatiaLite, MySQL Spatial, and PostGIS are the three major and important open-source spatial database systems that have helped to shape the world of spatial application development.
These open source spatial databases are very well supported in several desktop geoprocessing software within the open source geospatial ecosystem including QGIS.
DIFFERENCE BETWEEN TRADITIONAL DATABASE AND SPATIAL DATABASE
1. A spatial database supports special data types for geometric objects and allows you to store geometric data (usually of a geographic nature) in tables while a traditional or regular database doesn't support such.
2. A spatial database provides special functions and indexes for querying and manipulating geospatial data using something like Structured Query Language (SQL) while traditional or regular database doesn't provide such functions and indexes.
3. A spatial database is often used as a storage container for geospatial data, but it can do much more than that. While traditional or regular database is often used as a storage container for non-spatial data.
4. A spatial database uses spatial query in geometric functions to answer questions about space and objects in space. While traditional or regular database don't support spatial queries.
5. In addition to being able to answer questions about the use of space, spatial database functions allow you to create and modify objects in space. This portion of spatial analysis is often referred to as geometric or spatial processing.
6. A spatially enabled database can intrinsically work with data types like rivers (modeled as linestrings), land parcels (modeled as polygons), and trees (modeled as points). While traditional or regular database can’t work with these forms of models.
CREATING A SPATIAL DATABASE
I will demonstrate how these three open source spatial databases are used within the QGIS desktop software. Let's get our hands dirty by creating our first spatial database based on the three open source spatial databases listed above - SpatiaLite, MySQL Spatial, and PostGIS.
There are several tools that can be used to create spatial databases, but in this article I will use the cross-platform QGIS desktop software.
Download and install the latest version of QGIS.
CONNECTING TO SPATIAL DATABASE IN QGIS
Regardless of the type of database you wish to load, the loading sequence is similar. There are several ways to interact with spatial databases in QGIS. Most common are as follow:-
~ The first way, which will be covered in detail in this section, is to use the Layer | Add Layer menu and select the appropriate name of database you wish to connect.
~ The second way is to open the Browser panel, navigate to the database you wish to load, and then right click on the database you want and select "New Connection". After which you can drag the database table on to the map display, or on to the Layers panel.
~ The third way to load database is to enable the Manage Layers toolbar and click on the button representing the database type you wish to load. To load MySQL spatial database, you have to use the “Load Vector Layer” button and select “Database” as the source type as seen below.
~ The fourth way to load database is to enable the Database toolbar or use the Database | DB Manager menu and click on the button representing the DB manager.
GET THE DATA
Now that we know how to connect spatial databases to QGIS, let’s take a quick look at the dataset we’ll be loading into the databases. Get a Shapefile spatial dataset that you will load into the spatial database we will create in the next section. You can use any shapefile for this, I am going to download and use the administrative boundary map of Nigeria in shapefile format from DIVA-GIS.
Load in the shapefile (the file ending in .shp) through the menu Layer | Add Layer | Add Vector Layer or from the tools bar Add Vector Layer as seen below.
LOOKING AT EACH SPATIAL DATABASES SEPARATELY
Starting with SpatiaLite, let’s explore each of the databases and load shapefile into the database tables. We use shapefile because it is the most basic and widely used spatial data file format. Aside that, other spatial files like geo-referenced raster images could also be stored in a spatial database.
WORKING with SpatiaLite DATABASE
SpatiaLite is an SQLite database engine with Spatial functions added. This is the spatial extension for SQLite database to support fully fledged Spatial SQL capabilities. SpatiaLite is a self-contained, file-based (Serverless) spatial database which means it doesn't require a server to set it up.
In QGIS, open the Browser panel and right click on “SpatiaLite” then select “Create Database...”. Navigate to the folder where you want to save the database and enter a suitable name (the name I used is: db_spatialite) then click on “Save”.
...coming soon...
WORKING with MySQL (MySQL Spatial) DATABASE
...coming soon...
WORKING with PostgreSQL (PostGIS) DATABASE
...coming soon...
GeoPackage DATABASE
REFERENCES
~ https://en.m.wikipedia.org/wiki/Database
~ https://en.m.wikipedia.org/wiki/Spatial_database
~ https://www.vocabulary.com/dictionary/spatial
~ https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
~ http://gisgeography.com/spatial-databases/
~ https://www.quora.com/How-do-spatial-databases-differ-from-the-usual-database
~ QGIS Documentation: http://www.qgis.org/en/docs/index.html
No comments:
Post a Comment