Tap a marker to see the most recent health code violation(s) for any restaurant in zip code 10003. In the first screenshot, HACCP stands for Hazard analysis and critical control points. In the second screenshot, the hole in the upper right corner of the zipcode is Stuyvestant Square on Second Avenue. The barely noticeable hole in the upper left corner is Union Square. For the outlying markers, see exercise 4 below.
MainActivity.java.
Class
MainActivity
implements the interface
OnMapReadyCallback.
Helper.java
defines a subclass of
SQLiteAssetHelper.
activity_main.xml
contains a
com.google.android.gms.maps.MapView.
info_window.xml
contains the vertical
LinearLayout
displayed when you tap a
Marker.
strings.xml
contains the
google_maps_key.
You will have to put in your own key.
restaurants.db
AndroidManifest.xml
build.gradle
(Module: app)
Follow the instructions in
Assets
for creating the folders
app/assets
and
app/assets/databases,
and modifying
build.gradle.
Follow the instructions in
MapView
for getting a Google Maps key and modifying
AndroidManifest.xml
and
build.gradle.
Create
restaurants.db
by runnning the following shellscript.
To use the JSON module in the second
perl,
you will have to give the
cpan
and
cpanm
commands in the CPAN
Quick start
before you run the shellscript.
Type them as follows.
cpan App::cpanminus ~/perl5/bin/cpanm JSONYou must also give the following
-I
option to
perl.
#!/bin/bash
#This shellscript runs on a Mac and creates the SQLite database file
#restaurants.db, containing the most recent inspection result for each
#restaurant in the selected zipcode. The shellscript deposits the file in the
#folder ~/restaurant, so you must create this folder before running the
#shellscript.
selectedzipcode=10003
date
cd ~/restaurant
#Step 1. Download the comma-separated values file from New York City OpenData.
rm -f DOHMH_New_York_City_Restaurant_Inspection_Results.csv
curl \
'https://data.cityofnewyork.us/api/views/xx67-kt59/rows.csv?accessType=DOWNLOAD' \
> DOHMH_New_York_City_Restaurant_Inspection_Results.csv
#List the file. This -l stands for "long".
ls -l DOHMH_New_York_City_Restaurant_Inspection_Results.csv
#Print the number of lines in the file. This -l stands for "lines".
wc -l DOHMH_New_York_City_Restaurant_Inspection_Results.csv
#Step 2. Create a csv file (importable.csv) that can be .imported into sqlite3.
#Make these changes to the original csv file:
# 1. Remove the first line, which consists of the names of the fields.
# 2. In any field surrounded by "double quotes", change each comma to |
# because the sqlite3 .import statement would be confused by the commas.
# 3. [Only cosmetic.] Remove leading, trailing, and redundant internal
# whitespace from the building and street fields.
rm -f importable.csv
perl -ne '
next if $. == 1; #Remove the first line.
chomp; #Remove the newline at the end of the current line.
tr/ / /s; #Change each group of 2 or more consecutive spaces
#into a single space.
#The variable $_ holds the current line of input.
#Loop until this line has been reduced to the empty string.
while ($_ ne "") {
#If the current line begins with a double-quoted string
#that contains no double quotes, chop it off and store it in $&.
if (s/^"[^"]*"//) {
$field = $&;
$field =~ tr/,/|/;
} else {
#Chop off everything up to but not including the first
#comma, and store it in $&.
s/^[^,]*//;
$field = $&;
$field =~ s/^\s+//; #Remove leading whitespace.
$field =~ s/\s+$//; #Remove trailing whitespace.
}
print $field;
#If the line begins with a comma,
#chop it off and store it in $&.
if (s/^,//) {
print $&;
}
}
print "\n";
' DOHMH_New_York_City_Restaurant_Inspection_Results.csv > importable.csv
ls -l importable.csv
wc -l importable.csv
#Step 3. .import importable.csv into sqlite3 so we can reformat the dates and
#delete every inspection result except for the most recent one for each
#restaurant.
rm -f temp.db
sqlite3 temp.db << EOF
create table restaurants (
camis integer, --City-Wide Agency Management Information System
dba text,
boro text,
building text,
street text,
zipcode integer,
phone text,
cuisine_description text,
inspection_date text,
action text,
violation_code text,
violation_description text,
critical_flag text,
score integer,
grade text,
grade_date text,
record_date text,
inspection_type text
);
.mode csv
.import importable.csv restaurants
select "Table restaurants in temp.db contains " || count(*) || " records."
from restaurants;
--Change the format of each date from 12/31/2015 to 2015-12-31 (with dashes).
--An alphabetical comparison of two dates will now tell us which one is earlier.
--Since the grade_date and record_date can be empty, we have to use the
--underscore wildcard.
update restaurants set inspection_date =
substr(inspection_date, 7, 4) || "-" ||
substr(inspection_date, 1, 2) || "-" ||
substr(inspection_date, 4, 2) where inspection_date like "__/__/____";
update restaurants set grade_date =
substr(grade_date, 7, 4) || "-" ||
substr(grade_date, 1, 2) || "-" ||
substr(grade_date, 4, 2) where grade_date like "__/__/____";
update restaurants set record_date =
substr(record_date, 7, 4) || "-" ||
substr(record_date, 1, 2) || "-" ||
substr(record_date, 4, 2) where record_date like "__/__/____";
.quit
EOF
#Step 4. Create a new csv file ($selectedzipcode.csv) containing only the most
#recent inspection result for each restaurant in the selected zipcode. Then add
#three new columns to $selectedzipcode.csv: _id, latitude, longitude.
#Google gives us a big JSON object containing two fields, status and results.
#status is a string (hopefully "OK"). results is an array.
#Each object in the array contains a smaller object named geometry.
#geometry contains a smaller object named location.
#location contains two numbers, latitude and longitude.
rm -f $selectedzipcode.csv
sqlite3 temp.db '.separator ,' 'select
camis,
dba,
boro,
building,
street,
zipcode,
phone,
cuisine_description,
max(inspection_date),
action,
violation_code,
violation_description,
critical_flag,
score,
grade,
grade_date,
record_date,
inspection_type
from restaurants where zipcode = '$selectedzipcode' group by camis;' |
perl -I$HOME/perl5/lib/perl5 -F, -ane '
use JSON; #imported from www.cpan.org
chomp;
$address = "$F[3] $F[4], New York, NY $F[5]";
$address =~ tr/ /+/s; #Change each chunk of white space to a plus.
$address =~ s/'"'"'/%27/; #Change each single quote to %27.
$url = "https://maps.googleapis.com/maps/api/geocode/json?address=$address";
$json = `curl -s $url`;
$bigObject = decode_json($json);
if ($bigObject->{"status"} eq "OK") {
$result = $bigObject->{"results"}[0];
$geometry = $result->{"geometry"};
$location = $geometry->{"location"};
$latitude = $location->{"lat"};
$longitude = $location->{"lng"};
} else {
$latitude = 0;
$longitude = 0;
print STDERR "could not find lat lng for $F[0] $F[1] at $address\n";
}
print "$.,$_,$latitude,$longitude\n";
' > $selectedzipcode.csv
ls -l $selectedzipcode.csv
wc -l $selectedzipcode.csv
#Create restaurants.db and put the table android_metadata into it. Also copy
#$selectedzipcode.csv into restaurants, changing the |s back to commas.
rm -f restaurants.db
sqlite3 restaurants.db << EOF
create table android_metadata (
"locale" text default "en_US"
);
insert into android_metadata (locale) values ("en_US");
create table restaurants (
_id integer primary key autoincrement,
camis integer,
dba text,
boro text,
building text,
street text,
zipcode integer,
phone text,
cuisine_description text,
inspection_date text,
action text,
violation_code text,
violation_description text,
critical_flag text,
score integer,
grade text,
grade_date text,
record_date text,
inspection_type text,
latitude real,
longitude real
);
.mode csv
.import $selectedzipcode.csv restaurants
--Change the vertical bars back to commas now that we are safely past the
--.imports.
update restaurants
set dba = replace(dba, "|", ",");
update restaurants
set violation_description = replace(violation_description, "|", ",");
select "Table restaurants in restaurants.db contains " || count(*)
|| " records." from restaurants;
.quit
EOF
chmod 444 restaurants.db #r--r--r--
ls -l restaurants.db
#rm DOHMH_New_York_City_Restaurant_Inspection_Results.csv importable.csv $selectedzipcode.csv temp.db
date
exit
Here is the output of the above shellscript.
Sun Aug 23 16:52:30 EDT 2015
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 180M 0 180M 0 0 791k 0 --:--:-- 0:03:53 --:--:-- 1106k
-rw-r--r-- 1 myname mygroup 189163743 Aug 23 16:56 DOHMH_New_York_City_Restaurant_Inspection_Results.csv
493588 DOHMH_New_York_City_Restaurant_Inspection_Results.csv
-rw-r--r-- 1 myname mygroup 179388445 Aug 23 16:56 importable.csv
493587 importable.csv
"Table restaurants in temp.db contains 493587 records."
-rw-r--r-- 1 myname mygroup 251126 Aug 23 17:00 10003.csv
673 10003.csv
"Table restaurants in restaurants.db contains 673 records."
-r--r--r-- 1 myname mygroup 321536 Aug 23 17:00 restaurants.db
Sun Aug 23 17:00:45 EDT 2015
To run the project in
Violation.zip,
you will have to put your own Google Maps key into
strings.xml.
head -3 DOHMH_New_York_City_Restaurant_Inspection_Results.csv CAMIS,DBA,BORO,BUILDING,STREET,ZIPCODE,PHONE,CUISINE DESCRIPTION,INSPECTION DATE,ACTION,VIOLATION CODE,VIOLATION DESCRIPTION,CRITICAL FLAG,SCORE,GRADE,GRADE DATE,RECORD DATE,INSPECTION TYPE 30075445,MORRIS PARK BAKE SHOP,BRONX,1007,MORRIS PARK AVE,10462,7188924968,Bakery,02/09/2015,Violations were cited in the following area(s).,06C,"Food not protected from potential source of contamination during storage, preparation, transportation, display or service.",Critical,6,A,02/09/2015,08/15/2015,Cycle Inspection / Initial Inspection 30075445,MORRIS PARK BAKE SHOP,BRONX,1007,MORRIS PARK AVE,10462,7188924968,Bakery,03/03/2014,Violations were cited in the following area(s).,10F,"Non-food contact surface improperly constructed. Unacceptable material used. Non-food contact surface or equipment improperly maintained and/or not properly sealed, raised, spaced or movable to allow accessibility for cleaning on all sides, above and underneath the unit.",Not Critical,2,A,03/03/2014,08/15/2015,Cycle Inspection / Initial Inspection head -3 importable.csv 30075445,MORRIS PARK BAKE SHOP,BRONX,1007,MORRIS PARK AVE,10462,7188924968,Bakery,02/09/2015,Violations were cited in the following area(s).,06C,"Food not protected from potential source of contamination during storage| preparation| transportation| display or service.",Critical,6,A,02/09/2015,08/15/2015,Cycle Inspection / Initial Inspection 30075445,MORRIS PARK BAKE SHOP,BRONX,1007,MORRIS PARK AVE,10462,7188924968,Bakery,03/03/2014,Violations were cited in the following area(s).,10F,"Non-food contact surface improperly constructed. Unacceptable material used. Non-food contact surface or equipment improperly maintained and/or not properly sealed| raised| spaced or movable to allow accessibility for cleaning on all sides| above and underneath the unit.",Not Critical,2,A,03/03/2014,08/15/2015,Cycle Inspection / Initial Inspection 30075445,MORRIS PARK BAKE SHOP,BRONX,1007,MORRIS PARK AVE,10462,7188924968,Bakery,10/10/2013,No violations were recorded at the time of this inspection.,,,Not Applicable,,,,08/15/2015,Trans Fat / Second Compliance Inspection head -3 10003.csv 1,40361708,BULLY'S DELI,MANHATTAN,759,BROADWAY,10003,2122549755,Delicatessen,2015-08-06,Violations were cited in the following area(s).,02G,Cold food item held above 41º F (smoked fish and reduced oxygen packaged foods above 38 ºF) except during necessary preparation.,Critical,28,,,2015-08-15,Cycle Inspection / Initial Inspection,40.7309346,-73.9925306 2,40364389,OLD TOWN BAR & RESTAURANT,MANHATTAN,45,EAST 18 STREET,10003,2125296732,American,2014-10-08,Violations were cited in the following area(s).,06D,Food contact surface not properly washed| rinsed and sanitized after each use and following any activity when contamination may have occurred.,Critical,10,A,2014-10-08,2015-08-15,Cycle Inspection / Initial Inspection,40.7375638,-73.9891878 3,40365075,MCSORLEY'S OLD ALE HOUSE,MANHATTAN,15,EAST 7 STREET,10003,2122542570,Irish,2015-05-20,Violations were cited in the following area(s).,10B,Plumbing not properly installed or maintained; anti-siphonage or backflow prevention device not provided where required; equipment or floor not properly drained; sewage disposal system in disrepair or not functioning properly.,Not Critical,2,A,2015-05-20,2015-08-15,Cycle Inspection / Initial Inspection,40.7287978,-73.9896713
Point your web browser at
https://maps.googleapis.com/maps/api/geocode/json?address=7+East+12th+Street,+New+York,+NY+10003
You will see the JSON parsed by the above shellscript.
See the
Geocoding API Request Format
and
Geocoding
Responses.
{
"results" : [
{
"address_components" : [
{
"long_name" : "7",
"short_name" : "7",
"types" : [ "street_number" ]
},
{
"long_name" : "East 12th Street",
"short_name" : "E 12th St",
"types" : [ "route" ]
},
{
"long_name" : "Lower Manhattan",
"short_name" : "Lower Manhattan",
"types" : [ "neighborhood", "political" ]
},
{
"long_name" : "Manhattan",
"short_name" : "Manhattan",
"types" : [ "sublocality_level_1", "sublocality", "political" ]
},
{
"long_name" : "New York",
"short_name" : "NY",
"types" : [ "locality", "political" ]
},
{
"long_name" : "New York County",
"short_name" : "New York County",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "New York",
"short_name" : "NY",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
},
{
"long_name" : "10003",
"short_name" : "10003",
"types" : [ "postal_code" ]
}
],
"formatted_address" : "7 East 12th Street, New York, NY 10003, USA",
"geometry" : {
"location" : {
"lat" : 40.7344999,
"lng" : -73.99370259999999
},
"location_type" : "ROOFTOP",
"viewport" : {
"northeast" : {
"lat" : 40.73584888029149,
"lng" : -73.9923536197085
},
"southwest" : {
"lat" : 40.73315091970849,
"lng" : -73.9950515802915
}
}
},
"place_id" : "ChIJG_dRBJtZwokRTkBf190nOJk",
"types" : [ "street_address" ]
}
],
"status" : "OK"
}
adb devices adb -s 192.168.57.101:5555 shell cd /data/data/edu.nyu.scps.violation/databases pwd /data/data/edu.nyu.scps.violation/databases ls -l sqlite3 restaurants.db .mode line select * from restaurants where camis = 41629811; .quit exit
critical_flag
field of the table.
Or you could have a brown
marker
for each restaurant that has mice.
In the
onMapReady
method of the
MainActivity,
BitmapDescriptor greenIcon = BitmapDescriptorFactory.defaultMarker(BitmapDescriptorFactory.HUE_GREEN);
markerOptions.icon(greenIcon);
C3
and
82
(in hexadecimal).
The degree symbol
(°)
after it is Unicode
\00B0,
encoded as
C2
and
BA in
UTF-8.
grep 'THE HUMMUS AND PITA CO\.' DOHMH_New_York_City_Restaurant_Inspection_Results.csv | head -1 | tail -c +177 | od -c 0000000 o r a b o v e 1 4 0 Â ** º ** grep 'THE HUMMUS AND PITA CO\.' DOHMH_New_York_City_Restaurant_Inspection_Results.csv | head -1 | tail -c +177 | od -t x1 0000000 6f 72 20 61 62 6f 76 65 20 31 34 30 c3 82 c2 baRemove the offending bytes by inserting the following statement into the first
perl
command immediately after the
tr/ / /s;
s/\xc3\x82//g; #Remove every c382.
121,40856729,CRIF DOGS / PLEASE DON'T TELL,MANHATTAN,113,ST MARKS'S PLACE,10003,2126142728,American,2015-05-26,Violations were cited in the following area(s).,02G,Cold food item held above 41º F (smoked fish and reduced oxygen packaged foods above 38 ºF) except during necessary preparation.,Critical,21,Z,2015-05-26,2015-08-15,Cycle Inspection / Re-inspection,40.7271072,-73.9837352113 St. Mark’s Place is actually in zipcode 10009 because it’s east of First Avenue. This is an error in
DOHMH_New_York_City_Restaurant_Inspection_Results.csv.
To delete the offending line,
insert the following statement into the second
perl
immediately after the
chomp;.
next if $F[0] eq "40856729"; #$F[0] is the camis number.
159,41026782,LUZZO'S,MANHATTAN,211213,1 AVENUE,10003,2124737447,Italian,2015-03-13,Violations were cited in the following area(s).,06D,Food contact surface not properly washed| rinsed and sanitized after each use and following any activity when contamination may have occurred.,Critical,7,A,2015-03-13,2015-08-15,Cycle Inspection / Initial Inspection,40.6003392,-73.9859034This restaurant is at 211–213 First Avenue (between 12th and 13th Streets), but the missing dash causes the marker to appear deep in Brooklyn. (You’ll have to zoom way out to see it.) This is an error in
DOHMH_New_York_City_Restaurant_Inspection_Results.csv.
Insert the following statement into the second
perl
immediately after the statement you just inserted in the previous part
of this exercise.
$F[3] =~ s/211213/211-213/ if $F[0] eq "41026782"; #$F[3] is the building number.
316,41460275,MOM'S COOKING,MANHATTAN,33,EAST 8 STREET,10003,2126747687,American,2015-05-20,Violations were cited in the following area(s).,10F,Non-food contact surface improperly constructed. Unacceptable material used. Non-food contact surface or equipment improperly maintained and/or not properly sealed| raised| spaced or movable to allow accessibility for cleaning on all sides| above and underneath the unit.,Not Critical,2,A,2015-05-20,2015-08-15,Cycle Inspection / Re-inspection,40.7383414,-74.0042118Google Maps believes that this address is on Eighth Avenue, not Eighth Street. To prove it, point your browser at
https://www.google.com/maps/place/33+East+8+Street,+New+York,+NY+10003