proximity searching in zip codes

recently i was working on a proof of concept for virtual earth, etc.  i wanted to do better "find stuff near my address" but have never worked with geospacial proximity before.  so on the quest i went for more information.  i wanted something better than my "select * where zipcode = @zip" query :-).  i was looking for "select * where proximity is within 5 miles of @zip" to show some true calculation.

a friend of mine turned me on to this article regarding implementing proximity searching on sql server 2000.  i read it and it was intriguing (and works on sql 2005 as well) so i gave it a go.  note: atn is not a built-in function for sql server (likely why they used access in the sample, but i bypassed that. 

i had addresses in my database, but not all were geocoded, so i whipped up a quick app using my geocode provider (which default uses yahoo geocoding) and went to town geocoding 2000+ addresses.  well either yahoo didn't like that kind of activity (or they probably didn't appreciate me using the appkey 'yahoodemo' either!) but it started to fail.  i got most of them in which was adequate for my sample.  the ones that failed actually had apartment numbers in their addresses, etc.  ideally this would be done when the address was inserted from the user interface (geocode at point of save/update).

the second part was to do the proximity calculations update, for which i used the article.  again, ideally this could be a trigger on the insert once you know the lat/long of the address -- i didn't write that up, but that would be a good idea to do as a function or something.

my end result was everything working perfectly as i needed. -- thanks doug for the pointer!

i then wired it up to a web service that i call using asp.net ajax.  then wired that up to virtual earth and boom, done.  i decided to use custom pushpins and format the details pane a little bit more than the default.  the one thing i wanted was to format it even more, which can be done using the VEPushpin customization of the ShowDetailOnMouseOver and OnMouseOverCallback -- both of which worked, but are applied globally to the VEPushpin objects.  the one thing i didn't like was the lack of OnMouseOutCallback...where's that! 

at any rate, my sample refactored and is working.  i made a modified version of what the arizona department of public safety provides for searching for sex offenders in your area.  mine uses virtual earth (the data is publically available).  you can see it at http://azso.asyncpostback.com.  you can use zip codes like: 85032, 85242, anything in arizona only.