Starting with spatial databases: a case of PostgreSQL

This will be your best start with spatial databases, not the theory but its use.

Not being a computer professional, yes you read that right. Being a social science graduate, and trained mostly with paper based work during my undergraduate degrees. The first time I heard of PostgreSQL being used by one of my colleagues to do all her GIS related work was shocking, but even more when I found out that many of my GIS professional were having a hard time adding a raster to a database. Let alone, processing it and getting meaningful data out of it.

The process was therefore of interest and concern for me. I decided to take my chances with it. And found out that there was so much so wrong that people tried starting with. Its not the query language that you need to start with, its the database management system, its tables, rows/columns, and data handling, data import and export and visualization of that data, that gives you a good enough start with it. The simple CRUD (CREATE, READ, UPDATE and DELETE) operations can wait for a while.

Since, many of you are already aware of our course on

I prefer to use a pre configured system as a virtual machine on my computer. You can read more about how to create one, using my previous articles setting up OSGeoLive and Installing guest additions, links are here.

But before you start, you need to understand that for creating, the OSGeoLive virtual machine on your PC, your username should be user and keep the password preferable user too. The reason for this being that on the OSGeoLive system, the username for the PostgreSQL database is user too. So accessing it becomes easy and hassle free.

On a preconfigured Free and Open Source Geo System, you will be able to use a variety of commands and interact with your Postgres installation. For the sake of starting up. Press ctrl+alt+t and a command shell will open for you, there type

psql        

If your username is different from the default username as suggested above, you can then use the following command to connect

psql -U <username>        

and you will be prompted for a password. The above is not recommended, as mentioned before. Sometimes you might be required to make administrative changes to work with a different username and it can be troubling for new users.

You will be presented with a Postgres shell and there you can try the following few commands and see if everything is working right for you.

\l        

The above command will print all the databases for you and some other relevant details such as owner, and access privileges etc. Some other details are frivolous for an early user.

Then you can connect to one of the database shown in the list using

\c <name of the database>        

Once you are connected to a database, you can for now list the tables, in that database, using the command

\dt         

Some of the other commands you can try to use are

\d <table name>        

The above will show table definition including any triggers that were used. For listing schemas in a table you can type

\dn         

and for functions

\df        

for views (a named query stored in a database)

\dv         

and to list your tables in a format also known as pretty format, you can type

\x         

although no exes are pretty they just feel pretty because you don't have them anymore.

To quit your sql shell you need to type the following

\q        

Next up you should try and create, a database, run some additional information commands. First connect to psql again using the psql command and type the following commands.

CREATE DATABASE exercise_db;        

CREATE EXTENSION postgis;        

SELECT PostGIS_Full_Version();        

You will have the following output

Now connect to the database you just created and then list the tables in it. You can do that by type

\c exercise_db         

Then type

\dt         

And you will see the following screen.

This suggests that you have successfully create a database and enabled its spatial abilities. So now it can handle vector spatial data for you. For raster I will be posting a separate blog.

Hope you benefitted. If so, share for others too.