MUSA 620: Spatial Query Assignment

[galkamax]

@MUSA-620-Fall-2017/musa-620 In preparation for next class, you should have two items installed.

1. Postgres / PostGIS. Most of you should already have this set up from last class.

2. Qgis (an open source alternative to ArcGIS). We will be using Qgis to import the data into our database. You can download it here: http://www.qgis.org/en/site/

The next required assignment is posted here: https://github.com/MUSA-620-Fall-2017/MUSA-620-Week-6

It is not due until the first class after break (March 15th), but it is a challenging one, so I recommend not waiting until the last minute to get started.

Max

[galkamax]

@MUSA-620-Fall-2017/musa-620 Assignment update: In addition to adding some clarification, I've also simplified the assignment by removing the piece about alcohol as a contributing factor.

You can find the updated description here

Enjoy the break!

[LaurenPR]

Just an FYI, if anyone else was looking to try to include the alcohol analysis back into the assignment you may find the following FARS metadata useful (location was not obvious on their site): https://crashstats.nhtsa.dot.gov/#/DocumentTypeList/6 (it is the FARS Analytical Users Manual, third one down)

[gtaves]

Has anyone found metadata for the cleaned traffic fatalities data? I'm having difficulty writing the first query to select the fatalities in Philadelphia. I'm trying to select data points with a specific state and county code (as is listed in the traffic fatalities csv) for the query. Any and all help is much appreciated!

[aaronxsu]

Hi @gtaves , I am not sure if you have found the solution yet. But my approach was to use the st_within syntax provided by postgis to select points within Philadelphia boundary. The documentation is here.

I am sure there are other solutions as well. But hope this helps.

[galkamax]

@gtaves The method I had in mind was what @aronxoxo describes: using ST_WITHIN and the Philly boundary file. Though your method should work just as well. The state and county fields are FIPS codes -- same as the ones used in Census data.

[robmarie]

I found that postgres wouldn't let me add the philly boundary file to the database at all, so I couldn't use ST_Within on it, is that the same problem that anyone else is having? I tried reinstalling postgres to see if I missed the geographic part of the install but it still isn't working.

[clairedouglass]

@robmarie I'm having a similar issue. When I try to import any vector layer to the database I get an error (Error 3). Is that the same problem you were having? Were you able to come up with a solution? I've also tried reinstalling, and installing different versions of postgres but have had no luck.

[galkamax]

@MUSA-620-Fall-2017/musa-620 I understand some people are having issues importing spatial layers into the database. If the error message you're getting is "no function matches the given name and argument types" referencing the function AddGeometryColumn, that means PostGIS is not properly set up for the database.

You should be able to fix it by going into PGAdmin and adding the PostGIS extension here:

Once it's added, you should see "AddGeometryColumn" in your functions table.