Coldfusion Zip Code Radius Query: coldfusion, code, zip, radius

by: dgrafxPosted on 2008-01-14 at 03:12:39ID: 20652200

Hello Electricians
I don't quite understand what you want to get.
Do you want to get the users city from their zipcode?
or
Do you want to get neighboring cities from what they enter?
I guess I don't understand exactly what you are wanting to do.

by: aseusaincPosted on 2008-01-14 at 05:23:59ID: 20652662

You said you already have a database of zip codes. You'll also need columns for latitude and longitude for each zip. This one isn't too bad: http://www.populardata.com/downloads.html

Then, this formula will give you the distance in miles between two sets of LAT/LONG:

<cfset radlat1 = ((pi() * lat1)/180)>
<cfset radlat2 = ((pi() * lat2)/180)>
<cfset radlon1 = ((pi() * lon1)/180)>
<cfset radlon2 = ((pi() * lon2)/180)>
<cfset theta = lon1-lon2>
<cfset radtheta = ((pi() * theta)/180)>
<cfset dist = ((60 * 1.1515) * (180 / pi()) * (ACos((Sin(radlat1) * Sin(radlat2))
+ (Cos(radlat1) * Cos(radlat2) * Cos(radtheta)))))>

<cfset radlat1 = ((pi() * lat1)/180)>
<cfset radlat2 = ((pi() * lat2)/180)>
<cfset radlon1 = ((pi() * lon1)/180)>
<cfset radlon2 = ((pi() * lon2)/180)>
<cfset theta = lon1-lon2>
<cfset radtheta = ((pi() * theta)/180)>
<cfset dist = ((60 * 1.1515) * (180 / pi()) * (ACos((Sin(radlat1) * Sin(radlat2))
 + (Cos(radlat1) * Cos(radlat2) * Cos(radtheta)))))>

1:
2:
3:
4:
5:
6:
7:
8:

Select all Open in new window

by: ElectriciansnetPosted on 2008-01-15 at 06:36:42ID: 20662883

Hello again dgrafx:, I currently have the members inserting every zip code in their service area. However, when the end user is looking for an electrician (member) they have to hit exactly on one of those zips which is not working out too well.

Hi aseusainc:. This looksw really cool and is probably my answer but could you lower the cookie jar a bit? I'm not too sure how this would come together.

by: dgrafxPosted on 2008-01-15 at 08:07:11ID: 20663751

ok - i see
yes - the method aseusainc posted is good
here is a different approach

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.membername,b.city,b.state
FROM aTable a,bTable b
WHERE b.Zipcode = '#form.Zipcode#'
ORDER BY Distance

1. you need a lat / long database that also has zipcode (that would be atable above)
2. don't know how you have your member table setup but btable is your members table where the end user searches for zips (form.zipcode)

by: ElectriciansnetPosted on 2008-01-16 at 19:24:06ID: 20678436

I have a downloaded table called zipcodedatabase that contains 42,000 zips complete with lat and long. I have table members and I have table memberzip. currently when a member is enrolling, the members new memberid is aoutonumber and is assigned in table members. The next thing that happens is the memberid is inserted into memberzip, sometimes hundreds of times, again and again with every zip code they have in their cfgrid. In short, zipcodedatabase is queried to populate the cfgrid based on the service areas they choose, then inserted into memberzip.
The reason I'm telling the both of you this is because your code looks very advanced and I'm not too sure how to apply it to what I already have and I would like not to break anything. I'm going to paste enroll.cfm so you can see what is currently happening

enroll.cfm:
 
<cfif session.debug>
  <cfdump label="form information from city.cfm" var="#form#">
  <cfdump label="session" var="#session#"> 
  <cfdump label="cffile" var="#session.myFile#">
    
  <cfelse>
 
 
<cfif isDefined ('form.submit') AND form.carrier IS NOT " ">
<!---<cftransaction>--->
 
<!---[dbo.members]--->
<cfquery name="insertMemberData" datasource="#enet#">
 INSERT INTO dbo.members (companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, PassWord, Contact_URL, License, CellNumber, NetworkID, member_Since, Slogan, isDeleted) VALUES ('#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.passWord#', '#form.url#', '#form.license#', '#form.cell_phone#', '#form.carrier#', getdate(),
'#slogan#', 0)
SELECT id1 = SCOPE_IDENTITY()
 
SET NOCOUNT OFF
</cfquery>
<!---[dbo.memberzip]--->
<cfset ID1=#insertMemberData.id1#>
<cfloop index = "ListElement" from="1" to=#arraylen(Form.zipList.zip_code)#>
<cfif form.zipList.checked[ListElement] eq 1>
<cfquery name="insertZipCodes" datasource="#enet#"> INSERT INTO dbo.memberZip(memberID, zipCode ) VALUES (#ID1#, <cfqueryparam value="#Form.zipList.zip_code[ListElement]#" CFSQLType="CF_SQL_VARCHAR" >)
</cfquery>
</cfif>
</cfloop>
<!---[dbo.location] future use--->
<cfquery name="insertLocale" datasource="#enet#">
INSERT INTO dbo.location(memberID, lat, lon, city, state, zip)
VALUES (#ID1#, '#form.lat#', '#form.lon#', '#form.city#', '#form.states#', '#form.memberzCode#')
</cfquery>
<!---[dbo.logo]--->
<cfif isdefined ('form.fileNameField') and form.fileNameField is not "">
<cffile action="readBinary" file="c:\inetpub\wwwroot\electriciansnet.com\picts\member_logo\#session.myFile#" variable="binImage">
 <cfquery name="insertBlob" datasource="enetdb1sql">
      INSERT INTO logo (memberID, img, fileName)
      VALUES (#ID1#, <cfqueryparam value="#binImage#" cfsqltype="cf_sql_blob">, '#session.myFile#' )
   </cfquery>
</cfif>
<cflocation url="mailer/mailer_member.cfm?company=#Form.company#&address=#form.address#&city=#form.city#&state=#form.states#&first_name=#Form.first_name#&last_name=#form.last_name#&title=#form.title#&PhoneO=#form.PhoneO#&email=#form.email#&password=#form.passWord#&website=#form.url#&license=#form.license#&cell_phone=#form.cell_phone#&carrier=#form.carrier#&slogan=#form.slogan#&logo=#form.id#" addtoken="no">
<!---</cftransaction>--->
<cfelse><!---IF NO carrier dropdown/ zips selected--->
<cflocation url="noZips-noCarrier.cfm" addtoken="no">
</cfif>
__________________________________________________
zipcodedatabase:
 
USE [enetdb1SQL]
GO
/****** Object:  Table [dbo].[ZipCodeDatabase]    Script Date: 01/16/2008 22:20:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ZipCodeDatabase](
	[ZIP_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LATITUDE] [float] NULL,
	[LONGITUDE] [float] NULL,
	[ZIP_CLASS] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CITY] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[STATE_NUMBER] [float] NULL,
	[COUNTY_NUMBER] [float] NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaZipCodeDatabase_PK] PRIMARY KEY NONCLUSTERED 
(
	[ZIP_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'ÒéÈ1´Tüº' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DecimalPlaces', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'General Number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'ZIP_CODE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'8' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'ZIP_CODE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'7' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CODE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'@	i÷XL' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DecimalPlaces', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'General Number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'LATITUDE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'8' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'LATITUDE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'7' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LATITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'cÀµõ' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DecimalPlaces', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'General Number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'LONGITUDE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'8' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'LONGITUDE' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'7' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'LONGITUDE'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'ýÑ[µâw' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'@' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMESentMode', @value=N'3' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'ZIP_CLASS' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'3' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'ZIP_CLASS' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'10' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'UnicodeCompression', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'ZIP_CLASS'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'5¢™1ñæ' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'@' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMESentMode', @value=N'3' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'CITY' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'4' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'CITY' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'10' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'UnicodeCompression', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'CITY'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'ÿ?!t‡µª‚' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DecimalPlaces', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'General Number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'STATE_NUMBER' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'5' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'8' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'STATE_NUMBER' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'7' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'STATE_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AggregateType', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'AppendOnly', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'CollatingOrder', @value=N'1033' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnHidden', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnOrder', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'ColumnWidth', @value=N'-1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DataUpdatable', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'GUID', @value=N'*ŸZƒV˜H' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DecimalPlaces', @value=N'255' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'General Number' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'COUNTY_NUMBER' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrdinalPosition', @value=N'6' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Required', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Size', @value=N'8' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceField', @value=N'COUNTY_NUMBER' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'SourceTable', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TextAlign', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Type', @value=N'7' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase', @level2type=N'COLUMN', @level2name=N'COUNTY_NUMBER'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Attributes', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DateCreated', @value=N'4/17/2007 11:46:06 AM' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'DisplayViewsOnSharePointSite', @value=N'1' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'FilterOnLoad', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'HideNewField', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'LastUpdated', @value=N'4/17/2007 11:53:36 AM' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=N'2' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=N'0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'ZipCodeDatabase_local' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'OrderByOnLoad', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'RecordCount', @value=N'42192' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'TotalsRow', @value=N'False' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
GO
EXEC sys.sp_addextendedproperty @name=N'Updatable', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ZipCodeDatabase'
 
__________________________________________________
memberzip:
 
INSERT INTO [enetdb1SQL].[dbo].[memberZip]
           ([memberID]
           ,[zipCode])
     VALUES
           (<memberID, int,>
           ,<zipCode, varchar(10),>)
________________________________________
 
I also have a table that I made called states that associates the state number with the state name for the states dropdown on city.cfm

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:

Select all Open in new window

by: dgrafxPosted on 2008-01-21 at 14:51:40ID: 20710461

Sorry I was away ... technical difficulties ...
Scratch what you're doing ...
Do this:
1) When a member is enrolling, you don't need them to enter zip codes!!!
during insert into members you get the lat & lon of their zipcode
insert into members
(zipcode,latitude,longitude, ...)
Select
#form.zipcode#,
(select latitude from aTable WITH (NOLOCK) where zipcode = #form.zipcode#),
(select longitude from aTable WITH (NOLOCK) where zipcode = #form.zipcode#),
et cetera ...

2) Then all you need do is provide a form for website visitors to enter their zipcode - the following query will find the closest 10 (or however many) members.
<cfquery datasource="#dsn#" name="getMembers">
SELECT Top 10 -- or you can do #form.numresults# by providing a field in the form so they can choose how many results
(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.memberid,b.membername,b.city,b.state
FROM aTable a WITH (NOLOCK),bTable b WITH (NOLOCK)
WHERE a.Zipcode = '#form.Zipcode#'
ORDER BY Distance
</cfquery>
<cfoutput query="getMembers">
#currentrow#) #distance# #membername# #city# #state# <br>
</cfoutput>

I had a small error in first query i posted - use this second one
aTable is your zipcode-lat-lon database - all it needs is lat & lon & zipcode
bTable is your members table and all it needs for this feature is lat & lon - it doesn't actually need zip as members zip is not calculated in the query.

good luck ...

by: ElectriciansnetPosted on 2008-01-26 at 14:25:02ID: 20751538

Even though I'm getting the error below, I'm starting to figure out what is going on a little. However, keep in mind that I do have table "memberzip" which is what stores the member's memberid in the following order:

memberid zipcode
999 11111
999 22222
etc.
Should I abandon this table with your method?

Here is the error I mentioned:


Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 4104, State 1, Line 3
Invalid column name 'latitude'.
Invalid column name 'latitude'.
Invalid column name 'longitude'.
The multi-part identifier "dbo.members.CompanyName" could not be bound.
The multi-part identifier "dbo.Members.city" could not be bound.
The multi-part identifier "dbo.Members.state" could not be bound.

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

by: ElectriciansnetPosted on 2008-01-26 at 14:30:09ID: 20751551

This is the query that is currently returning the above error

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,
      dbo.members.CompanyName,dbo.Members.city,dbo.Members.state
FROM dbo.ZipCodeDatabase a,dbo.members b
WHERE dbo.ZipCodeDatabase.Zip_code = 33556
 
ORDER BY Distance

1:
2:
3:
4:
5:
6:
7:
8:

Select all Open in new window

by: ElectriciansnetPosted on 2008-01-26 at 14:41:38ID: 20751579

I just caught your last post and I will do it your way but what about the 1st 30+ members with thousands of zip codes combined that have been inserted into the pivot table dbo.memberzip? Should I create a new table and insert to it because my dbo.members table does contain 1 zip which is with the rest of the member's address.

by: dgrafxPosted on 2008-01-26 at 16:22:43ID: 20751752

read my last post and do exactly what it says and you'll be happy ...

by: ElectriciansnetPosted on 2008-01-26 at 16:49:12ID: 20751824

I'm working on it. I had forgotten that I created a table a while back called location and it has been quetly collecting the memberID, lat, lon, city, state, zip all along. Its missing a couple of things though. zipcodedatabase works off of state numbers not states and I already have a table that I created called states that simply has the states with their respective state numbers. I'm trying to figure a way to insert the state numbers in for each member all at once. Also I have a couple of members prior to the creation of location.

by: ElectriciansnetPosted on 2008-01-27 at 07:22:28ID: 20753953

Its not working at all. (Please note: I'm using msquery for now)
Let me just show you my 2 tables:

dbo.location:
memberID, lat, lon, city, state, stateNumber, zip

dbo.members:
memberID (PK), CompanyName, StreetNumber, City, State, all other company info but no more locale

Sidebar:
I made a query that gets the lat and lon from zipcodedatabase and inputs into location.
I currently have 2 Canadian members and I had to go manually find their latitude/ longitude and input them. Their postal codes contain 3 letters but have always been present in table. Now, all the sudden, the = operator will not work in query unless I wrap the zip code I'm using behind the WHERE in single quotes. Also, my query would not work with any zip with leading zero.

by: dgrafxPosted on 2008-01-27 at 08:04:52ID: 20754065

electricians,
good sunday morning to you - just happened to check email.
i guess i'm uncertain if you're going about this correctly.
it is really simple - you may be making it more complex or looking at it incorrectly

1) your lat / lon table will NOT contain a memberid!
it will probably be left untouched and in the same state as when you purchased it

dbo.location: lat, lon, zip - that's it!!!!
dbo.members: lat, lon, and whatever else you want to store for each member but keep in mind that the ONLY columns needed for formula is lat & lon

note: where you make the statement that your code is erroring unless you put zipcode in single quotes tells me you are Not running the query I posted as the query I posted does have single quotes. point being "what else are you not doing what i posted?"

additional: if you have a canadian postalcode db - then you should combine the US zipcode table with the canadian postalcode table into a new table and use that new table for lat / lon lookups
or you could do a CF statement saying if isNumeric(form.zipcode) then use this table else use that table
but get this first part working before you mess with that

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.MemberID,b.CompanyName,b.City,b.State
FROM location a WITH (NOLOCK),members b WITH (NOLOCK)
WHERE a.Zip = '#form.Zipcode#'
ORDER BY Distance

by: dgrafxPosted on 2008-01-27 at 08:22:10ID: 20754183

I just noticed something
you are creating another table - don't!

this function needs the 2 tables only!
1) the table that has the zipcodes & lat & lon (this is what you do lookups on)
2) your members table

so instead of the location table - you should just use the zipcodedatabase.
zipcodedatabase has zip & lat & lon - right?
so that's all you need for the lookup.

like I said - you're making it too complex by looking at it incorrectly

by: ElectriciansnetPosted on 2008-01-27 at 08:35:28ID: 20754220

Yes but members does not have zipcodes- location does.My zipcodedatabase will not render the Canada members.
I now have lat and lon in members and almost got it to work. I'm willing to use zipcodedatabase instead (do what you tell me) but I have to find a way around these issues.
The following error (I believe) is being caused by the fact that I have 2 Canadian members with postal codes (i.e. m1m2m2) and the fact that location.zip is set to nvarchar but I'm scared to chage it to varchar because its the only record of the member's actual zip codes I have. Please forgive me- I have been away from my site for a couple of months and apparently I did not retain what I learned very well.
Here is the error:

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 8115, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.

SELECT    
  (3959 * ACOS(sin(location.lat/57.3) * sin(members.lat/57.3) +
      cos(location.lat/57.3) * cos(members.lat/57.3) *
      cos(members.lon/57.3 - location.lon/57.3))) AS Distance,
      members.memberid,members.companyname,members.city,members.state
FROM location WITH (NOLOCK),members  WITH (NOLOCK)
WHERE location.zip = '33556'
ORDER BY Distance

1:
2:
3:
4:
5:
6:
7:
8:

Select all Open in new window

by: ElectriciansnetPosted on 2008-01-27 at 09:01:57ID: 20754282

I added column zip to members with data type varchar and populated it. Then I replaced dbo.location with dbo.zipcode database. The query took > 30 sec. but still returned this error:

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 8115, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.

SELECT    
  (3959 * ACOS(sin(zipcodedatabase.latitude/57.3) * sin(members.lat/57.3) +
      cos(zipcodedatabase.latitude/57.3) * cos(members.lat/57.3) *
      cos(members.lon/57.3 - zipcodedatabase.longitude/57.3))) AS Distance,
      members.memberid,members.companyname,members.city,members.state
FROM zipcodedatabase WITH (NOLOCK),members  WITH (NOLOCK)
WHERE zipcodedatabase.zip_code = '33556' ***(also tried 33556)***
ORDER BY Distance

1:
2:
3:
4:
5:
6:
7:
8:

Select all Open in new window

by: ElectriciansnetPosted on 2008-01-27 at 09:03:43ID: 20754287

again, I think it is because of the Canadian zip codes containing letters.

by: dgrafxPosted on 2008-01-27 at 09:12:42ID: 20754314

check your email - i filled out your contact form

by: ElectriciansnetPosted on 2008-01-27 at 10:23:58ID: 20754525

Did you get my response?

by: dgrafxPosted on 2008-01-27 at 11:38:43ID: 20754830

just replied - let me know if you don't get it

Question

Coldfusion Zip Code Radius Query

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

coldfusion

code

zip

radius

ColdFusion Application Server

MS SQL Server

SQL Server 2005

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press