title: Geonames database: getting a full hierarchy (country -> admin1 -> admin2 -> city) with one only mysql query
body: <p>i'm trying to use <a href="http://www.geonames.org" rel="nofollow">geonames</a> database (downloaded and loaded on a local mysql) to get all associated informations from a location (lowest level is cityname) using Java and JDBC drivers.</p>
  
  <p>I have a big performance issue: each query takes something like 4-5 seconds, and i have to split the query in three parts before getting all the results i need.</p>
  
  <p>Basically, i have loaded the allCountries database, the admin1codes and the admin2codes. Since admin1 and admin2 unique ids are composed by information retrieved from the query on allcountries, i had to do something like this to query the database:</p>
  
  <pre><code>String query = "SELECT DISTINCT "
               + "loc_geoname.name AS name,"
               + "loc_geoname.asciiname AS asciiname,"
               + "loc_geoname.alternatenames AS alternames,"
               + "loc_geoname.latitude AS latitude,"
               + "loc_geoname.longitude AS longitude,"
               + "loc_geoname.country AS country_code,"
               + "loc_geoname.admin1 AS admin1, "
               + "loc_geoname.admin2 AS admin2, "
               + "loc_countryinfo.name AS country, "
               + "loc_countryinfo.currency AS value "
               + "FROM loc_geoname, loc_countryinfo "
               + "WHERE loc_countryinfo.iso_alpha2=loc_geoname.country "
               + "AND loc_geoname.asciiname IN (" + search + ") "
               + "AND loc_geoname.country='" + countrycode + "' "
               + "AND loc_geoname.fcode like 'PP%' limit 10;"
  </code></pre>
  
  <p>and this is the query i'm using to fetch all the info for a city (search and countrycode are strings prebuild, they're not important)</p>
  
  <p>now, to get the admin1 name and the admin2 name i have to launch two new queries:</p>
  
  <pre><code>String a1 = res.getString("admin1");
  String query_area = "select name from loc_admin1Codes where code='" + countrycode + "." + a1 + "';";
  
  String a2 = res.getString("admin2");
  String query_district = "select name from loc_admin2Codes where code='" + countrycode + "." + a1 + "." + a2 + "';";
  </code></pre>
  
  <p>I'm sure its possible to combine the two queries into one (but not so sure) but i don't know how to create the admin1 and admin2 codes without getting them values from the first query and combining strings into the second and the third.</p>
  
  <p>Also, i'm sure i can improve my first query to speed it up a bit (maybe using joins instead of a cross combination with a where clause...)</p>
  
  <p>Table structure is the same as defined <a href="http://download.geonames.org/export/dump/readme.txt" rel="nofollow">here</a> and i didn't change anything from that.
  Thanks all in advance for the hints!</p>