PostGIS
If you couldn't tell from the title, I'm going to talking about my exploration of PostGIS to use geospatial data in analysis.
I wanted to see how I could merge my GIS background with my new programming skills. Here's the thing: the GIS world of code is huge. And there are tons of ways to get more spatial with your programming. Just doing a quick search of geospatial tools used with Ruby on Rails yields tons of libraries, plug-ins, extensions and more. This blog post also shows common technologies included in a spatial stack.
I chose to dip my toesies into PostGIS because it's an extension of PostgreSQL, something I am very familiar with. There is also a great Boundless tutorial on PostGIS I could easily follow. However, their OpenGeo Suite is no longer free, so I used Rails to manage my data. First, I installed PostGIS using Homebrew. Then, I changed my default adapter in the database.yml file:
default: &default
adapter: postgis
encoding: unicodeAnd I'm set up with PostGIS!
Next, I loaded shapefiles into my PostGIS database. BUT before I could do that, I had to actually find the shapefiles. Census data is great, but the factfinder.census.gov is not the easiest to navigate. I ended up going to my state's website to download the census shapefiles with demographic data. The Colorado Department of Health & Environment also has an amazing user interface for finding all kinds of shapefiles.
After I downloaded my shapefile, which is actually a collection of typically four related files with the same name and different extensions (.shp, .shx, .dbf, .prj), I inserted it into the data directory of my geo_playground Rails app. To load the data from the shapefile into my PostGIS database, I used the following command:
shp2pgsql -I -s 4269 ./db/data/cities/Colorado_City_Boundaires.shp cities | psql -U postgres -d geo_playground_development-I creates a spatial index for faster performance
-s 4269 is the system reference id (SRID) which indicates the projection of the data. To find the SRID for a shapefile, open the .prj file and copy its contents into this nifty service.
The shp2pgsql command converts the shapefile into a series of SQL commands, which is then piped to the psql command which executes the SQL and actually loads the data into the database.
Just a note: the first time I tried running this command, I ran into the error:
psql: FATAL: role "postgres" does not existLike most of my troubleshooting solutions, there was something wrong with my path. I used this StackOverflow post to remedy it.
Finally, I hopped into my rails db and start playing around with SQL queries using spatial joins!
Here are additional resources I found interesting / useful in my spatial programming exploration:
