-
Notifications
You must be signed in to change notification settings - Fork 6
Quick stats
Chris Gansen edited this page Jul 16, 2013
·
3 revisions
First 25 pictures sent via Open311
cwfy=# SELECT service_request_id,media_url,requested_datetime FROM service_requests WHERE media_url != '' ORDER BY requested_datetime ASC LIMIT 25;
service_request_id | media_url | requested_datetime
--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------
12-01612896 | http://311request.cityofchicago.org/media/chicago/report/photos/505244924e457c52651f4793/chicago512px.png | 2012-09-13 20:43:41+00
12-01614034 | http://311request.cityofchicago.org/media/chicago/report/photos/5052a41f4e457c52651f4b14/1948whenderson_sinkhole.jpeg | 2012-09-14 03:28:51+00
12-01620537 | http://311request.cityofchicago.org/media/chicago/report/photos/5053dc864e4556040195fc52/beaver_habitat.jpg | 2012-09-15 11:08:41+00
12-01621768 | http://311request.cityofchicago.org/media/chicago/report/photos/5054e1f74e455604019602c1/DSC_0862.jpg | 2012-09-16 00:12:01+00
12-01630119 | http://311request.cityofchicago.org/media/chicago/report/photos/505786434e45560401961627/street_repair_2.jpg | 2012-09-18 01:07:22+00
12-01636354 | http://311request.cityofchicago.org/media/chicago/report/photos/5058f43f4e4561dda65151f7/Street_Light_Out.jpg | 2012-09-18 22:25:03+00
12-01636497 | http://311request.cityofchicago.org/media/chicago/report/photos/5058a8664e4561dda6514c92/Graffiti_9_18_12.jpg | 2012-09-18 22:52:05+00
12-01637077 | http://311request.cityofchicago.org/media/chicago/report/photos/505945784e4561dda65155c7/IMG_20120913_121840.jpg | 2012-09-19 04:10:11+00
12-01637949 | http://311request.cityofchicago.org/media/chicago/report/photos/5059cc2f4e4561dda651579c/6616_s_marshfield_001.jpg | 2012-09-19 13:46:20+00
12-01638077 | http://311request.cityofchicago.org/media/chicago/report/photos/5059cf834e4561dda65157f2/PART951346877805539.jpg | 2012-09-19 14:01:25+00
12-01639225 | http://311request.cityofchicago.org/media/chicago/report/photos/5059e4564e4561dda6515945/Ratsnestincar.jpg | 2012-09-19 15:28:28+00
12-01639262 | http://311request.cityofchicago.org/media/chicago/report/photos/5059e5074e4561dda651595a/Ratsnestincar.jpg | 2012-09-19 15:31:35+00
12-01642894 | http://311request.cityofchicago.org/media/chicago/report/photos/505a53734e4561dda651611c/IMG_20120919_144851.jpg | 2012-09-19 23:22:28+00
12-01642999 | http://311request.cityofchicago.org/media/chicago/report/photos/505a5dbe4e4561dda65161c8/2012-07-15_02.30.54.jpg | 2012-09-20 00:07:28+00
12-01643010 | http://311request.cityofchicago.org/media/chicago/report/photos/505a5e5d4e4561dda65161d9/2012-07-15_02.30.23.jpg | 2012-09-20 00:10:28+00
12-01643023 | http://311request.cityofchicago.org/media/chicago/report/photos/505a60104e4561dda65161f6/2012-07-15_02.30.35.jpg | 2012-09-20 00:16:35+00
12-01643030 | http://311request.cityofchicago.org/media/chicago/report/photos/505a60764e4561dda65161ff/2012-07-15_02.31.17.jpg | 2012-09-20 00:19:35+00
12-01643041 | http://311request.cityofchicago.org/media/chicago/report/photos/505a619d4e4561dda651620d/2012-07-15_02.33.05.jpg | 2012-09-20 00:22:35+00
12-01643055 | http://311request.cityofchicago.org/media/chicago/report/photos/505a61f64e4561dda6516216/0816121107.jpg | 2012-09-20 00:25:34+00
12-01644985 | http://311request.cityofchicago.org/media/chicago/report/photos/505b29374e45c5f269cea571/Pothole.jpg | 2012-09-20 14:34:42+00
12-01648730 | http://311request.cityofchicago.org/media/chicago/report/photos/505b83874e45c5f269ceafb2/LA_Fitness_1101_South_Canal__Chicago__Il_-Rusted_Fire_Exits_Pic_2.jpg | 2012-09-20 21:01:52+00
12-01648830 | http://311request.cityofchicago.org/media/chicago/report/photos/505b86bd4e45c5f269ceafc5/Southgate_Market_Ramp_to_La_Fitness_Parking_Garage-Roosevelt_Entrance.jpg | 2012-09-20 21:13:49+00
12-01648850 | http://311request.cityofchicago.org/media/chicago/report/photos/505b87184e45c5f269ceafd3/Southgate_Market_Ramp_to_La_Fitness_Parking_Garage-Roosevelt_Entrance.jpg | 2012-09-20 21:16:48+00
12-01649108 | http://311request.cityofchicago.org/media/chicago/report/photos/505b97724e45c5f269ceb124/LA_Fitness_1101_South_Canal__Chicago__Il_Pic_1.jpg | 2012-09-20 22:25:50+00
12-01651678 | http://311request.cityofchicago.org/media/chicago/report/photos/505c81cc4e45c5f269ceba16/Abandoned_Range_Rover.jpg | 2012-09-21 15:05:15+00
(25 rows)
cnt | address
-----+---------------------------------------------
926 | 7900 S STONY ISLAND AVE, CHICAGO, IL, 60617
785 | 4700 S LAKE PARK AVE, CHICAGO, IL, 60615
773 | 9500 S STONY ISLAND AVE, CHICAGO, IL, 60617
702 | 7900 S KEDZIE AVE, CHICAGO, IL, 60652
652 | 8700 S WESTERN AVE, CHICAGO, IL
645 | 9500 S JEFFERY AVE, CHICAGO, IL, 60617
616 | 2400 W PETERSON AVE, CHICAGO, IL, 60659
612 | 1000 W HOLLYWOOD AVE, CHICAGO, IL, 60660
560 | 8700 S STATE ST, CHICAGO, IL, 60619
556 | 3200 W BELMONT AVE, CHICAGO, IL, 60618
525 | 4801 N CICERO AVE, CHICAGO, IL, 60630
498 | 7100 S PULASKI RD, CHICAGO, IL, 60629
491 | 8700 S STONY ISLAND AVE, CHICAGO, IL, 60617
482 | 2400 W OGDEN AVE, CHICAGO, IL, 60608
464 | 8300 S VINCENNES AVE, CHICAGO, IL, 60620
462 | , CHICAGO, IL
459 | 2400 W FULLERTON AVE, CHICAGO, IL, 60647
439 | 6700 S STONY ISLAND AVE, CHICAGO, IL, 60649
434 | 8700 S LAFAYETTE AVE, CHICAGO, IL, 60620
423 | 7100 S STONY ISLAND AVE, CHICAGO, IL, 60649
409 | 6400 N CALDWELL AVE, CHICAGO, IL, 60646
398 | 1000 N LAKE SHORE DR, CHICAGO, IL, 60611
396 | 9500 S ASHLAND AVE, CHICAGO, IL, 60643
391 | 7900 S GREENWOOD AVE, CHICAGO, IL, 60619
390 | 5700 S KENTON AVE, CHICAGO, IL, 60629
cwfy=# SELECT COUNT(*) AS cnt, service_name FROM service_requests WHERE address = '7900 S STONY ISLAND AVE, CHICAGO, IL, 60617' GROUP BY service_name ORDER BY cnt DESC;
cnt | service_name
-----+--------------------------------
761 | Traffic Signal Out
75 | Pothole in Street
61 | Street Lights All / Out
8 | Restaurant Complaint
6 | Graffiti Removal
5 | Street Light 1 / Out
4 | Pavement Cave-In Survey
3 | Sanitation Code Violation
2 | Rodent Baiting / Rat Complaint
1 | Building Violation
(10 rows)
cwfy=# SELECT COUNT(*) AS cnt, service_name FROM service_requests WHERE address = '4700 S LAKE PARK AVE, CHICAGO, IL, 60615' GROUP BY service_name ORDER BY cnt DESC;
cnt | service_name
-----+--------------------------------
610 | Pothole in Street
70 | Traffic Signal Out
54 | Street Lights All / Out
16 | Pavement Cave-In Survey
12 | Graffiti Removal
10 | Building Violation
3 | Rodent Baiting / Rat Complaint
3 | Street Cut Complaints
3 | Restaurant Complaint
2 | Sanitation Code Violation
1 | Tree Debris
1 | Street Light 1 / Out
(12 rows)
cwfy=# SELECT COUNT(*) AS cnt, service_name FROM service_requests GROUP BY service_name ORDER BY cnt DESC;
cnt | service_name
--------+--------------------------------
927865 | Graffiti Removal
444532 | Pothole in Street
348829 | Street Lights All / Out
195729 | Rodent Baiting / Rat Complaint
173859 | Abandoned Vehicle
151718 | Alley Light Out
144910 | Building Violation
142025 | Tree Debris
141323 | Street Light 1 / Out
123047 | Traffic Signal Out
114055 | Sanitation Code Violation
39235 | Pavement Cave-In Survey
18292 | Restaurant Complaint
7836 | Street Cut Complaints
(14 rows)
cwfy=# SELECT COUNT(DISTINCT(address)) FROM service_requests;
count
--------
706324
cwfy=# SELECT COUNT(*) AS cnt, service_name FROM service_requests WHERE requested_datetime >= '2013-01-01'::date GROUP BY service_name ORDER BY cnt DESC;
cnt | service_name
-------+--------------------------------
76745 | Graffiti Removal
50162 | Pothole in Street
23723 | Street Lights All / Out
12989 | Rodent Baiting / Rat Complaint
11539 | Building Violation
10848 | Abandoned Vehicle
10785 | Traffic Signal Out
9708 | Street Light 1 / Out
9348 | Sanitation Code Violation
9073 | Tree Debris
7296 | Alley Light Out
3893 | Pavement Cave-In Survey
1326 | Restaurant Complaint
931 | Street Cut Complaints
(14 rows)