Awesome

Indonesia Postal & Province Code

RECOMMENDATION

Use our another repository https://github.com/ArrayAccess/Indonesia-Postal-And-Area when you need more complete data from Badan Pusat Statistic (BPS) Indonesia contains geolocations from country to sub-districts.

Postal codes, provinces & regions data in various formats.

- MySQL
- PostgreSQL
- JSON 
- Csv 
- SQLite

Indonesia Postal Codes Database

Database Kode Pos Indonesia - Kelurahan - Kecamatan - Kota

follow https://sig-dev.bps.go.id/webgis/pencariankodenama#data-table

I've got wrong !! The province code difference between `PAPUA` on BPS is 94

Please consider change province code 92 code to 94

Contains:

34      - Provinces
81248   - Areas (Postal Codes)

DATA TYPE

MySQL Database Syntax ( see at ) https://www.mysql.com/

(root)/
    ├── Csv/
    │    ├── Comma/     (contains csv comma as separator)
    │    │     ├── first_row_header_db_postal_code_data.csv (first row is table header)
    │    │     ├── first_row_header_db_province_data.csv    (first row is table header)
    │    │     ├── noheader_db_postal_code_data.csv
    │    │     └── noheader_db_province_data.csv
    │    │
    │    └── Semicolon/ (contains csv semicolon as separator)
    │          ├── first_row_header_db_postal_code_data.csv (first row is table header)
    │          ├── first_row_header_db_province_data.csv    (first row is table header)
    │          ├── noheader_db_postal_code_data.csv
    │          └── noheader_db_province_data.csv  
    │
    ├── Json/
    │     └─ (file name-*...).json     (various type JSON record files)       
    │
    └─── Database/
          ├── mysql_provinces.sql      (MySQL data)
          ├── postgresql_provinces.sql (PostgreSQL data)
          └── sqlite_provinces.sqlite (SQLite Binary)

MySQL Database To Import

Database/mysql_provinces.sql

Import With CLI

mysql -u [userName] -p [databaseName] < mysql_provinces.sql

Note :

`[userName]`      : MySQL User That Granted Access For Database
`[databaseName]`  : Database Name target to import.

Or just doing import process with phpMyAdmin

REFERENCES

The SQL Dump contains drop tables

DROP TABLE IF EXISTS `db_province_data`;
DROP TABLE IF EXISTS `db_postal_code_data`;

Please make sure delete the lines (on mysql_provinces.sql or postgresql_provinces.sql ) or backup database before doing import process.

I'm sorry about unoptimized database structures.

Maybe you could add more index & split it into:

provinces
- province_id INTEGER PRIMARY KEY AUTOINCREMENT
- province_name varchar(255)
- province_name_en varchar(255)
- province_code INTEGER -> UNIQUE INDEX
cities
- city_id INTEGER PRIMARY KEY AUTOINCREMENT
- city_name varchar(255) -> INDEX -> CONSTRAINT cities.city_name -> cities.province_code
- province_code INTEGER -> REFERENCES provinces.province_code
subdistricts
- subdistrict_id INTEGER PRIMARY KEY AUTOINCREMENT
- subdistrict_name varchar(255) -> INDEX -> CONSTRAINT subdistricts.subdistrict_name -> urbans.city_id
- city_id INTEGER -> REFERENCES cities.id
urbans
- urban_id INTEGER PRIMARY KEY AUTOINCREMENT
- urban_name varchar(255) -> INDEX -> CONSTRAINT urbans.urban_name -> urbans.subdistrict_id
- postal_code INTEGER -> UNIQUE INDEX
- subdistrict_id INTEGER -> REFERENCES subdistricts.subdistrict_id

PROVINCES REFERENCES

{
    "11" : "ACEH",
    "12" : "SUMATERA UTARA",
    "13" : "SUMATERA BARAT",
    "14" : "RIAU",
    "15" : "JAMBI",
    "16" : "SUMATERA SELATAN",
    "17" : "BENGKULU",
    "18" : "LAMPUNG",
    "19" : "KEPULAUAN BANGKA BELITUNG",
    "21" : "KEPULAUAN RIAU",
    "31" : "DAERAH KHUSUS IBUKOTA JAKARTA",
    "32" : "JAWA BARAT",
    "33" : "JAWA TENGAH",
    "34" : "DAERAH ISTIMEWA YOGYAKARTA",
    "35" : "JAWA TIMUR",
    "36" : "BANTEN",
    "51" : "BALI",
    "52" : "NUSA TENGGARA BARAT",
    "53" : "NUSA TENGGARA TIMUR",
    "61" : "KALIMANTAN BARAT",
    "62" : "KALIMANTAN TENGAH",
    "63" : "KALIMANTAN SELATAN",
    "64" : "KALIMANTAN TIMUR",
    "65" : "KALIMANTAN UTARA",
    "71" : "SULAWESI UTARA",
    "72" : "SULAWESI TENGAH",
    "73" : "SULAWESI SELATAN",
    "74" : "SULAWESI TENGGARA",
    "75" : "GORONTALO",
    "76" : "SULAWESI BARAT",
    "81" : "MALUKU",
    "82" : "MALUKU UTARA",
    "91" : "PAPUA",
    "92" : "PAPUA BARAT"
}

EXAMPLE MySQL CODE TO USE BOTH TABLE

SELECT
  `db_postal_code_data`.*,
  `db_province_data`.`province_name`
FROM
  `db_postal_code_data`
INNER JOIN
  `db_province_data` ON `db_province_data`.`province_code` = `db_postal_code_data`.`province_code`
WHERE
  `db_province_data`.`province_code` = 11
ORDER BY
  `db_postal_code_data`.`id` DESC
LIMIT 10 OFFSET 0

EXAMPLE OUTPUT

+------+--------------+----------------------+--------------+---------------+-------------+---------------+
| id   | urban        | sub_district         | city         | province_code | postal_code | province_name |
+------+--------------+----------------------+--------------+---------------+-------------+---------------+
| 6464 | YUB MEE      | INDRAJAYA            | PIDIE        |            11 | 24171       | ACEH          |
| 6463 | WUE LHOK     | MANTASIEK (MONTASIK) | ACEH BESAR   |            11 | 23362       | ACEH          |
| 6462 | WONOSOBO     | WIH PESAM            | BENER MERIAH |            11 | 24581       | ACEH          |
| 6461 | WONOSARI     | BANDAR               | BENER MERIAH |            11 | 24582       | ACEH          |
| 6460 | WONO SARI    | TAMIANG HULU         | ACEH TAMIANG |            11 | 24478       | ACEH          |
| 6459 | WIHNI DURIN  | SYIAH UTAMA          | BENER MERIAH |            11 | 24582       | ACEH          |
| 6458 | WIHNI DURIN  | SILIH NARA           | ACEH TENGAH  |            11 | 24562       | ACEH          |
| 6457 | WIHNI BAKONG | SILIH NARA           | ACEH TENGAH  |            11 | 24562       | ACEH          |
| 6456 | WIHLAH SETIE | BINTANG              | ACEH TENGAH  |            11 | 24571       | ACEH          |
| 6455 | WIH TERJUN   | PEGASING             | ACEH TENGAH  |            11 | 24561       | ACEH          |
+------+--------------+----------------------+--------------+---------------+-------------+---------------+

NOTE (Indonesia)

Table Name	Description
`id`	Increment number / kode urut otomatis
`urban`	Nama Kelurahan
`city`	Nama Kota
`sub_district`	kecamatan
`postal_code`	Kode Pos Daerah
`province_code`	Kode Provinsi ( Standar )
`province_name`	Nama Provinsi ( Bahasa Indonesia )
`province_name_en`	Nama Provinsi ( Bahasa Inggris )

LINK

LICENSE

MIT @see LICENSE