0

First time posting on here because Google is yielding no results!

So, I have a website that is based around travelling and locations. Everytime someone enters content into the site, they select a location and that content then has lat and long, country, etc.

The issue I face is that I have a DB of all the "cities and areas" of the world and there are a good 3.5 million records in the database I believe.

My question to you is how would you guys recommend doing a 1 field autocomplete form for all the cities? I don't need advice on the autocomplete form itself, I need advice on HOW and WHERE I should be storing the data... text files? SQL? Up until now, I have been using SQL but I don't know how it should be done. Would an AJAX autoloader be able to handle it if I only returned 100 records or so? Should all the results be preloaded?

Thanks for your help guys!

EDIT: I have actually found another way to do it. I found this awesome little plugin to integrate Google Maps with it

http://xilinus.com/jquery-addresspicker/demos/index.html

Fantastic.

Community
  • 1
  • 1
Benny McLennan
  • 1
  • 1
  • just call ajax in your autocomplete function. send the select country as one of the parameters – Deepanshu Goyal Dec 05 '13 at 04:13
  • What do you mean send the select country as a parameter? I want 1 input field where the user starts typing the city or area and the closest results are returned. :) – Benny McLennan Dec 05 '13 at 04:16
  • in ajax, you are supposed to send data to be processed, send two things using ajax, country and the city area name entered by the user, you can send multiple values using ajax – Deepanshu Goyal Dec 05 '13 at 04:18
  • Thanks, I'm not new to AJAX or development at all. How can I send the country... if I don't have the country? You're not following my requirements. The only data I am able to send via AJAX is the letters the user inputs into the single input field. There is no "country" variable to be sent. I can find the country AFTER the city is called. See my dilemma? The input field is not restricted to cities from a single country. Whatever the user is inputting, should be available from every city record in the DB (some 3 million or so) – Benny McLennan Dec 05 '13 at 04:21
  • then dont send country, send only the city area name, and now you just need to create an optimised query which wont take too long to query through 3 million records, just go for ajax, and call it in your autocomplete function – Deepanshu Goyal Dec 05 '13 at 04:23
  • well sorry I misread it, you should keep the data in SQL only, just jave nice query to fetch it fastly, and definitely have a limit cclause at the end – Deepanshu Goyal Dec 05 '13 at 04:33

1 Answers1

0

Benny

I have a few thoughts here:

  1. since you don't know whether a user will enter the english or local (native) name, each city record in your database should have both. Make sure to index these fields.

  2. Do not do auto-complete until you have a minimum number of characters. Otherwise, you will match way too many rows in your table. For example, assuming an even distribution of english characters (26), then at 3.5 million records you would statistically get thar = he following matches per character:

1 char = 135k
2 char = 5.2k
3 char = 200
4 char = 8

  1. If you are using MySQL you will want to use the LIKE specifier.

There are much more advance methods for predictive matching, but this should be a good start.

Andrew - OpenGeoCode
  • 2,299
  • 18
  • 15