Adobe Air Zip Codes - Niagara Web Design | Niagara Custom Software | Niagara Web Hosting | Niagara Internet Marketing | Niagara SEO | Swhistle Soft

Adobe Air Zip Codes

Ever want to find your zipcode? Ever want to find the geolocation of your zipcode? Ever want to find the zipcodes in a State or City? Ever play around with Adobe Air? Well, I have had all of the previous inclinings. I decided to check out a source forge project:Source Forge Zip Codes Database

To Download

Adobe Air Needed to run air applications

The Data

The data itself is in a format that I was unable to use to start with; I wanted to see something like zipcodes.sql. However, I was not a complete bafoon about it and decided to convert it myself. I was able, through the use of regular expressions in a wonderful tool called notepad++. This seems to be somewhat of a standard now amongst editors and I am sure that you could use the tool of your choice. My first task was to perform the regular expression to make the csv into an insert statement. Once complete, I used a simple create statement to create the table in mysql and ran the script.

DROP TABLE IF EXISTS `zipcodegeo`;
CREATE TABLE  `zipcodegeo` (
`id` int(11) NOT NULL auto_increment,
`zipcode` varchar(10) NOT NULL default '',
`stateabr` varchar(2) NOT NULL default '',
`latitude` decimal(10,6) default NULL,
`longitude` decimal(10,6) default NULL,
`city` varchar(100) default NULL,
`state` varchar(100) default NULL,
PRIMARY KEY  USING BTREE (`id`,`zipcode`,`stateabr`)
) ENGINE=MyISAM AUTO_INCREMENT=33179 DEFAULT CHARSET=utf8;

Of course there are options when using a create statement, but this is simple to start off with.

Mysql is great for web and networked DBMS, however, I wanted to use something local, and although mysql can be used localhost, I wanted to use something a little more light weight like: sqlite.

Converting to Sqlite

I had a small issue when I created the sqlite db because of a small and somewhat not well known character sequence known as a BOM ( Byte Order Mark). It is a sequence of bytes that appears at the beginning of some text files to signify byte endiness - see: Byte order mark. Fortunately for me one of Notepad++ tools allows you to add / remove the BOM at will. Once I fixed this small issue, the query ran like a charm.  NOTE: the create table statement was adjusted slightly also from the mysql query:

CREATE TABLE zipcodegeo (
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
zipcode varchar(10) NOT NULL default '',
stateabr varchar(2) NOT NULL default '',
latitude DECIMAL(10,6) default NULL,
longitude DECIMAL(10,6) default NULL,
city varchar(100) default NULL,
state varchar(100) default NULL
);
CREATE INDEX ind_city ON zipcodegeo (city);
CREATE INDEX ind_state ON zipcodegeo (state);
CREATE INDEX ind_stateabr ON zipcodegeo (stateabr);
CREATE INDEX ind_zipcode ON zipcodegeo (zipcode);

Creating the Application

I wanted to have a simple interface besides the sqlite console interface because it is less than fun to use on a more than regular basis. Also, it's a lot easier to get other people to use, never mind teaching people how to use a windows like interface than a console application. So I wasnted a rapid application tool that understood sqlite as well as a few other dynamic qualities that processes relatively quickly. I chose Adobe air because it is easy to use and has support for sqlite out of the box.

The interface is simple and is composed of a couple of radio buttons to select the search method and a textbox to type in the item to query for. There is also a simple button that commits the action. When the button is clicked and there is either a valid city, state, or US zip code, a progress bar is shown to show the html generation processing status and also to prevent the main thread from locking the UI. A scrollable list of zipcodes coresponding with lat and long values as well as city and state appear after the html is generated and inserted into the window.

Adobe Air has two main choices for useage, one is the flash /flex api, and the other is the javascript/html api. I chose to use the html api. Feel free to try it out. There are several open source scripts used in this application such as php.js and Keys.js which is an html keypress api composed of a javascript object that contains the microsoft virtual keys into a javascript object.