A database is a collection of information that is organized so that it can be easily accessed, managed and updated [Wikipedia]. For instance a database can be created to house all the data of patients visiting a hospital every day. Such data may include: the name of patient, time of visit, gender, age and diagnosis. The database can therefore be queried to get information such as number of patients per day, the disease that is most common among the male, female or at different ages. This information can help the hospital better manage the facilities available. Such kind of a database is called a Database Management System (DBMS).
A DBMS is a computer software application that interacts with the user, other applications and the database itself to capture and analyze data [wikipedia]. Examples include MySQL and PostgreSQL which are OpenSource, Microsoft SQL and Oracle which are Proprietary. It is recommended that a beginner starts with Microsoft access and progress slowly to other DBMS such as MySQL and PostgreSQL. Mostly we will be using PostgreSQL because it has a spatially enabled package called PostGIS that will come in handy in handling spatial data.
Installation of PostgreSQL/PostGIS
To install PostgreSQL, use the steps outlined below. This tutorial assumes you are using windows operating system.
- Visit the official website for PostgreSQL and go to the download section for windows. http://www.postgresql.org/download/windows/
- Click on download installer from EnterpriseDB
- From there you can select the version of PostgreSQL and your operating system. In this manual we will be using version 9.5.6
- After downloading, start the installation process by double clicking the set up file.
Leave the default directory and click Next
Unless otherwise, leave the default directory and click Next.
Enter the password. This will be used to access PostgreSQL once it is installed. Click Next.
Ensure that no other application is using this port. Otherwise, leave the default Port and click Next.
Leave the default locale and click Next.
Click Next to start the installation process.
Be patient as this might take a few minutes.
Once the installation is complete, click the check box as shown above. This will launch the stack builder and help in installing additional tools such as PostGIS. Click Finish.
Select in the dropdown arrow the version of PostgreSQL installed. Click Next.
The key focus of this manual is PostGIS and this is what will be installed for now. However, explore the content of the other check boxes.
Under the Spatial Extensions select the appropriate version of PostGIS for your computer.
Choose the directory (or leave the default) for the chosen package and click Next.
This might take some minutes depending on the speed of the internet.
Click Next to start the installation of the downloaded packages.
Read through the terms of agreement then click I Agree.
Check the Create spatial database check box and click Next.
Leave the default installation folder and click Next.
Specify the username, password and Port you entered earlier in the installation. Click Next.
Write an appropriate database name and click Install.
This will take some few minutes.
Currently I do not have GDAL installed in my computer, so I will click Yes. This will be used for operations that will involve raster, such as raster transformations, clipping etc.
Click Yes to enable the Raster drivers.
Click Yes again to enable ‘out of database’ rasters.
The installation is complete. Click Close, then Finish.
To confirm that PostgreSQL/PostGIS is installed we need to launch it. It comes with a Graphical User Interface (GUI) called pgAdmin III. Search this in the windows explorer and click to launch it.
Right click on the PostgreSQL and click on connect as shown above. Enter the password that you entered during the installation process. The software is now ready for use. The next section will cover some basic operations on the functionality of PostgreSQL.
Source of featured image: http://gisvm.com/gisvm-postgis/