Indonesia-Postal-Code

KO | EN

gitlite — search

#javascript #hacktoberfest #react #llm #golang #deep-learning #docker #java #cli #awesome-list #security #openai #ios #open-source

Indonesia-Postal-Code

(★ 103)

MySQL / PostgreSQL / JSON / Csv : Indonesia Postal Codes Database (Database Kode Pos Indonesia - Kelurahan - Kecamatan - Kota)

Indonesia-Postal-Code Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Indonesia Postal & Province Code

## RECOMMENDATION

Use our another repository [https://github.com/ArrayAccess/Indonesia-Postal-And-Area](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.

```txt
- 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](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/](https://www.mysql.com/)

```txt
(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

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

Note :

```txt
`[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

```sql
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
```json
{
    "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

```sql
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

```txt
+------+--------------+----------------------+--------------+---------------+-------------+---------------+
| 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
- [https://github.com/edwin/database-kodepos-seluruh-indonesia/](https://github.com/edwin/database-kodepos-seluruh-indonesia/)
- [http://kodepos.posindonesia.co.id/](http://kodepos.posindonesia.co.id/)

## LICENSE

MIT @see [LICENSE](LICENSE)

Indonesia-Postal-Code

Indonesia-Postal-Code Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Indonesia-Postal-Code

Indonesia-Postal-Code Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README