Adding a new country (and geo tables description background)

Adding a Country and its Provinces to the WS database

The WS database does not have a complete list of countries or provinces. This is due to the history of the location databases as well as the preference not to have a pull-down list of 200+ countries, when only less than a hundred are actually likely to be used.

The two tables that determine the countries and provinces displayed are user_location_countries and user_location_provinces.

To add a new country, you can add a new entry to user_location_countries with the 2-letter ISO code and the center of the country. You can get this information from the geonames_countryinfo database. Here's an example for Armenia:

select iso_alpha2,c.name,latitude,longitude from geonames_countryinfo c,geonames g where c.name="armenia" and c.geonameid=g.geonameid

If you're in a hurry and you verify what you're doing, you can actually just do this:

INSERT INTO user_location_countries SELECT iso_alpha2,c.name,latitude,longitude FROM geonames_countryinfo c,geonames g WHERE c.geonameid=g.geonameid AND c.name="YOURCOUNTRY"

You can do a similar thing to add the provinces. Example again for Armenia:

INSERT INTO user_location_provinces SELECT * FROM geonames_adm1 WHERE country_code="ZZ"

After doing both of these you will need to delete the cache to cause the new info to show up:

Use Admin Menu's Clear Cache- Cache tables.

What the various geographic tables are:

• user_location is the table where USER geographic info is stored. It must have country and province abbreviations that come from the user_location_countries and user_location_provinces tables. For historical reasons, some of these are not the same as the proper ISO codes.
• user_location_countries and user_location_provinces are where the drop-downs and such come from on the website.
• geonames is a dump of the Geonames.org database, containing a huge number of specific placenames. It is used for searching for placenames in the database when trying to geocode a place. It is also used on the map when somebody types in a placename for searching.
• geonames_adm1 is the selected list of ADM1 or first-level administrative divisions in Geonames. As you see above, it is a fairly authoritative place to get geographic info. I don't believe it is used directly by the WS site.
• geonames_countryinfo has the full information about each country. It is not used directly, I don't believe.
• ip_locator_country is apparently an old db used by the ip2cc module, which looks up incoming IP addresses and geocodes them
• iso3166 is apparently the current country database used by ip2cc
• civicrm_country and civicrm_state_province are hopefully not used by anything any more.