First time here?

You are looking at the most recent posts. You may also want to check out older archives. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS or email. Thank you for visiting!

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 (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 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.


The opinions/content expressed on this blog are provided "ASIS" with no warranties and are my own personal opinions/content (unless otherwise noted) and do not represent my employer's view in any way.