Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Smart Address search algorithm

    Hello,

    I'm trying to find way to let the user search for addresses in a database similar googles maps does. That means, if the search string is something like 'Denver' the user will get just one result, because there's only one city in the db with extactly the same name. If it is something like 'Denver av' there might be more results, including the streets, not the city. If it is 'Denver Av LA' its just one again, beacuse theres only one Denver Av in LA. Could also be 'LA Denver Av'. That means a set of combinations leads to the same result. In Google you can also search for 'Denver LA av', but that's not necessary. Anyone beeing occupied with this before? I'm thankful for every hint. Supported columns should be street, zip, city, streetno. Table has about 1000 rows. My first approach was to concat every column and use 'like'. But that did not really work. Hopy you have some ideas.

    Thanks, silas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the solution you are looking for will depend on exactly how you analyze the incoming user search string and translate it into an appropriate query

    therefore, this is not a mysql question but rather an application language question

    what are you using? php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Yes, php with mysql. I'm currently trying to solve this with a view or stored procedure. But still don't have a clue how to start. Maybe a regexp in a concated string?
    Edit: And perhaps a extra column for the quality of the match.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to php forum

    seriously, you have to do the bulk of the logic in php

    otherwise the SQL will not know when to search for denver as a city or denver as a street

    you did, after all, stipulate that you wanted a smart address search algorithm

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    I want a sql solution. Pull me back.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, it's your thread, i'll move it back to mysql forum

    good luck getting a "smart" solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Posts
    97
    Thanks for your sarcasm. I'm pretty confident it's up in someones sleeve out there :-)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by silas
    Thanks for your sarcasm.
    oh, you're quite welcome, i think you earned it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    Well, this is really starts annoying me. I posted a simple question and you blow up my thread with unreasonable comments.Too bad about this forum, I once really liked it.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think they were unreasonable, but i do apologize if you were offended

    we have lots of competent mysql people here, don't give up yet...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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