zip code database
-
One thing to watch out for when purchasing a product from a third party...
If the package can be used to validate to USPS CASS certification standards, the data will expire every three months. This means you will need a subscription to the service. MelissaData is a product that exposes this information via it's AddrObject; a COM+ component. You have to keep up on updates or after the DB expiration it simply will fail.
I have run across other problems such as during a monthly update, they changed the component constructor without notification! requiring me to re-code a lot of applications that use this. Do not attempt to pass in "crap" and expect a graceful error or closest match to be returned...
-Mike Gercevich
-
all i got was a couple of text files and one with the data in it... the data below is from the text file..
can some hide some sort of code in here that would shut the table down?
00501,HOLTSVILLE,NY,AD,631,-5,+40.813394,-73.047175
00544,HOLTSVILLE,NY,AD,631,-5,+40.813394,-73.047175
00601,ADJUNTAS,PR,AD,787|939,-4,+18.180103,-66.749472
00602,AGUADA,PR,AD,787|939,-4,+18.363285,-67.180247
00603,AGUADILLA,PR,AD,787|939,-4,+18.448619,-67.134224
00603,RAMEY,PR,AS,787|939,-4,+18.448619,-67.134224
00604,AGUADILLA,PR,AD,787|939,-4,+18.498987,-67.136995
Dam again!
-
Mike is correct.. be careful when you buy Data. If you want to use the data forever (plus some) then you want to buy TEXT files, not some data returned by a black box function call to data on a CD drive.
At the time, (August 2000) data was available as a series of CSV Text files, which I imported into a series of related tables in SQL Server. I think now the data comes on CD, but that may just be the delivery tool. As long as you can still copy the CSV files OFF the CD, it shouldn't be a problem.
Now, if you are using their Mailer's software (MelissaData) which updates addresses on Postal CASS certifications, then yes.. the *software could fail on you when the CASS Certification date expires.
Unless you're in the bulk mailing business.. just buy the raw TEXT files...
-
All that i got was a comma delimited text file... i do not see how they could take that back....
Dam again!
-
You're fine, Erik.. I've had mine in use since Aug 2000 without any problems - other than being a bit on the stale side...
-
well if you need the latest... just let me know
Dam again!
-
does anyone know What is the correct datatype to store latitude and longitude with?
+40.813394, -73.047175
Dam again!
-
Float seems to work.. but then again, I'm not using LAT/LON to calculate anything..
-
i believe that float is the magic type... i am trying to get a grasp on how to set the precision... or what that actully means
??????
Dam again!
-
finally got one of my distance procedures down.....
i'll give this one to the fourm...
i looked all over the net and never found a complete one just like this. this has the function, sproc.... and the lastest zipcode data for the us.
http://afcc1.com/Zips_With_Sprocs.zip
Dam again!
-
OpenACS has a US zipcode file from about 1999 in it, sourced from the US Census' "TIGER" data. This is probably the most recent free dataset out there. If you don't want to d/l OpenACS, you still might be able to get this data from the US Census' website. It's been awhile since I checked on it.
Or, you can order various datasets from the USPS on CD-ROM.
-
I have got a resent set of data... i just posted a link yesterday with it in a zip folder along with two procedures and on function.... For calculating distance.
my problem now is that i can not seem to get a solid piece of code that will help me with this distance between zipcodes and a given radius from a given zipcode..
Got any suggestions?
erik
Dam again!
-
I was playing around once, curious about the mileage thing as well and came up with a query using a local zipcode as a sample.
I normally document this stuff when I pull it from an email thread, but I guess I didn't this time.. sorry for not being able to give credit to the person who helped me with the code.. That being said.. try this:
-- .16 is very close to 1 mile
-- Lat 29.5328 Lon 98.493399 = ZipCode 78216
-- This query will pull
SELECT DISTINCT
dt.Miles,
dt.Zip,
dt.City,
dt.State,
dt.AreaCode
FROM (
select
Miles = Convert(numeric(18,9),SQRT(POWER((29.5328 - Lat),2) + POWER((98.493399 - Lon),2))) / .16,
z.*
from REPORTS.dbo.zips z
where SQRT(POWER((29.5328 - Lat),2) + POWER((98.493399 - Lon),2) ) < .32
) dt
order by dt.Miles
BTW, in MY table, LAT and LON are both signed POSITIVE.
You can play around with the above query to get what you want, hopefully..
- Mark
-
I am not sure if they are suppose to be positive are negitive...
do you know?
i am three days deep into this already..
this is how bad its gotten...
http://www.krysstal.com/sphertrig.html
Dam again!
-
There are two forms of this rule depending on if the values of the two Longitudes. If the Longitudes are both on the same side of the Greenwich Meridian, (i.e both E or both W), the formula is given by:
Cos(DistBC) = Sin(lC)Sin(lB) + Cos(lC)Cos(lB)Cos(LC - LB)
If the Longitudes are on different sides of the Greenwich Meridian (i.e. One is E and the other is W), the formula is given by:
Cos(DistBC) = Sin(lC)Sin(lB) + Cos(lC)Cos(lB)Cos(LC + LB) Cos(LC - LB) or Cos(LC + LB)
Dam again!
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply