Using Metacoding for Philadelphia LandCare Site Manipulation

Posted on by

For my capstone project, I will be working with Pennsylvania Horticulture Society to develop a streamlined data management system for their LandCare Program. The LandCare Program strives to rehabilitate vacant land parcels in the city of Philadelphia to address urban revitalization and land vacancy issues plaguing the city. The image below shows what some of these land areas can look like.

The project has so far consisted of the construction of a database in SpatiaLite to work with the shapefiles for the project, see the image below for an overview of the GUI. I selected SpatiaLite as it uses the SQLite language and connects seamlessly with ArcMap. This ultimately allows the user to create an manipulate geodatabases using code written in SpatiaLite and immediately access the results in ArcMap.

My first sprint (May 30th to June 8th) consisted primarily in the set up of documentation for the project, in addition to the following items: creating a geodatabase, working to remove sites that have new uses (no longer PHS LandCare sites) as well as writing base code for updating tables based on an excel.

The process to create documentation was decided upon so formal directions would be available for anyone working on this project in the future. In addition to this, I have detailed notes of what I have done thus far and can change information as the project progresses.

Most of the coding that has been done thus far has been metacoded in excel. By doing this, the overall update process is now streamlined to where someone with no SQL experience can use the excel and get valid results. Using the image below, an example to delete a row from a table in SpatiaLite, it is visible that the user only must input a small amount of information such as the table name and the identifier for the row being deleted. This will benefit not only the inexperienced coder in the future, but also anyone who must update many files at once as only one column must be manipulated. The end result will be to gain statements that can be copied and pasted in the query window of SpatiaLite.

So far, there are metacoding files for updating new use sites and updating columns overall. When updating new use sites, we must copy information from both parcels (individual lots) and sites (many lots together) to develop one overarching row of data. This process was broken down into four steps, two with updates and two for deleting the land area from the new parcel and site tables as they are no longer in the maintenance database. The individual update metacoding was created for a quick update like square feet. Using this process, much information can be quickly added to the pre-written code and then executed in SpatiaLite.

Finally, in sprint two, the next process will be determining the process to update parcel and site data for address changes. As many sites may lose a parcel or gain a parcel over the course of the year for maintenance purposes, it is important that adequate files are maintained on current geometries. This will most likely be completed again with many steps in the form of a metacoded file.

Find the best matched polygons for use in a time series using pgAdmin

Posted on by

Goal:
Find the best matched polygons in a time series using a PostGIS Spatial Database
Background:
For previous project a comparison of housing data from the 1930s to current and historic US Census data was required. The geographic portion of the census data, was obtained from the National Historical Geographic Information System (NHGIS) which produces downloadable shapefiles with a GISJOIN field allowing for convenient linking of the shapefile to tables of census data also available through NHGIS. The 1930s housing data was in the form of an image of a 1937 Home Owners’ Loan Corporation (HOLC) map of Philadelphia available here. Very broadly the HOLC graded neighborhoods based on race/class and these grades where then used to determine the likelihood of housing loan assistance. There is debate if the HOLC process normalized race-based home lending practices and then the lead to further housing issues such as redlining. more info.

Process:
The HOLC map data was pulled into a GIS and a shapefile was created of the neighborhood grading (this process is often listed as ‘Heads-up Digitizing’ in job postings) here is a handy tutorial for ArcMap and another using QGIS. Shapefile and HOLC jpeg available on github.

The HOLC neighborhood grading schema does not coincide with any current (or historic) census arrangement so the spatial querying capabilities of pgAdmin and the PostgreSQL database engine was used to match the newly created HOLC digital data with the NHGIS Decennial Census geographic data.
Create a PostGIS Spatial Database and use pgShapeLoader to load the shapefiles (HOLC and NHGIS spatial data)

After much trial and error the following code worked the best:

create table overlap2010census as
with limited as(
select distinct us2010.*
from us2010, holc1937
WHERE ST_DWithin(us2010.geom, holc1937.geom, 0.25)
order by us2010.gid
)
/* the section above limits the query of the 2010 census polygons (Entire US) to those near the HOLC polygons –input can vary based on projection of files*/

SELECT DISTINCT ON (limited.gid)
limited.gid as us2010a_gid,
holc1937.gid as holc_gid, holc1937.name, holc1937.grade,
st_area((st_transform(limited.geom,4326)::geography)) as area_check,
ST_Area(ST_Intersection((st_transform(limited.geom,4326)::geography),
(st_transform(holc1937.geom,4326)::geography))) as intersect_area_2010_meters,
(ST_Area(ST_Intersection((st_transform(limited.geom,4326)::geography),
(st_transform(holc1937.geom,4326)::geography)))/
st_area((st_transform(limited.geom,4326)::geography)))*100 as percent_overlap,

/*calculates the area of the returned census and HOLC polygons and the percent of overlap */

limited.*
FROM limited, holc1937
where limited.geom && ST_Expand(holc1937.geom,1) and ST_Area(ST_Intersection(limited.geom, holc1937.geom)) >0

/* joins the files where the geom overlap*/

ORDER BY limited.gid, ST_Area(ST_Intersection((st_transform(limited.geom,4326)::geography),
(st_transform(holc1937.geom,4326)::geography))) DESC;

/* sorts by the largest amount of overlap, and in conjunction with the DISTINCT ON call
only returns the first instance of the census polygon that overlaps the target polygon */

While this method requires the user to update and repeat this code for each comparison the tables created can then be exported out to your GIS or QGIS can connect directly to your database if you prefer that option. As the query also created a unique identifier for each year and polygon of the Census data, a crosswalk/key table was created using this data which allowed for the Census data (race,income housing status etc.) for multiple years to be attached via the NHGIS GISJOIN field previously mentioned. Based off of the multiyear Census data attached via the crosswalk table comparisons of the HOLC geographic areas could then be made regardless of shifting Census borders.

Courses Offered Fall 2017

Posted on by

The following courses are being offered Fall 2017. All times are 5:30pm to 8pm. Please refer to Banner for more information.

Monday

  • GUS 5062 – Fundamentals of GIS
  • GUS 8065 – Cartographic Design (required)

Tuesday

  • GUS 5062 – Fundamentals of GIS
  • GUS 5065 – Urban GIS (elective)
  • GUS 5069 – GIS for Health Data Analysis (elective)
  • GUS 5161 – Statistics for Urban Spatial Analysis

Wednesday

  • GUS 8067 – Spatial Database Design (required)

Thursday

  • GUS 5063 – Remote Sensing (elective)
  • GUS 5068 – Census Analysis with GIS (elective)

Friday

  • GUS 9187 – GIS Capstone (required, does not meet every week)

Courses Offered Spring 2017

Posted on by

The following courses are being offered Spring 2017. All times are 5:30pm to 8pm. Please refer to Banner for more information.

Monday

  • GUS 8068 – Web Mapping and Map Servers (elective)

Tuesday

  • GUS 5062 – Fundamentals of GIS
  • GUS 8066 – Application Development (required)

Wednesday

  • GUS 5062 – Fundamentals of GIS
  • GUS 5063 – Remote Sensing (elective)
  • GUS 5066 – Environmental Applications of GIS (elective)
  • GUS 9187 – GIS Capstone (required, does not meet every week)

Thursday

  • GUS 5073: Geovisualization (elective)
  • GUS 5065: Urban GIS (elective)

Courses Offered Fall 2016

Posted on by

The following courses are being offered Fall 2016. All times are 5:30pm to 8pm. Please refer to Banner for more information.

Monday

  • GUS 5062 – Fundamentals of GIS
  • GUS 8065 – Cartographic Design (required)
  • GUS 9187 – GIS Capstone (required, does not meet every week)

Tuesday

  • GUS 5062 – Fundamentals of GIS
  • GUS 5068 – Census Analysis with GIS (elective)
  • GUS 5161 – Statistics for Urban Spatial Analysis

Wednesday

  • GUS 8067 – Spatial Database Design (required)

Thursday

  • GUS 5000 – Remote Sensing (elective)
  • GUS 5067 – GIS and Location Analysis (elective)