这里是将数据转换为区分层级的sql和json格式的脚本

[l-7-l]

• 该脚本仅考虑postgres(表结构在底部) 会输出下面的文件
  • /gen
    • provinces.sql // 负责插入数据
    • provinces_drop.sql // *_drop.sql文件负责在相应的表中根据id清空数据
    • provinces.json
    • cities_drop.sql
    • cities.sql
    • cities.json
    • disticts.sql
    • districts.json
    • districts_drop.sql
    • subdistricts.sql
    • subdistricts_drop.sql
    • subdistricts.json
• 已考虑省级直辖市(如北京市)和不设区县的地级直辖市(如嘉峪关市，东莞市 中山市)
• 如需使用行政管理数据可采用list2
• 由于/code/类目下对应嘉峪关市的街道数据json文件名为620201（嘉峪关市）非620200(嘉峪关市) 所以须在list.json(或list2.json)文件2915行后添加"620201": "嘉峪关市",
• 156为iso国家代码
• 该脚本不考虑性能很臭 有更好方案或其他优化版本的兄台不要惜言贴出来
• 顺祝各位贡献者与看官小伙伴事业顺利 天天开心

import json from './list.json'
import fs from 'fs'
import path from 'path'

let provinces = {}
let cities = {}
let districts = {}
let subdistricts = {}
let gen = p => path.join(__dirname, `./gen/${p}`)

Object.keys(json).forEach(code => {
  let name = json[code]
  let sub_districts_path = `./code/${code}.json`

  let base = {
    country: 156,
    name,
    code: Number.parseInt(code),
  }

  let provinceCode = code.slice(0, 2)
  let cityCode = code.slice(2, 4)

  let provinceSixCode = Number.parseInt(`${provinceCode}0000`)
  let citySixCode = Number.parseInt(`${provinceCode}${cityCode}00`)

  let is_sub_districts_exist = fs.existsSync(sub_districts_path)

  if (is_sub_districts_exist || !code.endsWith('00')) {
    if (!cities[citySixCode]) {
      cities[citySixCode] = {
        ...base,
        province: provinceSixCode,
        code: citySixCode,
        name: citySixCode.toString().endsWith('100')
          ? provinces[provinceSixCode].name
          : name,
      }
    }

    // 区
    districts[code] = {
      province: provinceSixCode,
      city: citySixCode,
      ...base,
    }

    if (is_sub_districts_exist) {
      const sub_districts = require(sub_districts_path)
      Object.keys(sub_districts).forEach(subdistrict_code => {
        subdistricts[subdistrict_code] = {
          country: 156,
          province: provinceSixCode,
          city: citySixCode,
          district: base.code,
          name: sub_districts[subdistrict_code],
          code: Number.parseInt(subdistrict_code),
        }
      })
    }
  } else if (code.endsWith('0000')) {
    // 省
    provinces[code] = base
  } else {
    /// 市
    cities[code] = {
      ...base,
      province: provinceSixCode,
    }
  }
})

fs.writeFileSync(gen('subdistricts.json'), JSON.stringify(subdistricts))
fs.writeFileSync(gen('districts.json'), JSON.stringify(districts))
fs.writeFileSync(gen('cities.json'), JSON.stringify(cities))
fs.writeFileSync(gen('provinces.json'), JSON.stringify(provinces))

console.log('省: ', Object.keys(provinces).length)
console.log('市: ', Object.keys(cities).length)
console.log('区/县: ', Object.keys(districts).length)
console.log('镇/街道', Object.keys(subdistricts).length)

const generateSql = (table, columns, data) => {
  let sqlPath = gen(`./${table}.sql`)
  fs.writeFileSync(sqlPath, `INSERT INTO ${table} (${columns.join()}) VALUES`)
  let codes = []

  let values = Object.keys(data)
    .map(code => {
      codes.push(code)
      let x = data[code]
      let values = columns
        .map(column =>
          typeof x[column] === 'string' ? `'${x[column]}'` : x[column]
        )
        .join()
      return `(${values})`
    })
    .join()

  fs.appendFileSync(sqlPath, ` ${values};`)
  fs.writeFileSync(
    gen(`./${table}_drop.sql`),
    `DELETE FROM ${table} WHERE code = ANY(ARRAY[${codes.join()}]);`
  )
}

generateSql('provinces', ['code', 'country', 'name'], provinces)
generateSql('cities', ['code', 'country', 'province', 'name'], cities)
generateSql(
  'districts',
  ['code', 'country', 'province', 'city', 'name'],
  districts
)

generateSql(
  'subdistricts',
  ['code', 'country', 'province', 'city', 'district', 'name'],
  subdistricts
)

provinces

-- Your SQL goes here
CREATE TABLE provinces(
    code int NOT NULL PRIMARY KEY,
    country smallint NOT NULL,
    name text NOT NULL
);

cities

-- Your SQL goes here
CREATE TABLE cities(
    code int PRIMARY KEY NOT NULL,
    country smallint NOT NULL,
    province int NOT NULL,
    name text NOT NULL
);

CREATE INDEX idx_cities_country ON cities(country);

CREATE INDEX idx_cities_province ON cities(province);

CREATE INDEX idx_cities_name ON cities(name);

districts

-- Your SQL goes here
CREATE TABLE IF NOT EXISTS districts(
    code int PRIMARY KEY NOT NULL,
    country smallint NOT NULL,
    province int NOT NULL,
    city int NOT NULL,
    name text NOT NULL
);

CREATE INDEX idx_districts_country ON districts(country);

CREATE INDEX idx_districts_province ON districts(province);

CREATE INDEX idx_districts_city ON districts(city);

CREATE INDEX idx_districts_name ON districts(name);

subdistricts

-- Your SQL goes here
CREATE TABLE IF NOT EXISTS subdistricts(
    code int PRIMARY KEY NOT NULL,
    country smallint NOT NULL,
    province int NOT NULL,
    city int NOT NULL,
    district int NOT NULL,
    name text NOT NULL
);

CREATE INDEX idx_subdistricts_country ON subdistricts(country);

CREATE INDEX idx_subdistricts_province ON subdistricts(province);

CREATE INDEX idx_subdistricts_city ON subdistricts(city);

CREATE INDEX idx_subdistricts_district ON subdistricts(district);

CREATE INDEX idx_subdistricts_name ON subdistricts(name);

[mumuy]

嘉峪关市是地级市，行政区划编码就是620200，不合适在list.json中增加620201代码

[l-7-l]

@mumuy 感谢兄台指出， 没记错的话当时考虑的是一个占位符, 为了兼容省市区县镇五级行政机构格式 咱们这个结构 其他大多数国家都可以套

[mumuy]

嘉峪关市，东莞市等4个城市是直筒子市，没有区的，这个需要注意下