Creating the Database

It's kind of a long process. If you can develop without downloading the database, I recommend it.

PostgreSQL

First, install Postgres. With homebrew:

$ brew install postgresql

And configure it to start any time you start your computer (follow the given prompts).

The default username and password are postgres and postgres. You probably don't need to change it on your local machine but the database team should on the production machine.

The Database Script from Tim

Download Tim's DB dump: the barranquilla.gov.co.zip file from BlackBoard. Unzip it:

$ cd ~/Downloads
$ unzip barranquilla.gov.co.zip
$ cd database

The createDatabase_lasFlores.sql is the database dump. You can use the instructions in the copySQL.txt file to copy in the data stored in the *.data files. Unfortunately, it's not that simple. You need to install an extension before you can do anything.

PostGIS

Postgis is an extension for a Postgres database that allows location data. You can try to find the download here. And follow these instructions here, but it's really much simpler with a package manager. The database team will probably figure out how to do it on the production machine. I don't know about Windows, but MacOS can install it with:

$ brew install postgis
$ psql <name>
<name>=# CREATE EXTENSION postgis;
<name>=# \q

Where <name> is the name of your database user. You can use the default postgres user or you can use your username for your computer. Mine is michael. You can see yours by doing this:

$ psql

The user it logs you in as is your user. If you get an error for running that, use just:

$ createdb

With no arguments, psql will make a database for your username. You can see all your databases and users like this:

$ psql postgres
postgres=# \du
postgres=# \list
postgres=# \q

Where \du gives the users and their permissions and \list gives the databases.

Creating the lasflores Database

Now you're ready to add the database stuff from Tim. Go ahead and do that with this:

$ cd ~/Downloads/database
$ psql -U <username> -d lasflores -f createDatabase_lasFlores.sql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 setval
--------
    223
(1 row)

ALTER TABLE
CREATE INDEX
 setval
--------
   2388
(1 row)

 setval
--------
   1465
(1 row)

ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX

Where <username> is the postgres user you set up with postgis. For example, I set mine up under the michael user, so my command was:

$ psql -U michael -d lasflores -f createDatabase_lasFlores.sql

Copying in the Example Data

If you got an output like that, you're doing great so far. Now we can add some example data from the *.data files.

The copySQL.txt file is great if you know SQL but there're a few small bits you'll probably get caught on. First, SQL has a weird uppercase and lowercase pattern. If you need a mix of uppercase and lowercase, it's better to use quotes. Also, paths should be absolute (from root). We can create a new file that imports the required files like this:

$ cp copySQL.txt copySQL.sql

Edit the file with your editor of choice. Replace each instance of <file> with the absolute path to the appropriate files. In the end, mine looks this:

COPY construccion (id, geom, "OBJECTID", "CODIGO", "TERRENO_CODIGO", "TIPO_CONSTRUCCION", "TIPO_DOMINIO", "NUMERO_PISOS", "NUMERO_SOTANOS", "NUMERO_MEZANINES", "NUMERO_SEMISOTANOS", "ETIQUETA", "IDENTIFICADOR", "CODIGO_EDIFICACION", "CODIGO_ANTERIOR", "SHAPE.AREA", "SHAPE.LEN", wkt) FROM '/Users/michael/Downloads/database/construccion.data';

COPY terreno (id, geom, "OBJECTID", "CODIGO", "CODIGO_ANTERIOR", "SHAPE.AREA", "SHAPE.LEN", wkt) FROM '/Users/michael/Downloads/database/terreno.data';

COPY workshop_20170210 (id, geom, codigo, fuente, wkt) FROM '/Users/michael/Downloads/database/workshop_20170210.data';

If you're on a Windows machine, you can use the absolute path from the drive like this: C:\Users\... in place of all the /Users/....

Put it into the DB with this:

$ psql -U <username> -d lasflores -f copySQL.sql

And there you go, now you have all the data.