GeoNames Forum

Hi guys,

I've tested http://www.geonames.org/export/ajax-postalcode-autocomplete.html quite a bit today and I find it incredibly slow

Why is that?

My problem is that I need to create an autocomplete text field like the one found on www.booking.com but I can't find something nearly efficient.

Could someone direct me to the proper direction?

Thanks a lot!

too many users have been using the free services these days, which slowed the service down. Sorry.

Why don't you host the database on your server and use that?

I needed to do something similar, but for place names (Globally). I took the main geonames table and removed everything but P and A featurecode records. I.E. I removed mountains, rivers etc... This still left almost 3 million records to search. The user types into a textbox and after each keypress the database is queried for all record matching what the user has entered so far and displays the results. Initially the search was taking 32 seconds. Way too long. I had a quick read up on indexes and such and managed to get the query time down to just 0.078 seconds. This works pretty well.
Currently the database is on a test server (DebianLinux/apache/mysql/php) in a Virtual machine. Once it's moved to the production server it should be faster still.

regards...

Of course, I'm willing to host the whole thing on my own server but on http://www.geonames.org/export/ajax-postalcode-autocomplete.html it says the following :

The javascript file http://ws.geonames.org/export/geonamesData.js contains an array with all countries supported for this service and the country of the user's ip address. This file has to be downloaded from the geonames server, don't host it on your own server as it contains user specific data (user country).

Actually, I don't understand what they mean by "user" in that paragraph.

So, how am I supposed to do if I don't want to depend on geonames' servers?

Also, I only need to use countries, regions and cities. What files should I download then? I can't find files with region names on http://download.geonames.org/export/dump/

Thanks again for the help!

Hello?

I had a quick look around and it seems the postal codes dump you would need, is not readily available. I wasn't aware of this as I didn't need that data myself.

I found this:

Drop an email to info@geonames.org if you need the postal code dump file. (And if you are not a spammer )

That might be a good place to start. You won't get far with no postal code data to work with.

How you proceed after that depends on what you're familiar with.

For me, Javascript would monitor the textbox input and talk to the server via ajax. php on the server would query the postcode database, returning the results back to the browser for dynamic update.

Regards.

Thanks terry

No, I'm not a spammer!

As for the method, after a long thought, I'd prefer to use only select menus in order to totally avoid user inputs.

So, there would be one select menu for the country, one for the postal codes and one for the city. Each of the last two ones would be updated in accordande to the previous selection.

Do you think it is a good plan?

Thanks again for the help

You can implement it any way you feel comfortable doing it.
So you would select options from list menus:

Country > City > postcode ??

What, if like me you don't live in a city?

The original direction of this topic was "auto complete".

Personally, I would still go with auto complete, as it avoids situations where a user would not come under the umbrella of a particular city.
You would be using ajax anyway to populate your 2nd and 3rd listboxes, so you'd have most of the code there already. If you can get the queries fast enough, it could mean less code to write and it's more flexible for your users. It depends on what fields the postcode table has, but I would try just a "Select Country" listbox, then a textbox and run auto complete queries on the server based on that, limiting the search to just that country code to help speed up the query. Indexes on the postcode field and country code field, (assuming there is one), should help a lot.

You could even have your original 3 listboxes, and if they cannot find their postcode from that, offer an auto complete box.

Maybe just suck it and see what works best. Either option only requires simple minimal code at the client and server ends.

One other thing....

I'm definitely not an expert in ajax, php, mysql, or java script.
I've learn't how to do what I've needed to do, when I've needed to do it.
Websites etc, are pretty much just a hobby for me.
I'd take anything I say with a pinch of salt. I'm just trying to help out with some suggestions based on my own experience with similar stuff.
It would be well worth getting a second opinion if I were you.

Just trying to be helpful.

Thanks

Yeah, I wanted to do autocomplete but I thought that, if I let users enter city names in the database, I could end up with mistaken and non-standardized data that I would have to check. That wouldn't be good for the search function that I also have to implement.

But autocomplete could remain a good option if only the proposed choices could be entered in the database. I could make the city field optional for those whose city isn't listed, the postal code being enough.

What do you think?
Also, how many MB would the total dump file be? (countries, codes and cities)

PS : no problem if you're not an expert ... as long as it comes from practical experience

Users should not be able to enter what they actually typed, into the database, but instead get a list of actual names displayed, based on what they typed. They then pick one of these preselected standard names.
That way your data stays standardized.

I'm not too sure what your goal is here. I thought you wanted every user to pick a postcode as this is more precise than just a city, but it seems you would be happy just to have their city listed with no postcode?

That's not all that relevant though. I was just trying to get a feel for what you actually needed.

The dump I have here with the rivers, mountains etc removed is 490Mb uncompressed. I still have tables in there that I don't use, and only removed some stuff to speed up queries a bit on tables that I actually use. I'm not short on disk space so the unused tables are no bother. My dump includes these tables:

Admin1codes
Admin1codes ASCII
Admin2codes
Alternate Names
Continent Codes
Country info
feature codes
geonames
iso language codes
time zones

I'm guessing you'll at least need to download:

allcountries.zip
countryinfo.txt
and
postal codes (If you can get it)

and possibly create the continent codes if you want to minimize the list of countries displayed in the listbox. There's 247 countries to pick from if you display them all in one list.

If you just get the data you need and delete any rows that will be redundant for your application, you can make the database much smaller than it would be by default.

OK, I've downloaded the allCountries.txt, the one that makes 700mb uncompressed and it looks like SQLyog (community edition) can't take such a big file.

How do you guys manage to import it in your database?

Thanks again

navicat and phpmyadmin work fine for me.

I just got sqlyog community and that works just fine to import the 700Mb+ allCountries.txt file.
Took about 12 mins to import, but was straight forward.

Hi Terry,

I've tried again with SQLyog but it doesn't work. The dialogue box "execute query from a file" seems to freeze and nothing happen in the progress bar even after 30 minutes.

Then, I've installed phpmyadmin and it says that it can't process dump files bigger than 84mb. Then I ran bigdump.php and it said it can't process files bigger than 200mb!

So, what's the secret??

Thanks again!

This is how I did it from the query command

CREATE DATABASE geonames DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use geonames;

CREATE TABLE geonames (
geoname_id mediumint PRIMARY KEY,
name varchar(200),
ascii_name varchar(200),
alternate_names varchar(4000),
latitude decimal(10,7),
longitude decimal(10,7),
feature_class char(1),
feature_code varchar(10),
country_code varchar(2),
cc2 varchar(60),
admin1_code varchar(20),
admin2_code varchar(80),
admin3_code varchar(20),
admin4_code varchar(20),
population mediumint,
elevation int,
gtopo30 smallint,
time_zone varchar(40),
modification_date date
) CHARACTER SET utf8;

SET CHARACTER SET utf8;

SET NAMES utf8;

LOAD DATA LOCAL INFILE 'Drive:/path/to/allCountries.txt' INTO TABLE geonames (geoname_id, name, ascii_name, alternate_names, latitude, longitude, feature_class, feature_code, country_code, cc2, admin1_code, admin2_code, admin3_code, admin4_code, population, elevation, gtopo30, time_zone, modification_date);

Hope that helps

wow ... I don't know how I could thank you properly.
Your solution worked nicely and it took only 2 minutes to import the whole table!

I really appreciate your help.

BTW, do you know why there are records that lack the country code?
For example, some korean place lack it I've noticed.

Thanks again ... really!

I deleted all codes from geonames except for P and A, so I only have 16 entries left with no country_code.
These show up as places like NATO and the European Union. These are administrative bodies that do not really apply to a country.
That's my guess anyway.

Hi Terry

I've had a deeper look at the data and I have concerns about the quality of the records. For example, all the french postal codes are stored in the column "alternate_names" ... which is not very handy.

Do you know what are the commercial databases so that I could compare?

Thanks again for your precious help.

I can't really see any problem with the post code being under alternate names. Here in the uk the prefix of a postcode is often used as an area name. I can't say I've noticed any under alternate names, but then I'm not using the alternate names field, so that's not surprising.

I thought you were going to get the separate postal codes data and use that?

I have never used a commercial dataset for global location names etc...
It seems to me that such data should be in the public domain. I would guess that most data of this nature has to be provided by a government on some level, which means it's citizens have paid for it anyway.

I am a hobby user and have no interest in a commercial dataset.
To be honest, the commercial products probably get data from some of the same sources as geonames, not that I know, I'm just guessing.