PostGIS Directed Study Final Project Documentation

by Aaron Eubank, Bryce Stouffer, Sam Watson, Wei Hong Loh, and Jordan Frey

Introduction

This semester of work on "PostGIS" has in practice been a deep dive into the world of relational databases, server configuration, cloud computing (and security) and web-mapping.

The documentation here will take you through the process of creating a Postgres Server on an AWS EC2 instance (and how to spin up that instance), and the process we followed to create a front end web map using data from the Ramani Huria project, an effort of the Humanitarian OpenStreetMap Team in Tanzania, where Aaron interned this summer.

We collaborated heavily on this project and team members contributed at all points, but some of the heavier lifting on certain sections was as follows:

EC2 setup and AWS security research/implementation: Aaron
Geoserver setup and roadmap for web map: Bryce
Data upload and manipulation on virtual machine: Jordan
Web map realization and styling: Sam, Wei Hong, Jordan

Virtual Machine Creation (Elastic Computing 2 - Ubuntu Instance)

Opened Amazon Educate account
- Not strictly necessary, but gives you $40 of free credits (per month)
Opened AWS Account
Started here at the Management Console.
- Clicked EC2
On the EC2 page:
- Clicked Launch Instance
Choose and AMI (Amazon Machine Image) here
- We went with an Ubuntu instance (Free Tier Eligible)
- Several other options including some stock Amazon Linux instances. Not sure how different they would be to start up. May want to try with a different one at some point.
- Clicked Select by the Ubuntu AMI
Choose Instance Type here
- Tons of different options with different computing power for each AMI you choose which are optimized for different things. Many of Lyndon's Instances are t3.2xlarge ($0.1664 rate) (I think).
- Micro Instance the only one available for Free Tier, which will be fine for getting started.
- We'll want to scale up to a heavier duty machine with more storage and computing power after we develop the concept here.
- But we need to sort out if we can scale up an existing instance or need to migrate it over to a higher tier
Click Review and Launch
- There are several things to customize if not using the Free Tier including storage, tags and a few other things.
Instance should now be running. Check for it on the dashboard here
- 750 hours of use per month for the first 12 months
Create a Key Pair for the instance in order to access the instance through an SSH or Remote Desktop
- Don't really remember exactly where this step came, but I was prompted to create it
- Here is where you can view the key pairs and create new ones.
- Need to figure out if it is better to have a different key pair for each user or if it's ok to all use the same one and share a .PEM file.

Setting up the Virtual Machine & User Creation on VM

Logged in as ubuntu default user.
- Tried to log in as root but did not let me, which is probably good for security
- Created user aaron with superuser privileges

This article gives info on setting up new users on an ubuntu instance.

This one deals with access keys for users. Best practice is to use unique keys for each user.

Virtual Machine Connection

This article takes you through the process of connecting to an instance using the .PEM file

After user account creation, users can connect to the Ubuntu Instance through the use of command prompt and PEM file.

.PEM file must

ssh -i path-to-pem-file youruser@ec2-*-**-***-**.us-****-2.compute.amazonaws.com

after connecting to the instance, user can access postgres database with…

youruser@ip-172-31-22-27:~$ psql ramanihuria youruser

Installing PostgreSQL and Creating a Database on Linux EC2 Instance

Although we had done this several times throughout the semester and knew how to install Postgres and PostGIS, this post is a good how-to on this.

Once Postgres was installed we created our database and users in psql:

First, we switched to the postgres user on the linux machine by typing sudo -i -u postgres

createuser --interactive --pwprompt 
...
Enter the name of the role to add: <username>
Enter password for the new role: <pw>
Enter it again: <pw>
Shall the new role be a superuser? (y/n) y -- we said yes
...
postgres@ip---------

Switch to the postgres user in psql:

psql postgres
...
postgres=#

Create database:

postgres=# CREATE DATABASE ramanihuria
...
CREATE DATABASE

Grant user permissions to new database:

postgres=# GRANT ALL PRIVILEGES ON DATABASE ramanihuria TO <username>
...
GRANT

Then, we need to create our spatial extensions for the database. This is super easy to forget (since we've already installed PostGIS), but it will leave you scratching your head as to why your spatial layers won't work if you don't!

So we can now log into the database from the command line in psql by typing:

psql ramanihuria <user>

Create the spatial extension for PostGIS:

ramanihuria=# CREATE EXTENSION PostGIS
...
CREATE EXTENSION

Virtual Machine GUI Installation

Note: We thought that having a GUI of our virtual machine would be necessary for interacting with our data so that we didn't have to open up the ports to external connections, but we quickly found that it was not. The command line was perfectly adequate for setting up all of our virtual machine software and processes, and we were able to securely open the 5432 port so that we could upload/download and otherwise interact with our data from external devices. We're keeping this in here though since we spent some time figuring it out.

Edited the security group settings for the instance to include RDP import access port 3389 (on the AWS account online).

To connect: open Remote Desktop Connection and enter "ec2-3-19-241-93.us-east-2.compute.amazonaws.com" in the computer box. For linux, use VNC viewer.

Important consideration for GUI use: For free tier AWS instance, CPU credits are limited to about 144 credits for the machine, which are regenerated every 24 hours (6 credits per hour). GUI use seems to monopolize CPU usage and may not be useful to our needs after all. More info about this here.

Securing the Virtual Machine and PostgreSQL Database

This Helpful Article on security. Helped us set up a Firewall on our EC2 instance while only allowing our specific ip addresses through them.

To do this we used the following commands:

sudo ufw status - To check the status the firewall.

This was the output:
Status: inactive

sudo ufw enable - To enable the firewall.
Output:

Command may disrupt existing ssh connections. Proceed with operation (y|n)? y 

Firewall is active and enabled on system startup

sudo ufw allow from [client-ip] to any port 5432 - to add an ip address
Output:

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere                  
5432                       ALLOW       [client-ip]           
OpenSSH (v6)               ALLOW       Anywhere (v6)

Repeat for each ip address that needs access.

Accessing the EC2 Postgres Database on PgAdmin

This article was very helpful in the second step of getting IP addresses specifically set up for use of the database with Postgres.

To keep security strong, we elected to add only the ip addresses we will be using as the allowable ip addresses to access the database.

We did the following to grant access:

Augment the pg_hba.conf file to change the allowable ip addresses.

cd /path/to/pg_hba.conf
...
sudo nano hb_hba.conf

Insert the following line for each ip address needed:

host    <dbname>        <pg username>         <ip address>/xx       md5

Example:

host    ramanihuria     aaron                 140.232.11.49/23      md5

After making the changes, restart the postgres server with the following command:

sudo systemctl restart postgresql.service

Note: We had an error where the database was not starting up after changing the pg_hba.conf file. The error arose because we did not include a /xx notation at the end of an ip address and the invalid IP was preventing the server from starting up.

Postgres DB Data Import

First Steps (for DB on Windows Local Machine):

Using ogr2ogr to import spatial data:

Open OSGeo4W Shell, which comes with QGIS
Change the terminal/shell directory to the location of your Postgres folder

cd C:\Program Files\PostgreSQL\11

Using PostGIS Shapefile Import/Export Manager (GUI):

Download the PostGIS Shapefile and DBF loader (you can download the GUI when you download PostgreSQL)
After download, launch the software and set up your postgis connection: username, password, server host, port, and database.

Once it is connected, then upload the data, in shapefile format, to the PostGIS GUI, with specified SRID.

Note: You can import the data in batch, and this data transfer step can be delivered in QGIS as well.

First Steps (for DB on AWS EC2 Linux Virtual Machine):

Copy local files onto AWS EC2 Linux virtual machine

scp -i path\to\pem\file -r C:\Users\username\directory\of\files user@ec2-xx-xxx-xx-xx.us-east-2.compute.amazonaws.com:~/path/to/save/location

To upload a single file instead of a directory, remove the -roption.

Next Steps

Use the ogr2ogr command to upload vector spatial data into the database. This can be used for a multitude of file types, but the general structure of the code stays the same (see that the code chunks below are almost identical). Note that -lco precision=NO is often necessary when importing shapefiles, which contain field size limits. If uploading data to to postgres that has been copied to the virtual machine, you must now connect to EC2 instance prior to following the next steps. Instructions on connecting to EC2 are found earlier in this markdown.
- For uploading Shapefiles:
```
ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=postgres password=PASSWORD port=5432" FILE_PATH.shp -overwrite -lco precision=NO -lco GEOMETRY_NAME=geom -nln "NAME_OF_NEW_TABLE"
```
- For uploading GPX files:
```
ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=postgres password=PASSWORD port=5432" FILE_PATH.gpx -overwrite -lco GEOMETRY_NAME=geom -nln "NAME_OF_NEW_TABLE"
```

Notes:
- If you encounter an error, download GDAL from https://gdal.org/download.html and paste the folder into C:\Program Files\PostgreSQL\11\bin, then try the above steps again.

Using psql to import csv files:
1. First, create the table you wish to upload data to:

CREATE TABLE schema.table_name (
column_name1 text,
lat double precision,
lon double precision
);

copy is a psql command to import csv files

copy ch01.restaurants_staging
FROM 'CSV_file_path.csv' 
DELIMITER as ',';

Data Visualization

To visualize our data stored in the Postgres database, we chose to use a combination of Geoserver and Leaflet.

The data consists of five vector layers pertaining to flooding and local governance in Dar es Salaam.

GeoServer Set-up

Initial GeoServer was set-up on our local machines following this guide and this guide.
After starting the GeoServer (see guide above), we navigated to the GeoServer landing page (acessed by typing "http://localhost:8080/geoserver/web/" in our browser)
We created a new workspace by clicking on Workspaces → Add new workspace. we called it something like "ramani_huria" and gave it any url (it does not have to be real). We also checked to make it the Default Workspace. After it is created, go to Edit workspace and make sure the “WFS” option is checked.
We added a connection to our Postgres database by creating a new "Store", then adding our ec2 and database parameters as below.

Note that you must use a valid postgres username and password for this step, so use one you created in the "Installing PostgreSQL and Creating a Database on Linux EC2 Instance" section above.

Load in all the layers from your Postgres Database to GeoServer so that it can read everything. To do this click:

Layers → Add new Layer → Add layer from: ramani_huria:rh_aws (or “name_you_gave_to_the_workspace:name_you_gave_to_the_store”)
You should now see the names of all the layers in the Postgres database (there should be 6 in total).
Click on each one, give it a short title, make sure the SRS is “EPSG:4326” for everything and generate the bounding boxes by clicking Compute from data, then Compute from native bounds.
Click the Publishing tab, then make sure the default style matches the type of data.
Click save to save the layer

Make sure the layers have loaded correctly by going to the Layer Preview section on the left of the screen.

Find one of the layers and click the OpenLayers link. If the layer pops up in a new tab, then the connection is working and the layers will load to the webmap!

Leaflet Visualization

After the Geoserver is in place, we utilized code built upon Leaflet's library to pull our data from Geoserver and visualize it as a web map.

To do this, we placed the .html file containing the code for the webmap in the www file of the geoserver. For linux this defaults to /usr/share/geoserver/geoserver-2.16.1-bin/geoserver-2.16.1/data_dir/www.

We used this tutorial for support.

Here are some screenshots of our final product.

We did not ultimately deploy this webmap online due to lack of time and security concerns, but plan to do this to ultimately make the webmap operational to the public.

Web Map Code

The code here was developed using several online resources and collective knowledge of Leaflet.

<!DOCTYPE html -->
<html>
<head >
<title>Dar es Salaam Drain Segments</title>
<!--Load the style stylesheet of leaflet -->
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.1/dist/leaflet.css" integrity="sha512-Rksm5RenBEKSKFjgI3a41vrjkw4EVPlJ3+OiI65vTjIdo9brlAacEuKOiQ5OFh7cOI1bkDwLqdLw3Zg0cRJAAQ==" crossorigin=""/>
<!--Load leaflet -->
<script src="https://unpkg.com/leaflet@1.3.1/dist/leaflet.js" integrity="sha512-/Nsx9X4HebavoBvEBuyp3I7od5tA0UzAxs+j83KgC8PU0kgB4XiK4Lfe4y4cgBtaRJQEIFCW+oC506aPT2L1zw==" crossorigin=""></script>
<!--Load Legend Plugin-->
<link rel="stylesheet" href="../css/leaflet.wmslegend.css"/>
<script src="../js/leaflet.wmslegend.js"></script>
<style>

/*Set the dimensions of our map and elements*/
.map {
  height: 100%;
  width: 100%;
}
.info {
  padding: 6px 8px;
  font: 14px/16px Arial, Helvetica, sans-serif;
  background: white;
  background: rgba(255,255,255,0.8);
  box-shadow: 0 0 15px rgba(0,0,0,0.2);
  border-radius: 5px;
}
.legend {
  padding: 6px 8px;
  background: rgba(255, 255, 255, 0.8);
  line-height: 25px;
  color: #555;
  width: auto;
}
.legend h4 {
  text-align: left;
  font-size: 13px;
  margin: 4px 0 0;
  color: #777;
}
.legend i {
  width: 18px;
  height: 18px;
  margin: 0 8px 0 0;
  float: left;
  opacity: 0.5;
}

</style>
</head>
<body>
<!--Load ajax and jquery for geojson-->
<script
src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js">
</script>
<!--Create our map object -->
<div id="map" class="map"></div>
</body>
</html>
<script>
// Find our map id
var map = L.map('map')
// Set open openstreetmap
//var basemap = L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png')
//map.addLayer(basemap);

// add the OpenStreetMap tiles Basemap
var basemap = L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
       maxZoom: 19,
       attribution: '&copy; <a href="https://openstreetmap.org/copyright">OpenStreetMap Contributors</a>'
     }).addTo(map);

// Add Mapbox Basemap
var basemap1 = L.tileLayer(
    'https://api.mapbox.com/styles/v1/sam-zwatson254/ck3vs3tu40je01cr2ha4pfpwf/tiles/256/{z}/{x}/{y}@2x?access_token=***********************', { 
// Contact authors for info on how to get an access code
        tileSize: 512,
        zoomOffset: -1,
        attribution: '© <a href="https://apps.mapbox.com/feedback/">Mapbox</a> © <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>'
    });

var subwardsStyle = {
    "color": "#ff7800",
    "weight": 1,
    "opacity": 0.45
};
var dar_new_subwards = new L.GeoJSON();
            function handleJson(data) {
            	//alert(data);
                console.log(data);
               dar_new_subwards.addData(data).setStyle(subwardsStyle);
               dar_new_subwards.bindPopup('<h1>'+dar_new_subwards.param.srsName);
               map.addLayer(dar_new_subwards);
            }
            var rootUrl = 'http://localhost:8080/geoserver/wfs?service=wfs&version=1.0.0&request=GetFeature&typename=ramani_huria:dar_new_subwards&outputFormat=json&format_options=callback:handleJson&srsName=EPSG:4326';
             $.ajax({
                url: rootUrl,
                dataType: 'json',
                jsonpCallback: 'handleJson',
                success: handleJson
            });
var shinaStyle = {
    "color": "#B04173",
    "weight": 1,
    "opacity": 0.45
};
var dar_shina = new L.GeoJSON();
            function handleJsonshina(data) {
            	//alert(data);
                console.log(data);
               dar_shina.addData(data).setStyle(shinaStyle);
               dar_shina.bindPopup('<h1>'+dar_shina.param.srsName);
               map.addLayer(dar_shina);
            }
            var rootUrl = 'http://localhost:8080/geoserver/wfs?service=wfs&version=1.0.0&request=GetFeature&typename=ramani_huria:dar_shina&outputFormat=json&format_options=callback:handleJsonshina&srsName=EPSG:4326';
             $.ajax({
                url: rootUrl,
                dataType: 'json',
                jsonpCallback: 'handleJsonshina',
                success: handleJsonshina
            });
var wardsbyshinaStyle = {
    "color": "#80808",
    "weight": 1,
    "opacity": 0.45
};
var dar_wards_by_shina = new L.GeoJSON();
            function handleJsonwardsbyshina(data) {
            	//alert(data);
                console.log(data);
               dar_wards_by_shina.addData(data).setStyle(wardsbyshinaStyle);
               dar_wards_by_shina.bindPopup('<h1>'+dar_wards_by_shina.param.srsName);
               map.addLayer(dar_wards_by_shina);
            }
            var rootUrl = 'http://localhost:8080/geoserver/wfs?service=wfs&version=1.0.0&request=GetFeature&typename=ramani_huria:dar_wards_by_shina&outputFormat=json&format_options=callback:handleJsonwardsbyshina&srsName=EPSG:4326';
             $.ajax({
                url: rootUrl,
                dataType: 'json',
                jsonpCallback: 'handleJsonwardsbyshina',
                success: handleJsonwardsbyshina
            });

// Style for Drainage Lines
function subteStyle(feature) {
  var colorToUse;
  var depth = feature.properties.depth;

  if (depth <= .5) colorToUse = "#00FF00";
  else if (depth <= 1.50) colorToUse = "#FECC00";
  else if (depth >= 1.51) colorToUse = "#FF0000";
  else colorToUse = "#000000";

  return {
    "color": colorToUse,
    "weight": 3
  };
}
var rh_drain_segments = new L.GeoJSON();
            function handleJsondrainsegments(data) {
            	//alert(data);
                console.log(data);
               rh_drain_segments.addData(data).setStyle(subteStyle);
               rh_drain_segments.bindPopup("Drain Segment");
               map.addLayer(rh_drain_segments);
            }
            var rootUrl = 'http://localhost:8080/geoserver/wfs?service=wfs&version=1.0.0&request=GetFeature&typename=ramani_huria:rh_drain_segments&outputFormat=json&format_options=callback:handleJsondrainsegments&srsName=EPSG:4326';
             $.ajax({
                url: rootUrl,
                dataType: 'json',
                jsonpCallback: 'handleJsondrainsegments',
                success: handleJsondrainsegments
            });

///toggle set up for Final Map
var baseMaps = {
    "OpenStreetMap": basemap,
    "Mapbox": basemap1
};

var overlayLayers = {
	"New Subwards": dar_new_subwards,
	"Shina": dar_shina,
	"Wards By Shina": dar_wards_by_shina,
	//"Drain Points": rh_drain_points,
    "Drain Segments": rh_drain_segments
};

L.control.layers(baseMaps, overlayLayers).addTo(map);
// Set our initial location and zoomlevel
map.setView([-6.82, 39.2], 12.25);
var point_city;
dar_new_subwards.on("click", function(e){
  // console.log("clicking on the feature :", e);
  point_city = e;
  subward_name = point_city.layer.feature.properties.Vil_Mtaa_N;
  var city_popup = L.popup()
                    .setLatLng(point_city.latlng)
                    .setContent("<p>Subward Name: " + subward_name + " </p>" + point_city.latlng )
                    .openOn(map);
});

var shina_popup;
dar_shina.on("click", function(b){
  // console.log("clicking on the feature :", b);
  shina_popup = b;
  mjumbe = shina_popup.layer.feature.properties.mjumbe;
  var shina_pop = L.popup()
                    .setLatLng(shina_popup.latlng)
                    .setContent("<p>Local Leader: " + mjumbe + " </p>" + shina_popup.latlng )
                    .openOn(map);
});

var ward_popup;
dar_wards_by_shina.on("click", function(c){
  // console.log("clicking on the feature :", c);
  ward_popup = c;
  ward_name = ward_popup.layer.feature.properties.Ward_Name;
  var shina_pop = L.popup()
                    .setLatLng(ward_popup.latlng)
                    .setContent("<p>Ward Name: " + ward_name + " </p>" + ward_popup.latlng )
                    .openOn(map);
})

/*Add Legend*/
var legend = L.control({ position: "bottomleft" });

legend.onAdd = function(map) {
  var div = L.DomUtil.create("div", "legend");
  div.innerHTML += '<i style="background: #ff7800"></i><span>Wards</span><br>';
  div.innerHTML += '<i style="background: #B04173"></i><span>Shina (Hyperlocal Admin Divisions)</span><br>';
  div.innerHTML += '<i style="background: #808080"></i><span>New Subwards</span><br>';
  div.innerHTML += "<h4>Drainage Segment Depth</h4>";
  div.innerHTML += '<i class: line style="background: #00FF00"></i><span>Shallow</span><br>';
  div.innerHTML += '<i style="background: #FECC00"></i><span>Medium</span><br>';
  div.innerHTML += '<i style="background: #FF0000"></i><span>Deep</span><br>';

    return div;
};

legend.addTo(map);
</script>

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`	在筆記中貼入程式碼
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.