| Comments

for the past few days i've been sitting in a meeting.  today in the US is the superbowl (the national football championship game).  i'm not much of a football fan, but i'm interested in the outcome so i don't sound like an idiot in conversations :-)

so while this meeting is going on right now, the game has been going on.  i decided to take a look and went to *the* sport site, www.espn.com.  they have a "gamecast" application which provided me some great "live" action on the game.  take a look at the screenshot:


you can see a virtual field that (it isn't in this shot since it is halftime) shows the plays on the field, the chain markings for the downs, etc., etc. -- and it tells me about the last play -- etc.  a great UI and great information -- no refresh, very slick (would have been nice to have this in a sidebar gadget!).

for contrast i went to msnbc.com -- here's their "live" play:


horrible.  "select your refresh" setting at the top (read: no ajax style).  very "html" looking.  information in a scrollable page (not the 'first quarter' table at the bottom) -- so information not in a concise place.  shame on msnbc :-)

| Comments

there's been some questions to me and in my local community about my vague references to my geocoder provider (sorry andrew).  the demonstration/explanation of that provider is here in my "geocode provider" post (not sure why i didn't make that part of the code gallery originally, apologies -- it now is).  i hope that helps.

i've also just uploaded an example of how you can use virtual earth for geocoding...i've wrapped it in a windows forms app to show that you can leverage virtual earth in a windows forms application as well.  i hope that helps.

basically what happens is the form calls a script:

   1:  private void button1_Click(object sender, EventArgs e)
   2:  {
   3:      AddPushpin.Visible = false;
   4:      executeScript(ClearAllPushpins);
   5:      executeScript(FindLocation, LocationToFind.Text);
   6:  }

on the html page implementing the map rendering, i have a callback function that calls back into my winforms, providing the latlong:

   1:  function OnFoundLocation(e)
   2:  {
   3:      if (e.length > 0)
   4:      {
   5:          var latLon = e[0].LatLong;
   6:          window.external.LocationFound(latLon.Latitude, latLon.Longitude);
   7:      }
   8:  }

File: Virtual Earth Geocode in WinForms

| Comments

trying to wrap your head around .net 3.0 technologies?  well, here's a free sample to help you see several different aspects of it.


is a sample application that leverages all aspects of .net 3.0 -- and the source is provided!  workflow -- it's there, communication foundation? -- yep.  sprinkle in some cardspace and some wpf and there is a full application with different aspects demonstrated.

this sample is a pretty good starting guide (with some advanced features) to learn these technologies.

check it out at www.dinnernow.net and download the code!

| Comments

or "find stuff near me" and how i did it. (long read sorry)

after my last post about using the proximity search article for sql server 2000, i received some requests for a simple walkthrough on how i implemented my app.  well, here's an attempt at a very simple sample.

first, so we are clear, my scenario was as follows: given a known single point, search a database of other known points and return a result set of those points that are known to be within a certain mile radius of the single known point.  better put "find locations near me" :-)

let's use a sample database for the known points.  i'm using sql server 2005 express which is free.  here's my table structure for known points (for this sample):

   1:  CREATE TABLE [dbo].[Locations](
   2:      [LocationId] [int] IDENTITY(1,1) NOT NULL,
   3:      [LocationName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   4:      [Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   5:      [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   6:      [State] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   7:      [Zip] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   8:      [Latitude] [float] NOT NULL,
   9:      [Longitude] [float] NOT NULL,
  10:      [XAxis] [float] NOT NULL,
  11:      [YAxis] [float] NOT NULL,
  12:      [ZAxis] [float] NOT NULL,
  14:  (
  15:      [LocationId] ASC
  17:  ) ON [PRIMARY]

once you have the locations, there are two key steps here: 1) geocoding those locations and 2) establishing the proximity calculations for the axis points.  the first is rather simple.  you essentially have to use a geocoding service to perform the action.  in my application i use my and the yahoo geocode apis.  i've found them to be accurate and current.  google also has some as well.  both are 'free' (certain number of transactions) but you have to register your application.  i think yahoo is also implementing referrer-based checking as well, which might be a consideration.  either way, these will geocode your address.

suggestion: you can abstract this from your users very easily.  when they enter an address to save into your application UI, take an intermediary step to verify and geocode the address...once you have the lat/long then you are ready to save it to the database.

the next step is calculating the axis.  i'm honestly not going to sit here and pretend to tell you that i'm a geospacial expert on the laws of longitude, because i'm not.  basically the math that is performed makes calculations based on the radius and axis point of the earth.  no, i'm serious.  you'll see later one of the parameters is earth radius.  once you have the known lat/long of your addresses, then you have to calc the axis points.  to simplify this for me, i created three functions in sql server like this one:

   1:  CREATE FUNCTION [dbo].[XAxis] 
   2:      (
   3:      @lat float,
   4:      @lon float
   5:      )
   6:  RETURNS float
   7:  AS
   8:      BEGIN
   9:      RETURN COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lat) * COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lon)
  10:      END

the others are similar and you can get the scripts at the bottom of this post.  once you have those in your database for reusability, you have two options: stored procedure or trigger.  stored procedure (or rather, implementing within your insert/update routine) is probably most ideal.  you want to ensure the axis calculations (and also the geocoding) maintains integrity.  after all, if the user updates the address, you want to update all that information!  so within your stored proc you can simply update the XAxis, YAxis, and ZAxis fields using these functions using something like:

   1:  UPDATE Locations SET XAxis = dbo.XAxis(@lat, @long)

you could also do this in a trigger (as i was using), but it was pointed out to me (thanks bert) that using an AFTER UPDATE trigger might send this in an infinite loop as my trigger was performing an update statement itself.  stored proc is the way to go if you can.  i've included the INSERT trigger in the files just for your perusal though.

okay, with that done, you should have a sequence of when an address is entered (again possibly adding the intermediary step of geocoding for the user) you now have all the information you need.  now when your user needs to perform a "find near me" query the process is simple.

first, you'll need to geocode their asking point.  again, this is simple and can be accomplished by various geocoding apis.  once you have the lat/long of the asking point, you can feed them to the stored proc that will use your previous proximity information and find the ones nearest you.  here's the stored proc:

   1:  CREATE PROCEDURE [dbo].[FindNearby]
   2:     @CenterLat float, 
   3:     @CenterLon float, 
   4:     @SearchDistance float, 
   5:     @EarthRadius float
   6:  AS
   7:  declare @CntXAxis float
   8:  declare @CntYAxis float
   9:  declare @CntZAxis float
  11:  set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
  12:  set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
  13:  set @CntZAxis = sin(radians(@CenterLat))
  15:  select *,  ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + 
  16:    YAxis*@CntYAxis + ZAxis*@CntZAxis)
  17:  from  Locations
  18:  where  (@EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + 
  19:    ZAxis*@CntZAxis) <= @SearchDistance) AND latitude is not null
  20:  order by ProxDistance ASC

you'll notice the parameters of radius and earth radius.  since i'm using miles, i use the earth radius of 3961 miles.  you can search on live.com and other places for another number, but this seemed to be a general consensus of the radius of the earth in miles.  i put this in my configuration file in case i needed to change it.  no, not in case the earth changed, but in case it needed to be kilometers.  the SearchDistance param needs to be in the same unit of measurement as the earth radius.  feed those and your lat/long in and you get those near the point -- as well as the approximate distance in the same unit of measure.  boom, you are done.  do with the data as you wish.

using virtual earth, you can easily plot those points as i did in my sample application.  heck using you could also geocode for you in your user interface.

suggestion: you could present a map in your UI.  have the user enter an address...if found you can have them click on the point to verify and capture that lat/long from virtual earth, then sending back to your procedure and skipping that functional step on the backend.

that's it.  below are the files for the scripts to play around if you'd like.

Files: ProximitySampleSqlScripts

| Comments

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.