zip code database
-
Hello all...
i am in need of a zip code data...
does anyone have one that i can have.?
erik..
Dam again!
-
I'm not positive, but I believe you can get one from the US Postal Service.
-SQLBill
-
I couldn't find one at USPS's website. But if you GOOGLE for "ZIP CODE" Database, there's quite a few choices.
-SQLBill
-
i am all over google.. been there for awhile...
I WAS hoping that maybe that maybe someone here might have a file with some resent data before i spend 40.00 on a download..
erik
Dam again!
-
i found one for 15.00...
i have a question for you..
the area codes are delimited by |...... how do i import the data and remove the |
Also, the data has the Longitude and Latitude.. ....can i user this to measure the distance between to zip codes?? and how.
say if i have a user the lives in the 00501 zip code... and is looking for a contractor... i would like to Select top 5 Zipcodes where 00501.... and this is where i would like to add a radius of like 50 miles or so..
any suggestion
erik
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
00604,RAMEY,PR,AS,787|939,-4,+18.498987,-67.136995
00605,AGUADILLA,PR,AD,787|939,-4,+18.433347,-67.153184
00606,MARICAO,PR,AD,787|939,-4,+18.182151,-66.958807
00610,ANASCO,PR,AD,787|939,-4,+18.288319,-67.136046
00611,ANGELES,PR,AD,787|939,-4,+18.287343,-66.799976
00612,ARECIBO,PR,AD,787|939,-4,+18.449732,-66.698797
00613,ARECIBO,PR,AD,787|939,-4,+18.463319,-66.731857
00614,ARECIBO,PR,AD,787|939,-4,+18.463319,-66.731857
00616,BAJADERO,PR,AD,787|939,-4,+18.426748,-66.676692
Dam again!
-
now that i have the data in a table in sql2000 i can see where the area code are ment to be delimited this way |
Can anyone help me with the measurement of miles.. or distance from the Longitude and Latitude.. ?
Dam again!
-
the following may be of use to you.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57242&SearchTerms=longitude,latitude,zip
-
Instead of writing your own maps/geography database and application, there are some other alternatives:
1. There are APIs available to the major mapping service including Goggle, MapQuest and Yahoo.
2. Street Address to latitude/longitude services are available on the Internet with the data based on the US Census Bureau Tiger database.
Just search the Internet to find the providers.
SQL = Scarcely Qualifies as a Language
-
Can you give us the URL for this $15 solution?
-
You can get a tab delimited dataset with Zipcode, City and State from the US Postal Service. Typically they do not charge for this. My organization uses it to help with order entry...simply enter the zipcode and the valid city and strate are popped into the order.
The people to contact are with the USPS Business Services Network. The link is...
http://www.usps.com/nationalpremieraccounts/bsn.htm
Martin Vrieze
-
If you do not find what the zipcode table that you are looking for i will be glad to share the one that i have with you... it has over 50,000 records (unique)
let me know if you need it and i will post the data to a hyperlink.
erik
Dam again!
-
Ok this is what i was able to finally dig up on the net... and i think that i hit the jack pot! hee HAWWWW
Only one problem.... i do not have time to go to college and learn trig in order to see if these two procedures are valid. What i am in need of is two procedures.... on that will return a certain amount of zipcodes with in a given radius..........and another that wll caluclate the distance between two different zipcodes..... ( i do have the latitude and longitude fields in my zip table in the database.)
help,,,help!!!
erik
--**************************************
-- Name: Procedure to calc miles distance from two points on earth
-- Description:This procedure is used to calc miles distance from
-- two points on earth. You can get latitude and longitude for places on
-- earth, then just use this procedure to calc how many miles are in between.
--
--**************************************
CREATE Proc dbo.sp_MilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0 ,
@P_DiffMiles decimal(10,2) =0 output
)
As
Begin
/*************************************************************/
/* Routine to calc miles distance from two points on earth */
/* specified in latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
set nocount on
declare @Lat1 decimal(28,10)
declare @Long1 decimal(28,10)
declare @Lat2 decimal(28,10)
declare @Long2 decimal(28,10)
declare @d decimal(28,10)
/* Default return value */
set @P_DiffMiles = 0
/* Convert to radians */
set @Lat1 = @P_Lat1 / 57.2958
set @Long1 = @P_Long1 / 57.2958
set @Lat2 = @P_Lat2 / 57.2958
set @Long2 = @P_Long2 / 57.2958
/* Calc distance */
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))
/* Convert to miles */
if @d <> 0
begin
set @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
end
GO
/* DECLARE @miles int
/* EXEC @miles = sp_MilesLatLong 41.598994, -73.996119, 41.768420, -73.960450
/* SELECT miles = @miles
============================================
CREATE TABLE places
(
statecode char(2),
zipcode char(5),
lat float,
long float
)
insert into places select 'AL', '35085' ,32.965120 ,-86.744053
union all select 'AL', '35087', 34.303718 ,-86.583234
union all select 'AL', '35089', 32.941708 ,-86.060988
union all select 'AL', '35091', 33.771090 ,-86.806727
union all select 'AL', '35094', 33.530698 ,-86.555065
union all select 'AL', '35096', 33.605233 ,-86.120796
union all select 'AL', '35097', 33.896526 ,-86.630569
union all select 'AL', '35098', 34.120006 ,-87.050758
union all select 'AL', '350HH', 33.464476 ,-86.577571
union all select 'AL', '350XX', 32.978292 ,-86.499261
union all select 'AL', '35111', 33.279946 ,-87.101488
union all select 'AL', '35112', 33.681899 ,-86.482375
union all select 'AL', '35114', 33.225377 ,-86.864434
GO
CREATE PROC up_FindZipCodesWithinRadius
@ZipCode char(5) ,
@GivenMileRadius int
AS
SET NOCOUNT ON
DECLARE @lat1 float,
@long1 float
SELECT @lat1= lat,
@long1 = long
FROM places
WHERE zipcode = @ZipCode
SELECT ZipCode ,MileRadius
FROM
(
SELECT ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))), 2)) /
((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))))) MileRadius
FROM Places
) a
WHERE a.MileRadius <= @GivenMileRadius
AND ZipCode <> @ZipCode
ORDER BY MileRadius
GO
EXEC up_FindZipCodesWithinRadius '35085',20
GO
DROP PROC up_FindZipCodesWithinRadius
--DROP TABLE places
Dam again!
-
We bought a Zipcode database a few years back and it does what we needed at the time and we have not updated it since.. although we could probably use an update. Things probably have changed since Aug2000...
The product is called Zip*Data and was provided by:
Mailer's Software
800-800-6245
http://www.MailersSoftware.com
Besides the obvious contents of ZipCodes, City, State, FIPS Code, it has alot of other information spread out in several CSV files. You may not need all of it (we didn't) but something nice to have for our Marketing Dept was getting the MSA for each ZipCode. MSA's are Metropolitian Statistical Areas and PMSA's are Primary Metropolitian Statistical Areas - usefull when studying areas like Chicago, which could encompass many zipcodes that could cross state boundaries. Or you do like we do and edit the MSA's yourself and force a Customer to show up in one MSA or another by changing the MSA value for his zipcode...
Other items of interest are the LAT/LON, Elevation, AreaCode, SqMiles, CountySeat, AvgIncome, AvgHomeValue and other Demographic info..
Hope this helps.. (no I don't work for them - in case this sounds like an advertisment)
-
Thats a really nice site... i am in the market for thay type of data when i get the site up and running... the New home owner list is a for sure need..
erik
Dam again!
-
The SQLteam links really hold a wealth of info on this subject. The approach discussed by travisl (on the sqlteam site) with reference to the codeproject link seems great. I know I haven't had a great deal of use for trigonometry since junior high and high school but the formulas don't seem that complex. The difficult part seems to be that there are multiple formulas for for calculating the distance between two points and they may produce different results depending on lots of variables. Not the least is that many trug formula assume the angle and distances are on a flat surface, but the earth is curved. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12572 for discussion on this issue. I'm not sure this is a job for T-SQL. Perhaps some 3rd party software may be a better solution. See http://www.zipcodedownload.com/Products/Product/DistanceAPI/Standard/Overview/ as an example. It has a tutorial for general distance calculating based on zip codes and an API accessable from multiple languages, C#, VB etc. It works with SQL Server. There are likely other software that helps with this. Good luck, its a challenge.
Francis
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply