Dealer locater by zip code

seems like this is something google or yahoo would have a solution to in their map api

Thanks Kotis, but I wonder if that will work.  The first thread I found, where someone asked a similar question to Google, they told the user he couldn't use the API that way.  That he had to purchase a Zip Code DB or it would violate their terms.  I realize I might need this as well so am looking to anyone who's done this and has a great answer for me.  

Unless I'm reading thread wrong.  Do you have an API in particular that you recommend?

first off - there are several zip code databases available for the US & Canada
i just quickly googled this - don't know anything about it's quality - but here is a link:
http://www.zipcodedownload.com/Products/Family/ZIP5/?cid=ZC:1GZCp-family
And as you say in your post - yes you do need the db to contain latitude & longitude.


secondly - are you wanting to write a dealer locator function or are you looking to purchase someone elses code?

Thanks...while I was hoping for a recommendation of a company someone's had good luck with, I guess beggers can't be choosers...so thank you for the link.  

As for the code, I was hoping there was some JavaScript or CF code that would do the trick.  Currently, I have a table with all of the address, City, State, Postal Code, and Country of all the dealers.  Until I figure out the zip code locator, I just have dropdowns that auto-populate after selecting Country, then State, then City, but the client would like a zip code find that would locate the closest stores...even if that means the closest store is 100 miles away.  So any help would be greatly appreciated.

within the us and canada - this is easy
other than that - good luck ...
hard to find the data

i have the code laying around somewhere (i no longer use it)
will try to find it ...

You're the coolist!!!  I think for now, US and Canada will be fine.  They are a start up, which is why we need low-cost solutions.  I did see that the zip code site you recommended also has a product I could purchase which could help write CF code, but maybe we start with what you have, and as they grow, consider the other option. Let me know.  Thanks.

SELECT Top 10 -- 10 would be how many results you want returned
      (3959 * ACOS(sin(a.latitude/57.3) * sin(b.latitude/57.3) +
      cos(a.latitude/57.3) * cos(b.latitude/57.3) *
      cos(b.longitude/57.3 - a.longitude/57.3))) AS Distance,
      b.Company,b.Address,b.City,b.State,b.ZipCode
      FROM ZipTable a, DealersTable b
WHERE b.Zipcode = '#form.Zipcode#'
ORDER BY Distance

just change DealersTable to your real dealers table name - latitude and longitude are required columns!!!
to set it all up in the beginning just get each dealers lat / lon from the zipcode db you end up getting

and just change ZipTable to your real zipcode table name

you may need to change column names in this query

important!!!
if you get canada's postalcode db also I would reccommend combining the us zipcode table with it
will make lookups faster and easier
IF this is the case then the column data type needs to be nvarchar since that what canadian postal codes are.

any questions just ask ...

btw - i realize that you can't test this until you get a zip database but i guarantee it works great!

You ROCK!!!  I'll give this a try and let you know.  Thank you so much!!!

OK...got the DB, loaded it into SQL, added your code (adjusting the name of course)

SELECT Top 10
(3959 * ACOS(sin(a.latitude/57.3) * sin(b.latitude/57.3) +
              cos(a.latitude/57.3) * cos(b.latitude/57.3) *
              cos(b.longitude/57.3 - a.longitude/57.3))) AS Distance,
              b.*
            FROM       ZipCodes a, DealerDirectory b
            WHERE       a.Zipcode = <cfqueryparam value="#form.zipCode#" cfsqltype="cf_sql_integer">
            AND            b.Country_ID = <cfqueryparam value="#form.Country_ID#" cfsqltype="cf_sql_integer">
            ORDER BY Distance

Sorry...wasn't finished...anyway, I'm getting weird results.  W/some zip codes, everything comes in perfectly.  Others, I'll get 1 result, others, I'll have 2 of each record, so only really 5 results.  And others, 10 of the same record.  Any ideas???

don't know about some of it, but if you have country code then you may want to look into that because only the US would have a zipcode that is an integer.

so you'd want to check that the data type of z.zipcode is varchar and change the cf_sql_integer to cf_sql_varchar

that is if you have other countries like canada

start with that
also, can you post an online form so i can test - just something real simple that allows a zipcode to be entered and cfdump the query results

OK...I have the locator published so you can see it.  I hope this is what you wanted.  It's not linked on the site, but here's the direct link: http://tieboss.com/DealerLocator.cfm.

They only have 23 dealers at this point, but here's what I tried.  55119 displays 5 listings...2 times each.  55110 displays one listing 10 times. 55402 displays 10 perfect listings.  

Regarding the Country_ID...if they add a zipcode, the results page recongnizes this and set the Country_ID to the US ID, so that's not it.  At this point, they don't have any dealers outside of the US.  The Canadian one I have in the dropdown below the Zip field is is just for test purposes.  So, I added some error catching to bounce them back to the locator page if they key-in a non-US zip.  Hope this helps.

NOTE CHANGE OF URL...I had to rename my file because the client wanted to go live with the Country/State/Zip search as we address the zip search.  So, now you'll need to go to http://tieboss.com/DealerLocator2.cfm.

ok - so the zipcode database is something you purchased - right?
and the dealer database has latitude & longitude in it - right?

can you do a cfdump (after the query) so i can see the values please

Yes, I purchased the DB through the link you gave me above.   Hopefully that's not the problem.  And yes, I have the lat/lon in the Dealer table.  Sorry for not doing the CFDump...it's there now.  Hopefully this will be enough...you've spent so much time with me I really appreaciate it.  

try doing just

WHERE a.Zipcode = #form.zipCode#

in other words
remove the country code and the cfqueryparam so i can look at it

Done...try it now.

remove b.* and put these columns for now
b.COMPANY,            
b.ADDRESS,
b.STATE_ID,
b.ZIP,
b.ID,
b.LATITUDE,
b.LONGITUDE

fyi - noticed that at least the one dealers has empty string for lat / lon
can you also do this for me and put it at the bottom of the demo form
<cfquery datasource="#whateverDSN#" name="x">
select *
from DealerDirectory
</cfquery>
<cfdump var="#x#">

Sorry it took me a bit...I had to add all the fields I needed otherwise the result would bomb.  Note...the one dealer without a lat/lon is the Canadian dealer.  So, that was the result of removing the Country_ID.  Also, there are 263 dealers in the table but only 23 are publiished for display.  So that CFDump shows all.

FYI - I don't know if this is helpful, but I when I uploaded the zip data to SQL from the file I received from the vendor, it set the field types for lat/lon to decimal(9,6),null).  I left it that way and made sure the lat/lon fields in the DealerDirectory were consistant.  Also, I searched to see if there were any empty or '0' lat/lon fields in the zipcode table.  The only ones that came up were zips for Armed Forces which wouldn't be used.  

just fyi - i'm about to get busy ...

I understand.  Just let me know when you can.

where is the

<cfquery datasource="#whateverDSN#" name="x">
select *
from DealerDirectory
</cfquery>
<cfdump var="#x#">

i think you dumped the zipcode table instead ...
i see the page won't load due to timeout
just remove the dump of the zipcode table if thats what you did ...

That's just weird...no...I'm querying the DealerDirectory table which only has 263 records in it, so that shouldn't be timing out.  It worked find on my computer, so I'm not sure what's going on.  I added it to the initial form page.  I just set the maxrows to 50 so you could see something.  Will that work?

post this on form page also
<cfquery datasource="#Application.DSN#" name="y">
select id
from dealerdirectory
group by id
having count(id) > 1
</cfquery>

<cfdump var="#y#"><CFABORT>

does id have a unique constraint?

not quite sure if it matters but
convert your lat / lon columns to float

and i still think you need to also convert zip to varchar as well
then you'd need to do where z.Zip = '#form.zipcode#' - note the single quotes

on action page use this instead of just the zip

WHERE len(isNull(b.latitude,'')) > 0 and len(isNull(b.longitude,'')) > 0  and a.Zip = 'xxx'

Done, but doesn't seem to make a difference.  Question...the Zipcode table doesn't have an ID field of sorts.  I don't know if that matters, but we typcally always include an auto-numbering ID field.  While we wouldn't use it for anything, does it do anything for indexing that table???

the reason i posted the query for you to run is to see if you had any duplicates
you didn't post it though ...
i mean you may not have any but just trying to determine

also  you didn't put in
WHERE len(isNull(b.latitude,'')) > 0 and len(isNull(b.longitude,'')) > 0  and a.Zipcode = 'xxx'

Sorry about the WHERE...I missed that post.   I added that now.  

As for dups, I checked that out and don't see anything. I created another CFDump page that just has that query.  It still bombed out on the whole table, but I changed it to max 100 if you wanted to see that.  It's cfdump.cfm.  I sorted by zip, then company.  

did you change the lat / lon (in both tables) to float?

the id is UNIQUE in dealers table - is that correct ?
the ONLY reason i'm asking is to see if when i see duplicate id then is it a duplicate entry or the same record pulled up twice - know what i mean ...
i know it's not used in calculations however don't underestimate the reason i wanted to know
even if you don't know what i mean - please dbl check that there are not any duplicates in the id column of dealers table.

change the sql to
WHERE len(isNull(b.latitude,0)) > 1 and len(isNull(b.longitude,0)) > 1  and a.Zipcode =


it must have something to do with lat lon datatype ???
i just set this up recently to help someone else and it works great

in the whole formula there is only 5 required fields
z.lat,a.lon,a.zip,b.lat,b.lon
the zip is only to get access to a lat / lon starting point from zip table

Yes, lat/lon is float in both tables.
Yes the ID is unique in the dealer table, and I totally get what you're saying about the duplicate entry.   It's set to Identity-Yes, Increment-1, Seed-1.

Here are the fields in the Zip Code table if this helps.

ZIPCode      varchar(50)      Checked
ZIPType      nvarchar(1)      Checked
CityName      nvarchar(64)Checked
CityType      nvarchar(1)      Checked
StateName      nvarchar(64)Checked
StateAbbr      nvarchar(2)      Checked
AreaCode      nvarchar(16)Checked
Latitude      float      Checked
Longitude      float      Checked

Very odd!

all i can suggest is to send me an excel sheet of your dealers so that i can plug it in here
all it needs to contain is dealer name & zipcode - just those two columns

let me know ...

Is there a way to do this without uploading it to this post?  I say that because these are my client's dealers and a list of dealers they 'hope' to signon.  So I really can't publish them here if the list can be seen by anyone who stumbles upon this thread.  Sure, they can see them on their site, So, how do we do this without giving out our email or phone to the world???

post the name of a site that has a contact us form that you will get where i can then post my email address for you to send it to

You're too cool...icbits.com

To anyone reading this post, the initial code given to me by dgrafx above was perfect!!!  It ended up being an issue with the zip code table...there were duplicate zip codes where a group of small cities share the same zip.  Once I grouped them, by zip...it worked!  THANK YOU SO MUCH DGRAFX for all your hard work!

dgrafx was AWESOME!!!  He stuck with me and trouble-shot through every possible scenario.  It ended up the issue was with duplicate entries in the zip code database I received from a vendor.  So, his initial code was perfect.

glad to help Bob :)
and thanks for the points ...