This past semester, through my remote sensing course, I had the opportunity to analyze land changes that result from the construction of highways. Specifically images from 1972 and 2016 of route 476 were analyzed using remote sensing software. The land analyzed was tested for changes in urban spaces, grass, forest, and water. These for areas allowed for the best analysis with the most understandable results. The original composite images (bands 4, 5 and 7) are shown below prior to any testing or remote sensing analysis.

Highway 467 was the primary area of interest for this analysis as the construction in the Philadelphia area was regarded as highly controversial. Construction of the highway took many years, and removed many homes, trees, and other green space. This analysis sought results showing the amount of green space removed for construction purposes. This area would have been replaced with urban space.

Results were calculated using some ArcMap analysis but primarily ENVI software was used. All final maps were edited for readability and clarity using Adobe Illustrator. The following analysis was performed: a tasseled cap analysis, a supervised land classification using a majority analysis, and land change results were calculated.

Shown below, the classification shows the land in the original state (1972) and the final state (2016). It is evident when viewing this map, that there was some change in land type from grass or forest to urban spaces. While much of this was due to highway construction, it could be interpreted that the highway led to the urbanization of the surrounding suburbs.

The image below is a deeper explanation of the land that changed from forest or grass to the new land it consists of. What seemed interesting with these results were that much of the forest land changed to urban but also grass land. This was also similar with grass land and vice versa. One consideration for this could have been the increased interest in large green backyards. While many new homes were constructed, these homes typically had large sprawling yards. The area analyzed is primarily upper class when median household incomes were considered in this analysis.

Finally, the image below depicts the actual land changes from one category to another when analyzed in square kilometers. As was unexpected, much of the forest and grass land changed from one to the other. Yes, urban space was gained significantly, but the amount of grass and forest retained was entirely unexpected.  Future analysis should consider the type of forest growth (primary, secondary, etc) to determine the overall growth and health of the newly greened land spaces.

The overall results of this project showed that much greenspace was lost to urbanization with the construction of highways, but much land was retained and converted to other types of greenspaces. Future analysis suggests that a consideration of these new greenspaces be tested. While greenery is implemented, the overall health of the area has not been considered. As many plain greenspaces can harm environmental health, forest density versus grass should be regarded as an important future testing point.

PSM student Jess Bradley produced a map of stop-and-go locations in Philly’s 8th District. These stores sell alcohol, but may be in violation of liquor law requiring them to sell food and provide seating 30 patrons.

Councilwoman Bass holds sit-ins at stop-and-go stores

During sprint two of my capstone with PHS, I worked on finishing the update structure for LandCare Project shapefiles. As we were updating both parcels and sites, code was required for each subset of data. To streamline the process, I used metacoding like what was posted in my first blog post. There were two main processes that were coded in this sprint: address updates and the addition of whole new sites. Both will be discussed in this blog post.

I have found it best when explaining the parts of this project to think about parcels and sites as Lego bricks and structures respectively. Consider an individual Lego brick. Singularly it does a job and has a function, just as a land parcel does as a singular address. But, when parcels are added together they become sites that consist of multiple addresses. This could be considered to when several Lego bricks are combined to make a structure. There is still the ability to remove or add bricks but the overall geometry of the structure will change.

This is comparable to the address addition and removal process. As we add a new parcel or brick, we must also update the sites file as the geometry is changing. This is also true for the removal of a parcel. While the changes we are making may include only the addition of one small parcel or brick, the site geometry or structure changes significantly. This requires a special geometry update in SQL to ensure that these changes are not missed accidentally in a shapefile update.

These address updates were broken into two separate metacoding files: address addition and address removal. Address addition utilized data for parcels from the Philadelphia Water Department to provide geometries for new parcels being added to sites. Each parcel was individually added to the parcels file first with the selection of the geometry and other unique fields from the PWD parcel data. Following, data was updated for the parcel file for the new address range. Other parcel information was copied from other parcels in the site we were interested in. The code for these parts has been pasted below.

INSERT INTO PLCmaint_parcels_2017 (ADDRESS, Geometry)
SELECT ADDRESS, Shape
FROM allparcels_517
WHERE ADDRESS = "1501 N 15TH ST";

UPDATE PLCmaint_parcels_2017
SET PARCELID = (SELECT PLCmaint_parcels_2017.PARCELID FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    TENCODE = (SELECT PLCmaint_parcels_2017.TENCODE FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    OWNER1 = (SELECT PLCmaint_parcels_2017.OWNER1 FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"), 
    OWNER2 = (SELECT PLCmaint_parcels_2017.OWNER2 FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    BLDG_CODE = (SELECT PLCmaint_parcels_2017.BLDG_CODE FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    BLDG_DESC = (SELECT PLCmaint_parcels_2017.BLDG_DESC FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    BRT_ID = (SELECT PLCmaint_parcels_2017.BRT_ID FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    GROSS_AREA = (SELECT PLCmaint_parcels_2017.GROSS_AREA FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Shape_Area = (SELECT PLCmaint_parcels_2017.Shape_Area FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    PID = (SELECT PLCmaint_parcels_2017.PID FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Year = (SELECT PLCmaint_parcels_2017.Year FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Season = (SELECT PLCmaint_parcels_2017.Season FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    VL_Num = (SELECT PLCmaint_parcels_2017.VL_Num FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Sq_Ft = (SELECT PLCmaint_parcels_2017.Sq_Ft FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Trees_Num = (SELECT PLCmaint_parcels_2017.Trees_Num FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    TARGETAREA = (SELECT PLCmaint_parcels_2017.TARGETAREA FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Grid = (SELECT PLCmaint_parcels_2017.Grid FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    CCD = (SELECT PLCmaint_parcels_2017.CCD FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    ZIPCODE = (SELECT PLCmaint_parcels_2017.ZIPCODE FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Action = (SELECT PLCmaint_parcels_2017.Action FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    TARG_GRID = (SELECT PLCmaint_parcels_2017.TARG_GRID FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    CCD_Person = (SELECT PLCmaint_parcels_2017.CCD_Person FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST"),
    Program = (SELECT PLCmaint_parcels_2017.Program FROM PLCmaint_parcels_2017 WHERE PLCmaint_parcels_2017.ADDRESS = "1503 N 15TH ST") 
WHERE ADDRESS IN (
                SELECT ADDRESS
                FROM PLCmaint_parcels_2017
                WHERE PLCmaint_parcels_2017.ADDRESS = "1501 N 15TH ST");

UPDATE PLCmaint_parcels_2017
SET NumParcels = "13",
    Addr_Range = "1501 - 1507 N 15TH ST"
WHERE VL_Num = "VL0123EN_CLP";

Once parcel information was added and correct, the site shapefile geometry was updated accordingly. This was the product of a ST_Union function that inserted new geometries into the sites table in SpatiaLite. Address ranges and parcel numbers were updated accordingly as shown in the code block below.

UPDATE PLCmaint_SITES_2017
SET Geometry = (
                SELECT ST_Union(Geomtery)
                FROM PLCmaint_parcels_2017
                GROUP BY VL_Num);

UPDATE PLCmaint_SITES_2017
SET NumParcels = "13",
    Addr_Range = "1501 - 1507 N 15TH ST"
WHERE VL_Num = "VL0123EN_CLP";

Parcel removal required the copy of parcel data first to the New Use table so changes would be documented for the future. Once this was done, the parcel was deleted from the parcel table. The site geometry was updated following the same process as previously. Address ranges and parcel numbers were updated accordingly. This code can be visualized below for reference.

INSERT INTO NewUse_2017 (PARCELID, TENCODE, ADDRESS, OWNER1, OWNER2, BLDG_CODE, BLDG_DESC, BRT_ID, GROSS_AREA,
    Shape_Area,VL_Num, Sq_Ft, Trees_Num, PID, TARGETAREA, Grid, CCD, Addr_Range, ZIPCODE, Geometry)
SELECT PARCELID, TENCODE, ADDRESS, OWNER1, OWNER2, BLDG_CODE, BLDG_DESC, BRT_ID, GROSS_AREA, Shape_Area, VL_Num, 
    Sq_Ft, Trees_Num, PID, TARGETAREA, GRID, CCD, Addr_Range, ZIPCODE, Geometry
FROM PLCmaint_parcels_2017
WHERE ADDRESS = "1501 N 15TH ST";

DELETE FROM PLCmaint_parcels_2017
WHERE ADDRESS = "1501 N 15TH ST";

UPDATE PLCmaint_parcels_2017
SET NumParcels = "12",
    Addr_Range = "1503-1507 N 15TH ST"
WHERE VL_Num = "VL0123EN_CLP";

UPDATE PLCmaint_SITES_2017
SET Geometry = (
                SELECT ST_Union(Geometry)
                FROM PLCmaint_parcels_2017
                GROUP BY VL_Num);

UPDATE PLCmaint_SITES_2017
SET NumParcels = "12",
    Addr_Range = "1503-1507 N 15TH ST"
WHERE VL_Num = "VL0123EN_CLP";

Adding new sites entirely was a straightforward process. Four sections of metacoding were used to break down information and explain steps. The data was copied from provided shapefiles into the parcels table first, then the sites.

For the sites table update, the VL numbers (unique parcel identifier) were selected with a “SELECT DISTINCT” statement and these were copied into part three. This allowed for only the information for one parcel to be copied into the sites table, representing the data for the site that was available. Finally the site geometry was updated using the ST_Union feature described above. The code used is visible below.

INSERT INTO PLCmaint_parcels_2017 (PARCELID, TENCODE, ADDRESS, OWNER1, OWNER2, BLDG_CODE, BLDG_DESC, BRT_ID, 
    GROSS_AREA, Shape_Area, Year, Season, VL_Num, NumParcels, Sq_Ft, Trees_Num, TARGETAREA, Addr_Range, Geometry)
SELECT PARCELID, TENCODE, ADDRESS, OWNER1, OWNER2, BLDG_CODE, BLDG_DESC, BRT_ID, GROSS_AREA,Shape_Area, Year, 
    Season, VL_Num, NumParcels, Sq_Ft, Trees_Num, TARGETAREA, Addr_Range, Geometry)
FROM Fall16_Stabilization_parcels;

SELECT DISTINCT VL_Num
FROM Fall16_Stabilization_parcels;

INSERT INTO PLCmaint_SITES_2017 (VL_Num, ADDRESS, Year, Season, NumParcels,  Sq_Ft, Trees_Num, TARGETAREA, Addr_Range, Geometry)
SELECT VL_Num, ADDRESS, Year, Season, NumParcels,  Sq_Ft, Trees_Num, TARGETAREA, Addr_Range, Geometry
FROM Fall16_Stabilization_parcelsGROUP BY VL_Num HAVING VL_Num = "VL0123EN_CLP";

UPDATE PLCmaint_SITES_2017
SET Geometry = (
                SELECT ST_Union(Geometry)
                FROM PLCmaint_parcels_2017
                GROUP BY VL_Num);

Any resulting data from these sections were queried to double check the work. My sprint three blog will cover the exporting of these tables to shapefiles and will use visualizations.

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 30^th to June 8^th) 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.

There are about 550 schools and total 55 public libraries in Philadelphia. From all US small and largest cities, Philadelphia has one of the highest crime rate (44 per one thousand). This project tries to determine the risk for students when they want to go to the nearest public library. It also determines how many schools are in one mile from each library that will show how the libraries are unevenly distributed into the city area or the public libraries did not consider the number of schools around it when they established. This also shows where the schools and library and how many crimes happened around each school and library. So, this will predict the possibility to become a victim with the neighborhood crimes. To do this, I have used PostgreSQL which is easier to calculate and measure the risk on the way from a school to a closest library.

I used three important data sources such as school data as .csv, library data as a shapefile and the crime data that shows all the reported crimes that happened in Philadelphia from 2007 to 2014. All these data are acquired from OpenDataPhilly which is a open data source. After download all the necessary data performed a data normalization to reduce the data redundancy. To use these data with PostgreSQL, need to upload in the SQL server (for the shapefile use the shp2pgsql command and for the .csv file use the simple SQL console). The map below shows the location of schools and libraries,  crime incidences around one mile of each schools  and the numbers of schools within one miles from each public libraries.

After applying some quarries, I have found that there many schools where more than 2000 crimes happened and there are three schools where more than 5000 crimes happened within 1000 feet, and there are 5 libraries where more than 2000 crimes happened in those 7 years. The libraries are unevenly distributed around the city depending on the number of schools. There some libraries in which there are only one school, and there are two libraries where more than 20 schools within one mile. Even there are some schools that are more than one and half miles away from the closest libraries. The figure below shows the lines between the schools and the closest libraries with hundred feet buffer around the lines.


CREATE TABLE phl.shortest_distance_buffer AS
SELECT e.from_school, e.close_library, ST_Buffer(geom,100)::geometry(Polygon,2272) AS geom
FROM (
SELECT d.school as from_school,
d.library as close_library,
ST_MakeLine(d.geom1, d.geom2) as geom
FROM(
SELECT
s.facil_name AS school,
s.geom AS geom1,
A.branch_nam AS library,
A.geom AS geom2,
ST_Distance(s.geom, A.geom) as distance
FROM
phl.all_philly_school as s
CROSS JOIN LATERAL
(
SELECT l.branch_nam, l.geom
FROM phl.philly_libraries as l
ORDER BY l.geom s.geom
LIMIT 1
) AS A) as d) as e;



SELECT a.from_school, a.close_library, count(b.objectid)
FROM phl.shortest_distance_buffer as a
JOIN phl.philly_crime_incident_coded as b
ON ST_Contains(a.geom, b.geom)
GROUP BY a.from_school, a.close_library
ORDER BY count(b.objectid);

The above queries, I have use to make a straight line between each school to the closest libraries and make a 100 feet buffer around the each line. The bottom part of the query count crimes in each buffer. The intention of doing this is to determine the number of crimes happen in each buffer line and to find out the possibility to become victim if a student want to go to the closest library from school. The result of this query shows that there are 14 line distance from schools and libraries where more than 650 crime happen  from 2007 to 2014. Therefore, it is more likely to become a victim with the neighborhood crimes than other 531 line distances from schools to libraries. Alternatively, there are 12 line distances between schools and closest libraries where less than 10 crimes happen from 2007 to 2014.

There are some limitations in the project like the straight lines that created between schools and closest libraries are not the route to get to a library, so, the crime calculations are not right just an assumption. Also, at the time of crime calculation I did not concern the time of crime, and the student’s intending time to go to a library. It is important to consider the time of crime and the intending time for a student to go to a library to make a rational estimate of crimes (including the type of crime) that happen into the buffer areas. Depending on the limitations, the further research can be done in which the researcher can use the PG Routing to get the exact routes with distance between a school and a closest library and connect that result with the crimes considering the time and type of crimes to make an potential report to see the possibilities of becoming a victim by a neighborhood crime when a student intended to go to a library. The research can also find the alternative routes and day time where and when (safest way and time) has very less possibilities to become a victim by the neighborhood crime.

For project detail contact email: kamol.sarker@temple.edu