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
- SQLiteIndonesia Postal Codes Database
Database Kode Pos Indonesia - Kelurahan - Kecamatan - Kota
follow https://sig-dev.bps.go.id/webgis/pencariankodenama#data-table
Please consider change province code 92 code to 94
34 - Provinces
81248 - Areas (Postal Codes)
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)
Database/mysql_provinces.sql
mysql -u [userName] -p [databaseName] < mysql_provinces.sqlNote :
`[userName]` : MySQL User That Granted Access For Database
`[databaseName]` : Database Name target to import.Or just doing import process with phpMyAdmin
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:
-
provincesprovince_idINTEGER PRIMARY KEY AUTOINCREMENTprovince_namevarchar(255)province_name_envarchar(255)province_codeINTEGER ->UNIQUE INDEX
-
citiescity_idINTEGER PRIMARY KEY AUTOINCREMENTcity_namevarchar(255) ->INDEX->CONSTRAINT cities.city_name -> cities.province_codeprovince_codeINTEGER ->REFERENCES provinces.province_code
-
subdistrictssubdistrict_idINTEGER PRIMARY KEY AUTOINCREMENTsubdistrict_namevarchar(255) ->INDEX->CONSTRAINT subdistricts.subdistrict_name -> urbans.city_idcity_idINTEGER ->REFERENCES cities.id
-
urbansurban_idINTEGER PRIMARY KEY AUTOINCREMENTurban_namevarchar(255) ->INDEX->CONSTRAINT urbans.urban_name -> urbans.subdistrict_idpostal_codeINTEGER ->UNIQUE INDEXsubdistrict_idINTEGER ->REFERENCES subdistricts.subdistrict_id
{
"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"
}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 0EXAMPLE 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 |
+------+--------------+----------------------+--------------+---------------+-------------+---------------+| 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 ) |
MIT @see LICENSE