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 DatabaseLogging into the VirtualBox VM given to us in class, I ran the following commands:
ggs $ sudo -i(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:
# su - postgres
postgres $ pg_dump -c nyc > nyc.sql
- change ' TO postgres' to ' TO [user]'
- change ' FROM postgres' to ' FROM [user]'
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 - postgresNow I can create the database and import the data:
$ createuser --superuser [user]
$ createdb nycLet me run a query from the lesson to make sure it works:
$ psql nyc < nyc.sql
$ psql nyc
psql (9.3.9)To get the postgres server to except outside connections, I needed to make the following changes to config files:
Type "help" for help.
nyc=# select name from nyc_subway_stations where name like 'Broad%';
$ sudo vi /etc/postgresql/9.3/main/pg_hba.confand changed this line:
host all all 127.0.0.1/32 md5to:
host all all 0.0.0.0/0 md5then:
$ sudo vi /etc/postgresql/9.3/main/postgresql.confand 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!