John Sample

# re: State of the Zips

I'm glad to see you are still alive after the newborn ;) !

What kind of fees (price range) are you thinking of?

# re: State of the Zips

Hey:

I've been checking periodically on your site to see if you've disappeared for good... glad to see you back at it.

I'll check back soon to see what the latest on the project is.

-Mark

# re: State of the Zips

I think it will be a scaled licensing thing. I still want to provide a free version with commercial starting around $100 and going up from there.

# Installer: FTP

A few days ago I was giving myself a headache trying to understand perl to do a geocoder and gave up on perl because I ran out of excedrin. I started working on getting the data into my SQL server and updating the lat/long distance calculators I wrote in VBA to run on T-SQL. After working for a while and geting it working kinda well, I was searching for a way to do regular expresions in T-SQL (HA!) and ran across this site.

Have I mentioned that you are my hero? All bow before John Sample! This is a great tool. I am going to change the field names and such in my database to be closer to yours (I have to follow a naming convendtion, so I cant use the exact names, but they can be close) so youc an use anything I write. The tool I am writing must be entirely T-SQL, and you basicly (I think anyway) stopped updating your T-SQL procedures.

Here are some things I came across:

You could update the file download part of your installer to use the FTP command line in windows. The FTP command line has a command line switch that supports running commands from a file. So you could have a batch file like this:

ftp -A -i -s:<command file name> www2.census.gov

and a command file like this:


binary
cd /geo/tiger/tiger2005fe/<next state>/
mget <filename1>
mget <filename2>
mget <filename3>
mget <filename(and so on)>
cd ..
cd /geo/tiger/tiger2005fe/<next state>/
mget <filename1>
mget <filename2>
mget <filename3>
mget <filename(and so on)>


The file names can be created by going through the file at http://www.census.gov/geo/tigerline/app_a03.txt and building the file names, as they are all 'TGR' + 2-digit state FIPS code' + '3-digit county FIPS code' + '.zip' I haven't built one yet, but I will at some point

I tried using the get all command which would be mget tgr*.zip instead of the mget <filename1> but the FTP server kept kicking me after 3-4 files. Not sure why.

Anyway, by using a file external to the binary installer, the end user (or you :)) could modify the FTP command file to point to a different directoy as the data moves or is updated (looks to be once or 2x a year)

By the way, you mentioned quite a while ago that you would not be updating the T-SQL functions because you could nto do what you wanted in T-SQL, and moved to a .NET DLL wrapper.

Well, I kinda live in T-SQL land, and I wrote my own function for geocoding, just forward only, not like your awesome reverse lookup (I have no use for a reverse version, but your is totaly sweet). I was looking at your functions and they could be a little better.

For example, the use of cursors (you only use one) is a detriment to speed, and the line you are using the cursor on can be rewritten to not use a cursor, and not have that overhead. I am looking at doing hundreds of thousands of addresses a day, so any little bit helps.

Once I am done and have tested it extensively, I will modify the code to work with your table names exacly so you can just toss it in your distrobution. It will likely only be for forward only by address and zip.

Also, there is a regex parcer extended stored procedure for SQL 2000 (2005 too prolly) that uses the perl engine written by Dan Farino here:
http://www.codeproject.com/database/xp_pcre.asp and it works as advertized.

If you want to contact me for something, my email is gearbox80ATcomDONTSENDMESPAMcast.net (remove dont send me spam :) )

# Google Maps API

Also, you are using version 1 of the Google Maps API. The API was updated to version 2, and they are going to turn off Version 1 soon (according to their site, but knowing google V1 will work for a while) so you may want to update your source to use version 2.

Looking at it, your code should work as it is now in V2, just change this:

<script src="http://maps.google.com/maps?file=api&amp;v=1&amp;key=ABQIAAAAW-PEzn9ruvtgpHcCQvOoHRSpdjbQy1f-8tqn9JeXMIcYmGizCBRTYJvOImRVwAmjcQNLDxDjGQYMig"
type="text/javascript"></script>

to this:

<script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAW-PEzn9ruvtgpHcCQvOoHRSpdjbQy1f-8tqn9JeXMIcYmGizCBRTYJvOImRVwAmjcQNLDxDjGQYMig"
type="text/javascript"></script>

# re: State of the Zips

Hi John,

Your site mentions support for MySQL, but I cannot find the link! Could you please send it to me at [email protected]? Also, I'd like to port your code to PostGIS if you don't mind. Of course I'd give you the source to add to the site! I can also port it to Oracle Spatial if you like.