This semester, my peers Aaron Eubank, Bryce Stouffer, Samuel Watson, Wei Hong Loh, and I took on a directed study under our professor Dr. Lyndon Estes on the implementation and use of PostgreSQL/PostGIS. While I had prior experience with SQL Server and some of its spatial functions, I had never used SQL to perform a full range of geoprocessing tasks. I found my workflows to be more reproducible than using ArcGIS Pro/ArcMap and QGIS, and found processing speeds to be faster than R's {sf} package. Our professor noted to us, however, that working on a locally hosted database has become outmoded in the face of cloud computing. He recommended that we learn to set up PostGIS on AWS and learning how to secure it against unauthorized access. I had never used AWS prior to this semester, and didn't know too much about what was involved. As it turns out, use of these cloud services necessitates knowledge of the terminal/command prompt and its associated functions, which I had little prior experience with. By the end of the semester, though, I became much more proficient in its use and how to improve data security. I am even evolving into somewhat of a Linux convert from the experience. But I digress. After uploading data to our new AWS-hosted PostGIS database, we decided to visualize those results in a web map. We found that the use of GeoServer was important for adding an extra layer of security to our data, and improving interoperability as we pushed the data to Leaflet. Given the project's complexity, we found that creating a markdown was helpful for keeping track of each step in our workflow. I recommend that anyone undertaking a similar project for the first time also keeps notes of their progress. Our notes can be found below. And, of course, I would be more than happy to receive questions, comments, or suggestions on our methodology. VM Creation (Elastic Computing 2 - Ubuntu Instance)
3. To install Postgres on the instance for the first time and add users, follow the guide here. 4. To switch to the desired database user, enter the command below: 5. Create the PostGIS spatial extension: VM GUI Installation
Postgres Data Import First Steps (for DB on Windows Local Machine):
First Steps (for DB on AWS EC2 Linux Virutal Machine):
To upload a single file instead of a directory, remove the -r option. Next Steps:
Using psql to import csv files: First, create the table you wish to upload data to: `copy` is a psql command to import csv files: Securing the Virtual Machine and PostgreSQL Database
Accessing the EC2 Postgres Database on PgAdmin
We did the following to grant access:
Data Visualization
Leaflet Visualization
After the Geoserver is in place, we utilized code built upon Leaflets’ library to pull our data from GeoServer and visualize it as a web map. A future posting on GeoServer and Leaflet will be made available, with its use and workflow discussed in further detail. 8/10/2021 UPDATE: The code for our web map can be found here on hackmd.io, along with additional details regarding EC2 security/firewall implementation.
9 Comments
|
Jordan Frey#GIS and #DataScience professional. Hiking, cycling, skiing, outdoors and fitness, chess, Star Trek. Works with #rstats #python #SQL #JavaScript etc. Archives
August 2021
Categories |