OpenGeoDB to list towns, districts, municipalities (in a state) with hierarchy (adm. region, rural district), zipcode and population count .

opengeodb orte in bayern

SELECT 
   /** Ort / Ortsteil */
   ort_name.loc_id 'loc_id',
   ort_name.text_val 'loc_name',
   ort_level.text_val 'level',
   ort_type.text_val 'typ',
   /* ort_parent.text_val 'parent_id', */
   ort_plz.text_val 'postleitzahl',
/* LEVEL6 */
	hier.id_lvl6 'gemeinde_id',
   lvl6_name.text_val 'gemeinde',
   lvl6_type.text_val 'gemeinde_type',
/* LEVEL5 */
	hier.id_lvl5 'kreis_id',
   lvl5_name.text_val 'kreis',
/* LEVEL4 */
	hier.id_lvl4 'bezirk_id', 
   lvl4_name.text_val 'bezirk',
/* COORDS */
   coords.lat 'lat',
   coords.lon 'lon',
/* EINWOHNER */
	einwohner.int_val 'einwohner'
FROM
    geodb_textdata AS ort_level
/* Ort / Ortsteil */
LEFT JOIN geodb_textdata AS ort_name        ON ( ort_level.loc_id = ort_name.loc_id )
LEFT JOIN geodb_textdata AS ort_plz         ON ( ort_level.loc_id = ort_plz.loc_id )
LEFT JOIN geodb_textdata AS ort_type        ON ( ort_level.loc_id = ort_type.loc_id AND ort_type.text_type    = 400300000)
/** HIERARCHY **/
LEFT JOIN geodb_hierarchies AS hier         ON ( ort_level.loc_id = hier.loc_id )
/* Level6 */
LEFT JOIN geodb_textdata AS lvl6_name       ON ( hier.id_lvl6 = lvl6_name.loc_id AND lvl6_name.text_type = 500100000)
LEFT JOIN geodb_textdata AS lvl6_type       ON ( hier.id_lvl6 = lvl6_type.loc_id )
/* Level5 */
LEFT JOIN geodb_textdata AS lvl5_name       ON ( hier.id_lvl5 = lvl5_name.loc_id AND lvl5_name.text_type = 500100000 )
/* Level5 */
LEFT JOIN geodb_textdata AS lvl4_name       ON ( hier.id_lvl4 = lvl4_name.loc_id AND lvl4_name.text_type = 500100000 )
/* coords */
LEFT JOIN geodb_coordinates AS coords       ON ( ort_level.loc_id = coords.loc_id )
/* einwohner */
LEFT JOIN geodb_intdata AS einwohner       ON ( ort_level.loc_id = einwohner.loc_id AND einwohner.int_type = 600700000 )
WHERE 1=1
/** IN BAYERN */

AND hier.id_lvl3              = 111       /* BAYERN=111 / NRW=117 */
/** ORT / ORTSTEIL */
   AND ort_level.text_type    = 400200000 /* LEVEL */
   AND ort_name.text_type     = 500100000 /* NAME */
   AND ort_plz.text_type      = 500300000 /* POSTLEITZAHL */
   AND ort_level.text_val    >= 6
/** LEVEL6 */
   AND lvl6_type.text_type    = 400300000 /* TYPE */
/** LEVEL5 */
   AND lvl5_name.valid_since  IS NULL /** Landkreis: avoid duplicates! */
/** LEVEL4 */
   AND lvl4_name.text_type    = 500100000 /* NAME */
/** EINWOHNER */
# 	AND ( einwohner.int_type = 600700000 OR einwohner.int_type = NULL )