Zip codes

Source code in Zip.zip

1. main.m
2. Class ZipAppDelegate
3. Class Model
4. Class View

Download the file of zipcodes

Download zip5.csv onto your desktop. You can also download the file in Terminal.app.

cd ~/Desktop
pwd

ftp http://i5.nyu.edu/~mm64/INFO1-CE9236/src/zipcode/zip5.csv
ls -l zip5.zip

Open zip5.csv with TextEdit.app. CSV stands for comma separated values. Each line contains six values, separated by five commas. Latitude north of the equator is positive; longitude west of Greenwich is negative; both are in degrees. Here are the first five lines of the file:

00501,Holtsville,NY,40.8152,-73.0455,Suffolk
00544,Holtsville,NY,40.8152,-73.0455,Suffolk
00601,Adjuntas,PR,18.1788,-66.7516,Adjuntas
00602,Aguada,PR,18.381389,-67.188611,Aguada
00603,Aguadilla,PR,18.4554,-67.1308,Aguadilla

No newline

zip5.csv actually contains 41,755 lines. But the last line has no newline character, so wc doesn’t count it.

wc -l zip5.csv
   41754 zip5.csv

tail -1 zip5.csv
95811,Sacramento,CA,38.5815719,-121.4943996,Sacramento

tail -1 zip5.csv | od -c
0000000    9   5   8   1   1   ,   S   a   c   r   a   m   e   n   t   o
0000020    ,   C   A   ,   3   8   .   5   8   1   5   7   1   9   ,   -
0000040    1   2   1   .   4   9   4   3   9   9   6   ,   S   a   c   r
0000060    a   m   e   n   t   o

Create an sqlite database

cd ~/Desktop
pwd

sqlite3 zipcodes.db
sqlite> create table zipcodes (
   zipcode integer,
   city text,
   state text,
   latitude real,
   longitude real,
   county text
);
sqlite> .dump zipcodes

sqlite> .mode csv
sqlite> .show

sqlite> .import zip5.csv zipcodes
sqlite> select count(*) from zipcodes;
41755
sqlite> .quit

ls -l zipcodes.db
sqlite3 zipcodes.db .dump | head -14
BEGIN TRANSACTION;
CREATE TABLE zipcodes (
   zipcode integer,
   city text,
   state text,
   latitude real,
   longitude real,
   county text
);
INSERT INTO "zipcodes" VALUES(501,'Holtsville','NY',40.8152,-73.0455,'Suffolk');
INSERT INTO "zipcodes" VALUES(544,'Holtsville','NY',40.8152,-73.0455,'Suffolk');
INSERT INTO "zipcodes" VALUES(601,'Adjuntas','PR',18.1788,-66.7516,'Adjuntas');
INSERT INTO "zipcodes" VALUES(602,'Aguada','PR',18.381389,-67.188611,'Aguada');
INSERT INTO "zipcodes" VALUES(603,'Aguadilla','PR',18.4554,-67.1308,'Aguadilla');

Create the project

Add the file zipcodes.db to your project. In Groups & Files, highlight the resources folder. Add the dynamic library libsqlite3.dylib to the project.

The app

The address of the textView is passed to foundRecord.

Things to try

1. Create a view controller for the view. The view controller will call the getZip method of the model. The view will no longer call this method.


2. It’s disquieting that all fields are read from the database as strings, even the ones that are numeric. Here is a more efficient way to get the results. Remove the foundRecord function in Model.m. In the getZip: method of class Model, replace

   	char *error;
   	if (sqlite3_exec(base, sql, foundRecord, s, &error) != SQLITE_OK) {
   		NSLog(@"%s", error);
   		sqlite3_free(error);
   	}
   
   

   with the following.

   	sqlite3_stmt *statement;
   
   	if (sqlite3_prepare_v2(base, sql, strlen(sql), &statement, NULL) != SQLITE_OK) {
   		NSLog(@"%s", sqlite3_errmsg(base));
   	}
   
   	while (sqlite3_step(statement) == SQLITE_ROW) {
   		for (int column = 0; column < sqlite3_column_count(statement); ++column) {
   			[s appendFormat: @"%s: ", sqlite3_column_name(statement, column)];
   
   			switch (sqlite3_column_type(statement, column)) {
   
   			case SQLITE_INTEGER:
   				[s appendFormat: @"%d\n", sqlite3_column_int(statement, column)];
   				break;
   
   			case SQLITE_FLOAT:
   				[s appendFormat: @"%g\n", sqlite3_column_double(statement, column)];
   				break;
   
   			case SQLITE3_TEXT:
   				[s appendFormat: @"%s\n", sqlite3_column_text(statement, column)];
   				break;
   
   			case SQLITE_BLOB:
   				break;
   
   			case SQLITE_NULL:
   				[s appendString: @"(null)\n"];
   				break;
   
   			default:
   				NSLog(@"Unknown column type.");
   			}
   		}
   	}
   
   	if (sqlite3_finalize(statement) != SQLITE_OK) {
   		NSLog(@"sqlite3_finalize failed.");
   	}
   



3. Go to the New York City Data Mine.
   Data Sets → Raw Data
Data by Category → All
Data by Agency → All
search
   Look for the text files of restaurant inspection results from the Department of Health and Mental Hygiene (DOHMH). Action code "P" in Violation.txt means “establishment padlocked by DOHMH”.