Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: New to databases

    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing more about how your code and data is structured, I can only offer broad advice.

    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    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.
    Thanks for reading,
    John

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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