Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Smile Unanswered: Search or nearest location

    Hi

    I have a database where we have services for 10 communites for instance. Each community has several different services. Now i can search by communitry and keyword; for instance salt lake city and diabetes. If there are any services in salt lake city that has diabetes in the keywords, it will be displayed. What i wanted to do is if the is no diabetes services in salt lake city it would tell you the nearest placce there is diabetes, like if the nearest location is cedar city. This is only an example. Where this data base is located there a 10 small comunities with small populations and pretty much a main center 150 miles away.

    Should i have a main center and have the distances for each place put in and when it searches the database it go to the nearest?

    Any input apprecitaed.

    Newfiebullet

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The problem with recording only distances from one given location is you're stuck using said location for every search. There is no way to begin at a different location and return accurate results.

    You have to use coordinates. Since you only have 10 communities, you could go with a simplified grid instead of pulling actual lat/long coords. Once you have that setup, it's a matter of comparing your two source coordinates to all the other coords, and find the smallest difference eg: (source latitude - target latitude) + (source longitude - target longitude) = your total difference between any two locations.
    Last edited by Teddy; 01-13-05 at 10:30. Reason: HURRR I TYPE GUD
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Posts
    51

    Smile Nearest Search

    Hi Thanks for the reply,

    So what you are sayings is, every community would have lat and log cordinates. would you make these up based on somehthing? how would you put this in a table? I sort of understand what you are talking about but not sure how to start this. The communities are lined pretty much in one strait line. With the total distance from first to last 250 kms. Im in canada. I am in a community about 4 more past me but this is the main centre. The last one is another main city.

    Guidance appreciated.

    NewfieBullet

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If their on a straight line then yeah, you can record just distance. I assumed you needed a 2-dimensional distance map. Be aware that if you need to entire another community at any time that is say... 10 miles south of the original location... you'll have no way to do that.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Posts
    51

    Smile

    Thanks for the insight,

    We have a community cluster, here but in the entire region there are going to be 4 clusters of communityes and the dbase will cover the entire region(over time right now this cluster done and is trying to search by closest community). This is the delima. I hope this has given you further insight. I think the 2 dimensional map is the best option. Is there anything i can read or a book i can buy that would further expain this. I have no clue when it relates to maps or how to start such a thing.

    Have you did such a thing before?

    Newfiebullet

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's pretty simple... Do you understand how coordinates work?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Posts
    51

    Smile

    I sort of understand how coorinates work, a friend of mine has gps and is has all the area maps on computer. I also have a gps but not too sure how it really works. If i had all the coordinates for the towns would it be a help?

    Thanks again for your input.

    Newfiebullet

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes you can derive a distance based on the difference between co-ordinates. I have no idea of the branch of maths but there is a relatively simple formula for this purpose.

    in fact a quick google for "calculate distance between points co-ordinates"
    came up with
    http://mathforum.org/library/drmath/view/51722.html

    The problem of using a co-ordinate based system (derived from GPS or maps) is that whilst you can get a point to point distance but that doesn't neccesarily reflect the effedctive distance, unless you are flying or able to move in a direct line between the two points. Eg if you had 2 settlements either side of an impassable ravine, that were 1 Km apart, but the only crossing was 5 Km downstream do you calculate the disance as 1 Km the point to point distance or 11 Km the effective distance?

  9. #9
    Join Date
    Mar 2004
    Posts
    51

    Smile

    Yes I undestand what u are saying, so how do u propose to calculated the miles or lat and long. So really the gps is no good for this situation?

    Newfiebulllet

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    GPS coords seem to be radical overkill for the problem you first posted!!

    11 locations should fit happily in an imaginary grid 30x30 (or if some communities are unusually distant then a 100x100 grid should handle it.

    can you live with the error that comes from assuming arrow-straight roads join all communities??

    place your communities

    tblComms:
    IDcomm, auto, PK
    strComm, text
    Xcomm, integer
    Ycomm, integer
    doesDiabetes, y/n
    doesRabies, y/n
    doesDengeFever, y/n

    so you have a denge fever outbreak in community IDcomm = 3, grab X, Y into vars Xsite, Ysite

    a query produces ID, X, Y for all locations that can handle denge fever.

    a second query looks at the first (maybe it all works in one query, but who cares with only 10 records!!!!) and uses pythagoras to produce the following calculated field:

    ((Xcomm - Xsite)^2 + (Ycomm - Ysite)^2)^0.5

    sort by that field and you have APPROXIMATELY what you are looking for.

    if you can't live with the network of arrow-straight roads assumption (errors!) then you are talking about developing an interesting algo ...but it will take some maths and some time to get there ...starts me thinking about tree structures for some reason.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a look at these links for some VB that might be helpful.
    http://www.amanitamuscaria.org/content/view/4/37/
    http://www.programmersheaven.com/zone1/cat251/22588.htm

    it is also worth unleashing google on "dijkstra's algorithm" to get some background

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i posted a simple (simplistic ???) cost-based approach using DAO & SQL in Teddy's codebank thread in my last attempt to keep it alive.

    thanks: that was fun for a sunday afternoon!

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Mar 2004
    Posts
    51

    Smile

    Thanks for all the help.

    Sorry no repy but i was out for a couple of days. I will have a look at the information today and see if i understand it.

    Newfiebullet

  14. #14
    Join Date
    Mar 2004
    Posts
    51

    Smile

    Hey Man

    I was looking at your teddys db and not really good a visual basic, can do a little but not great. In your db you have 3 types of disease for instance but what if there were 150 types of disease or what ever. Is there a way to have it so you type it or select it from a list? > I think this is what i am looking for i am pretty sure of that. Now i have to try to figure out how to incorporate it into my db.

    Newfiebullet

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you would need to change the table design to handle 150 diseases.

    instead of keeping doesDiabetes etc in tblTown

    ...make a separate table tblDiseases to define the diseases... (idDisease and strDisease is enough) this can happily feed your list.

    ...and then another table tblTownDisease to define which location handles which disease (idDisease, idTown)

    fill tblTargets with idTown by looking for tblTownDisease WHERE idDisease = whatever the user selected in the list

    150 diseases (15,000 diseases) should not affect the "search" mechanism since only one selected disease is involved in the search.

    things will probably start to go wrong tho if you have many towns and many interconnecting roads and only a few widely separated treatment centers. i worry about the whole thing falling apart as ever more possible routes are constructed before the first route-to-target is found (after that is ok because the code starts dropping routes that are longer than first-found). dijkstra is probably the way to go for a complex "map", but i didn't find a sufficiently clear description to be able to code it up.

    izy
    currently using SS 2008R2

Posting Permissions

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