First Assignment in Web-based GIS Class

My first assignment for the Web-based GIS class has two parts: 1. Reading the first chapter of Spatial Databases: A Tour, by Shashi Shekhar and Sanjay Chawla, answering several conceptual questions related to the reading, and 2. A "lab" project designed to get us familiar with our VirtualBox VM, pgAdmin, and QGIS.

The questions involved understanding what spatial data is, comparing file systems to databases, looking at a brief history of spatial database management systems (SDMSs), defining abstract data types (ADTs) and understanding an SDMS as a specific instance of example of an object-relational database management system (ORDBMS).

The lab portion of the assignment involved running pgAdmin and QGIS on a VirtualBox VM to connect to a PostGIS database. I've been using VirtualBox for years, so I wanted use this assignment to push my PostGIS administration knowledge by setting up a PostGIS database on the web and migrating the database from our class VM to this server, and then connecting to it from pgAdmin and QGIS clients running on local machines.

Moving a Database

Logging into the VirtualBox VM given to us in class, I ran the following commands:

ggs $ sudo -i
# su - postgres
postgres $ pg_dump -c nyc > nyc.sql

(the file can be downloaded from here).  I then made the following substitutions in the nyc.sql file, since I want the database owner to be my username and not the postgres user:

1. change ' TO postgres' to ' TO [user]'
2. change ' FROM postgres' to ' FROM [user]'

I then used scp to copy the nyc.sql file to my on-line database server.

Picking up on the database server where I left off in my previous post, I now need to make my regular user a database admin who can create databases.

$ sudo su - postgres
$ createuser --superuser [user]
$ exit

Now I can create the database and import the data:

$ createdb nyc
$ psql nyc < nyc.sql

Let me run a query from the lesson to make sure it works:

$ psql nyc

psql (9.3.9)
Type "help" for help.

nyc=#  select name from nyc_subway_stations where name like 'Broad%';
         name         
-----------------------
 Broadway
 Broadway Jct
 Broadway Jct
 Broad St
 Broadway-Lafayette St
 Broadway-Nassau St
 Broadway Jct
 Broad Channel
 Broadway
(9 rows)

nyc=#

To get the postgres server to except outside connections, I needed to make the following changes to config files:

$ sudo vi /etc/postgresql/9.3/main/pg_hba.conf

and changed this line:

host  all  all  127.0.0.1/32  md5

to:

host  all  all  0.0.0.0/0     md5

then:

$ sudo vi /etc/postgresql/9.3/main/postgresql.conf

and changed this line:

#listen_addresses = 'localhost'

to:

listen_addresses = '*'

I still couldn't connect remotely, since I got a password error the user I created for the database, so I had to set that by running:

nyc=#  alter user [user] with password '[password]';

After that I could connect remotely to the server!