Posted by david on 1. February 2009 20:39
Built up a new page, 2002_02_02, using the styling of yesterdays page. I like it, it looks clean for this kind of test work. Not too much of a hassle.
The Plan:
add db connection, the mapsiteLinqToSQL
add a gridview,
take a crack at updating the records WHERE (Lat or Long IS NULL) AND (address IS NOT NULL)
source for the update will be the geocode service.
Questions to be answered:
how to iterate through,
is there a way to batch 'em?,
what will happen to the rest of the page as the thing is processing?
I’ve got, I think, 400 parks that need to be updated. that’s 26 minutes, if it takes 4 seconds a record. That sucks.
this is a one time thing, for the most part.
In most cases, a record would be added in singles, and a 3-4 sec lag to get a LatLong wouldn’t be so noticable … especially since it’s asynchronous.
Further, this might be a background process ( if there is such a thing ) … again probably won’t need to worry, cuz nobody is going to be batching in lists of addresses .. but say this was a live site, & I did have 400 parks to be geocoded. and say someone hit the site…. ( i can see this making little sense) & say there was the capability to import a list of addresses & say as soon as they got that imported, the app would start iterating through the UNgeocoded locations, getting a latlong for each one – but say the user was impatient & wanted to see one of their spots on the map immediately ( reasonable ) … so they click to show it, & the app would process that immediate request immediately, then return to it’s chugging through the list.
Again, this is nutty. This should be done in SQLServer 08. but SQL Server 2008, for all the hoopla about it’s geospatial capabilities, doesn’t geocode – there is not an easy to implement function so you could just throw an update sproc at a table. That link there, by Johannes Kebeck, is a step by step on how to connect the MapPoint Web Service to SQL08 & do what I’ve been implementing in the app. In fact, bloody hell, he’s got all the code for download.
His situation is a database which is having records inserted, with only addresses as the geo data, before the insert is done, an attempt is made to get the latlong, if successful plug it all in to the db, if not just plug the original data in & null for the lat longs. He is using a CLR Trigger – the db calls on some .NET code to deal with the web service & build the appropriate INSERT command. The Trigger is what calls the CLR … looks like it intercepts any INSERTS on the table ( the trigger part ) & replaces the INSERT with the output of the CLR ( a revised INSERT statement ),
The difference between our strategies is that I’m dealing with the missing latlong data at the application level, rather than the db level.
Why is there no easy function? – maybe because addresses are dynamic – they must be a search operation … mmmm not sure about that … the question is what is the web service actually doing to return the lat long? Does it have a giant list of addresses to sort through … is it running the address through an equation? If that were the case, then the easy function should be in TSQL … more likely: addresses are transient … a street name, a city even, are impermanent labels for fixed locations.
So you send a web service an address … it says to itself:
“hmmm, what does 3636 Manderly Pl, Fort Worth, TX 76109 mean to me today?”
“hmmm,” it may continue, “OK – 76109 is a valid zip code … I can give you a lat long for that”
“Now let me look through all the streets I have in 76109 – or in TX – or in Fort Worth, TX”
“ … OK, got it … whew that’s a lot of streets … where’s Manderly Pl. … OK OK there it is “
“ … is 3636 a valid house address on Manderly Pl.? … let’s see … “
“ Boom! there it is … I gotcha Lat = 32.690683; Long = 97.374533. Sorry that took so long! Next!”
A lat long is a solid, fixed, touchable truth. An address is much less. ( note the variance in my comparison data a the bottom of the 2009_02_02 page )
That’s why all map searches take a couple of seconds.