Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>For every one in the future : On geonames.org forum in the year 2008, this is "import all geonames dump into MySQL" <a href="http://forum.geonames.org/gforum/posts/list/732.page" rel="nofollow noreferrer">http://forum.geonames.org/gforum/posts/list/732.page</a></p> <p>Also google this : import dump into [postgresql OR SQL server OR MySQL] site:forum.geonames.org</p> <p>To find more answers even from the year 2006</p> <p><strong>Edited to provide a synopsis:</strong></p> <p>In the geoname official read me : <a href="http://download.geonames.org/export/dump/" rel="nofollow noreferrer">http://download.geonames.org/export/dump/</a>. We will find a good description about the dump files and contents of them.</p> <p>Dump files will be imported to the MySQL datatable directly. for example :</p> <pre><code>SET character_set_database=utf8; LOAD DATA INFILE '/home/data/countryInfo.txt' INTO TABLE _geo_countries IGNORE 51 LINES(ISO2,ISO3,ISO_Numeric,FIPSCode,AsciiName,Capital,Area_SqKm,Population,ContinentCode,TLD,CurrencyCode,CurrencyName,PhoneCodes,PostalCodeFormats,PostalCodeRegex,Languages,GeonameID,Neighbours,EquivalentFIPSCodes); SET character_set_database=default; </code></pre> <p>be careful about the characterset because if we use the CSV LOAD DATA ready importer of an old phpmyadmin of 2012 we may lose the utf characters even if the collation of columns was set to utf8_general_ci</p> <p>Currently there are 4 essential datatables : continents, countries(countryInfo.txt), divisions(admin1), cities or locations(geonames)</p> <p>admin1, 2, 3, 4 dump files are the different levels of internal divisions of countries such as admin 1 which is the states of US or provinces of other countries. admin 2 is more detailed and is the internal divisions of the state or the province. and so on for the 3 and 4</p> <p>The countries dump files have been listed there contain not only cities but all the locatoins in that country even including a store center. Also there is a huge file as "allCountries.txt" will be more than 1GB after extracting from zip file. If we want only the cities we should choose one of the dump files : cities1000.txt , cities5000.txt , cities15000.txt which the numbers represent the min population of the listed cities. We store cities in the geonames datatable(you may call it geo locations or geo cities).</p> <p>Before importing *.txt dump files take a few research about the LOAD DATA syntax in the MySQL documentation.</p> <p>The read me text file(also in the footer of dump page) provides enough description for example :</p> <pre><code>The main 'geoname' table has the following fields : --------------------------------------------------- geonameid : integer id of record in geonames database name : name of geographical point (utf8) varchar(200) asciiname : name of geographical point in plain ascii characters, varchar(200) alternatenames : alternatenames, comma separated varchar(5000) latitude : latitude in decimal degrees (wgs84) longitude : longitude in decimal degrees (wgs84) feature class : see http://www.geonames.org/export/codes.html, char(1) feature code : see http://www.geonames.org/export/codes.html, varchar(10) country code : ISO-3166 2-letter country code, 2 characters cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters admin1 code : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20) admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) admin3 code : code for third level administrative division, varchar(20) admin4 code : code for fourth level administrative division, varchar(20) population : bigint (8 byte int) elevation : in meters, integer dem : digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat. timezone : the timezone id (see file timeZone.txt) varchar(40) modification date : date of last modification in yyyy-MM-dd format </code></pre> <p>Also about the varchar(5000) we should know about the 64kb size of each row in MySQL 5.0 or later: <a href="https://stackoverflow.com/questions/1303476/is-a-varchar20000-valid-in-mysql">Is a VARCHAR(20000) valid in MySQL?</a></p>
    singulars
    1. This table or related slice is empty.
    1. POImporting data from geonames.org database into MySQL DB
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USAlix
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload