Question

Coldfusion Zip Code Radius Query

Asked by: Electriciansnet

I now have about 30 members on electriciansnet.com and presently, when they enroll, they enter their city as well as other surrounding cities in their service area to get sometimes over 100 zip codes inserted into my db with their member ID. This is proving to be inaccurate. I would like to switch this to a radious query either by changing the query to my zip code database of over 40,000 zip codes or maybe using a we service.

The 1st snippet is the current cffunction that is executed when the end user attempts to find a member by inputting their zip code on the home page.

The 2nd is the csfunction populates a cfgrid when the enrolling member inputs their city.

To see what is going on, you are welcome to visit http://www.electriciansnet.com and http://www.electriciansnet.com/city.cfm to enro;; with a dummy company.

<cffunction name="contactMember"
          access="remote"
        returntype="query"
        hint="This will return the results of the myZip search to contact.cfm"
        output="yes">
       
        <cfargument name="PostalCode" type="numeric" required="yes">
       
        <!--- DEBUGGING INFO --->
            <cfif session.debug>
              <cfdump label="arugments for ent1.contactMember" var="#arguments#">
        </cfif>
       
        <!--- SEE IF THERE ARE ANY RECORDS CURRENTLY IN THE DATABASE --->
        <cfquery name="session.contactMember" datasource="#enet#">
           
                  
                  SELECT             TOP 3 members.memberID, members.companyName, members.streetNumber, members.City, members.State, members.Contact_First, members.Contact_Last, members.Contact_title, members.Contact_Phone, members.Contact_Email, members.Contact_URL, members.License, members.CellNumber, members.Slogan, members.member_Since, members.isDeleted, networkID.networkID, networkID.networkMethod                             
            FROM             members INNER JOIN memberZip ON members.memberID = memberZip.memberID
                        INNER JOIN networkID ON members.networkID = networkID.networkID
            WHERE    members.isDeleted = 0  AND  memberZip.zipCode= #arguments.PostalCode#
			
			
			 ORDER BY dbo.members.member_since
        </cfquery>
        <cfif session.contactMember.recordCount EQ 0>
            <cfquery name="insertZip" datasource="#enet#">
                INSERT INTO member_miss(zipCode, miss_date)
                VALUES(#arguments.PostalCode#, #now()#)
            </cfquery>
          </cfif>
       
        <!--- RETURN THE RESULTS OF THE CONTACT MEMEBER QUERY --->
        <cfreturn session.contactMember/>
       
    </cffunction>
_________________________________________________________________
 
    <cffunction name="chkZips" access="remote" returntype="query" hint="Prints the">
	 
    <cfargument name="argZips" type="string" required="false">
	<!--- DEBUGGING INFO --->
            <cfif session.debug>
              <cfdump label="arugments for ent1.chkZips" var="#arguments#">
        </cfif>
    <cfloop index="iZip" list="dbo.ZipCodeDatabase">
    <CFQUERY NAME="getZips" DATASOURCE="enetdb1sql">
    SELECT 1 as [checked], CITY, COUNTY_NUMBER, STATE_NUMBER, ZIP_CODE, LATITUDE, LONGITUDE
    FROM dbo.ZipCodeDatabase
    WHERE dbo.ZipCodeDatabase.CITY = '#form.myCity#' AND dbo.ZipCodeDatabase.STATE_NUMBER =    #states_dropdown#
 </CFQUERY>
    <cfreturn getZips>
    </cfloop>
    </cffunction>

                                  
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:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2008-01-13 at 17:53:10ID23079814
Tags

coldfusion

,

code

,

zip

,

radius

Topics

ColdFusion Application Server

,

MS SQL Server

,

SQL Server 2005

Participating Experts
2
Points
500
Comments
20

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

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. How to create a trigger to update students currently enrolled?
    My trigger creates, but it won't update the course_section table (provides current enrollment status). The enrollment table displays the student's id, course id (csecid) and the grade. CREATE OR REPLACE TRIGGER lab5_trig AFTER INSERT OR DELETE on enrollment FOR EACH ROW ...
  2. Cities within a distance of a ZIP Code
    The question is simple but I can't find the solution which should be answered in C++ or Visual Basic: After I get a. A ZIP Code and b. A distance (miles) How do I find all the cities that are lied within the provided distance from that ZIP Code? * I already had - The ...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

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 allOpen 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 allOpen 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 allOpen 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 allOpen 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 allOpen 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 allOpen 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

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...