1. Registered User
Join Date
Jan 2014
Posts
2

Hello,
I am disabled and have way to much time on my hands, so I decided to learn databases. With that said what I am trying to do is, I have a table that contains zip codes, city, state and so on. What I want to do is calculate the distance from zip code "A" to zip code "B". I have that done. Now rather than having the table contain distance from "A" to "B" and "B" to "A". Now lets assume we have buyers and sellers. The buyers will be about to see an ad displayed within X distance and a seller will be able to place an ad within X distance. Now I know this can be done with the distance formula but in some cases the distance formula and the driving miles have very big errors. My table contains driving miles. What I need help with in the code to extract the distance and place the info on a web page.
Thank you,
John

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

If you're looking for zipcode to zipcode distance, then a simple way to manage the data size is to add a constraint to force one zipcode to be less than the other. That will reduce the size of the table by up to 50% and will help also help you manage complexity.

If you are looking to manage the computational complexity, that becomes a longer and much more detailed discussion.

-PatP

3. Registered User
Join Date
Jan 2014
Posts
2
Thank you for your reply. To give you a better idea, the table I have is zip "A" and every zip code within 75 driving miles. The first field contains let say A zip from let say Atlanta, Ga. Field 2 contains the cities that are less than 75 miles from Atlanta. So record 1 would be an Atlanta zip in field 1, filed 2 A city zip less than 75 miles, filed 3 contains the driving miles. This repeats with the Atlanta zip in field 1, and so on until all city zips within 75 miles are complete. I know many website calculate distance using longitude and latitude and the distance formula but in many cases the distances have very large errors when you start drive. I very much understand the table depending on the number of cities you use will be very large. If you used all the zip codes within a 75 mile radius you may end up with 200 to 300 records per zip code depending on what part of the country you are working on. With that said it bring up another good question that I don't understand. This has to do with database performance. I am running windows 7 and playing around with Microsoft SQL express. With the limits the express edition has this may not be a factor for me. If you install SQL on the same computer using at same processor, memory, hard drives and so on but you use a different OS. Does the OS you select have any thing to do with how SQL will perform. For many of you, this may be a stupid question. I have never used any version of a server software. Again I am just learning. I have found something I can do when I feel like it and I very much enjoy it. I love how it requires you to really think. I really dont want someone to do it for me. I am just looking for a little help and I also dont mind one bit to read. Advice on good books or web site to use would be very much appreciated.
John

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,837
Google Maps and Bing Maps both APIs (application programmer interfaces) that I am pretty sure will do this for you. However if you do this too much or for a for profit venture there might be a licensing issue. SQL Server depending on your version, also has Spatial and Geography data types that have functions that can handle this kind of thing, but I have never done it myself.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
MySQL has spatial extensions as do lots of other server DB's. don't think Ive seen it on file server db's though

Getting the point to point distance is relatively trivial
get the by road distance is a bit trickier as it requires more detailed information on roads.

As Thrasymachus if you start to hit the free services they may restrict or cut off access. they may well beworth approaching and finding out what the cost and or restrictions woudl be if yours is a paid for service.

If ytou are in the US then you may be able to make use of the TIGER datasets from the US (Geological) service.

however finding the (is it quickest, shortest, most fuel effieicent, scenic / whatever) route is complex. whats the right route for, say a salesperson who wants to get from A to E, via B,C & D may not be the right route for a rider, whats right for powered vehiocle may not be for cyclists.... y'get the picture

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•