Assignment: Storing Geographic Data in a Multi-CRS Table

File-based spatial data formats, such as shapefiles, can only store the geometries using a single CRS. Using PostGIS opens up the possibility of storing each feature’s geometry in a different CRS. One use case would be to store each feature in a locally appropriate coordinate system.

For example, the United States covers a large area. In storing data for the United States, you have to choose between using a continental projection (or lat-long coordinates) that would be inappropriate for urban and regional mapping, or choosing locally appropriate projections and transforming to a continental projection when mapping at that scale. In a file-based data format, each of those local projections would need to be in a separate file. In PostGIS, they can all be in one table, and transformed when necessary to a common CRS.

Pennsylvania has two State Plane zones, North and South. For this exercise, you will load the county data from Census layers on the Kropotkin server into a multi-CRS table, and use it in QGIS.

  1. Download the United States counties shapefile from the Census: https://www2.census.gov/geo/tiger/GENZ2016/shp/cb_2016_us_county_500k.zip. Upload it to your localhost PostGIS server.
  2. Using SQL, delete all counties that are not in Pennsylvania.
  3. Remove the SRID from the geometry column* of the table with the following SQL statement: 
    ALTER TABLE scratch.<table>
ALTER COLUMN geom TYPE geometry(MultiPolygon);
  4. Transform the geometry of each county based on whether it falls in Pennsylvania State Plane North or State Plane South. A list of which counties fall in which zone is available at https://gist.github.com/fitnr/10795511. If you are on Temple campus, you can also refer to the table public.county_spzone on Kropotkin.
  5. Add this data to QGIS. Note how it appears in the data selection interface. Note whether there are any problems in loading or displaying the data.

To Turn In

  1. A SQL script in one file with the statements that you ran in steps 2, 3, and 4. (I know I gave you the statement in step 3, but include it in your script anyway. We are trying to get in the habit of keeping the complete workflow in one script file.)
  2. A document with your observations from step 5, and a discussion of the pros and cons of storing geometries with different SRIDs in one database table.