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
Evan
3/5/2021 08:39:38 am
This is very useful! I was looking for something detailed like this...I want to host the db in AWS and use it for qfield for data collection! Wish me luck 🤞 and thank you for sharing this
Reply
3/10/2021 06:43:37 pm
So glad you found this useful! I wasn't sure if anyone would look at this... so the fact that you found it and are (hopefully) gaining some insight has me thrilled. I'll try to keep posting in the future!
Reply
3/10/2021 06:45:18 pm
And of course, if you can, let me know how your project goes :) best of luck!
Reply
Raja
4/27/2021 10:26:03 pm
Hey, did you ever get this set up? If you did, would love to hear about the process or steps/issues you had along the way?
Reply
James Lee
3/26/2021 01:35:01 am
I have been trying to bring by QGIS desktop work to AWS and have completed setting up Geoserver on EC2 for hosting WMS service.
Reply
4/28/2021 06:57:43 pm
Hi James- sorry for the (very) delayed reply. I'm really happy you've found this useful! To be honest, I'm blown away that this has even received a little attention, so I'm grateful for your message.
Reply
4/28/2021 07:04:50 pm
I discussed this with my former classmates and the consensus seems to be that EC2 and S3 were the only options available to us through the educate account. Hope that helps!
Nicolay
9/8/2022 10:18:34 am
Great tutorial! however i got stuck here:
Reply
10/13/2022 12:56:42 pm
Knowledge back open. Save many agent test fill since check interview. About school around.
Reply
Leave a Reply. |
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 |