A STUDY IN CHICAGO RECYCLING SYSTEM

by Soledad Hernandez & Pinar Dursun

Problem

The millions of tons of waste disposed of into our environment every year. As urban growth continues to take hold in many cities, our levels of all types of waste, combined with the problems created when it comes to disposing of them, are constantly increasing. In front of this situation, an efficient management waste system can solve a basic problem in the cities.

Chicago generates 7,299,174 tons of waste every year and residents recycle just more than 200,000 tons of materials per year.

Chicago has two recycling systems: Blue Cart and Drop-off

The goals

The aim of this project is to analysis how is the recycling system en Chicago

Examining the effectiveness of Chicago Recycling System.

How the recycling system of Chicago can be improved?

Recycling amount distribution by location.

Correlation between recycling amount and demographical information.

Process

After preparing the spreadsheets of the data and uploading them in iituim server database via PostgreSQL software as tables, shape files were imported to QGIS software where the contacts between the amounts and locations were made. Thus, equations for analysis were created.

Maps

Conclusion

22 wards have neither drop-off center nor blue cart system. 1,186,364 people living in this wards without any recycling service.

It is obvious that blue cart system is more efficient than drop-off centers due to its easiness. Travelling miles to throw the recyclables into the drop-off center instead of putting them into the blue cart in front of their house is a dissuasive effect for the people who do not live in blue cart covered neighborhoods.

It also seems like the people living in the north neighborhoods are more eager to recycle. Northwards can be proposed for the location of blue cart area future expansion.

For complete presentation with all the maps: RECYCLING 5.4.12

Advertisements

Geometry Creation SQL


--('schema','name of table to add a column to','name of column to add',srid,'postgis geometry type',dimension)
--SELECT AddGeometryColumn ('public','recycling_dropoff_location','geom',26971,'POINT',2);

--SELECT AddGeometryColumn('public','recycling_dropoff_location','untransgeom',900913,'POINT',2)
--SELECT AddGeometryColumn('public','recycling_dropoff_location','untransgeom2',4326,'POINT',2)

--st_transform(st_setsrid(st_point(lat,long),900913),26971)
--three compound functions
--the innermost st_point(lon,lat) creates a geometry from an x and y location
--... watch the lat/lon distinctions the table im testing with appears to have them flip flopped
--the next outer function st_setserid( geometry to operate on, 900913) establishes the srid of the geometry
--... in this case I am assuming the unprojected google system 900913
--the outermost function st_transform( geometry to operate on, 26971) converts from one projection to another
--UPDATE recycling_dropoff_location
--SET geom = st_transform(st_setsrid(st_point(lon,lat),900913),26971)

--UPDATE recycling_dropoff_location
--SET untransgeom2 = st_setsrid(st_point(lon,lat),4326)

--UPDATE recycling_dropoff_location
--SET geom = st_setsrid(st_point(st_x(st_transform(untransgeom2,26971))*3.2808399,st_y(st_transform(untransgeom2,26971))*3.2808399),26971)

--SELECT st_asewkt( geom )
--FROM recycling_dropoff_location

Useful Plugin for QGIS!!

PgQuery is a plugin for QGIS which allows you to execute sql with postgis functionality, and load the results directly as layers with geometry.

a longer writeup can be found here,

As a test, I executed the following query to get all the building footprints in a given neighborhood

SELECT "BLDG_ID","Buildings".the_geom,hood.pri_neigh
FROM
(SELECT * FROM neighboorhoods WHERE neighboorhoods.pri_neigh = 'Lincoln Park') AS hood,
"Buildings"
WHERE st_contains(hood.geom,"Buildings".the_geom) = true

Check it out! this should be really useful for you!!!

Query Example From Class

Here is the query sans-comments

SELECT nname, COUNT(nname) AS qty
FROM (
SELECT n.pri_neigh AS nname,
n.shape_area AS narea,
s.longname AS sname,
ST_Distance(n.geom,s.geom) AS distance
FROM neighboorhoods AS n
CROSS JOIN cta_railstations AS s
WHERE ST_Distance(n.geom,s.geom) < 1000
) GROUP BY nname;

explanatory comments included

--in the select portion we are pulling out the name
--from the sub-query below
SELECT nname, distance, COUNT(nname) AS qty
FROM
--this begins the sub-query
(
--selecting from two joined tables
--using alias for clarity
SELECT n.pri_neigh AS nname,
n.shape_area AS narea,
s.longname AS sname,
--postgis function to get the distance
--between two geometry objects
ST_Distance(n.geom,s.geom) AS distance
FROM neighboorhoods AS n
--joins neighboorhoods and railstations
--creates m*n rows
--for comparison of every neighboorhood
--to every railstation
CROSS JOIN cta_railstations AS s
--clause to reduce the m*n set to only
--those which fall within a certain distance
WHERE ST_Distance(n.geom,s.geom) < 1000
)
-- from w3 schools ...
--The GROUP BY statement is used in conjunction
--with the aggregate functions to group the result-set
--by one or more columns.
GROUP BY nname;

Links from class

QGIS tutorials by Gandhi
Quantum GIS (QGIS) Tutorials
Quantum GIS (QGIS) Tutorials: Using Tabular Data in QGIS
Quantum GIS (QGIS) Tutorials: Tutorial: Styling Vector Data in QGIS – Basics
Quantum GIS (QGIS) Tutorials: Tutorial: Spatial Analysis – Points in Polygon
Quantum GIS (QGIS) Tutorials: Tutorial: Performing Spatial Queries in QGIS
Quantum GIS (QGIS) Tutorials: Tutorial: Working with Attribute Table – Basic Queries

PostGIS function reference
PostGIS book I mentioned
Chapter 8. PostGIS Reference

Purely SQL
W3 Schools SQL Reference
SQL GROUP BY Statement
SQL: COUNT Function
SQL COUNT() Function

Where we might go time permitting
15 Advanced PostgreSQL Commands with Examples
www.day32.com/MySQL/Meetup/Presentations/postgresql_stored_procedures.pdf