---
# System prepended metadata

title: Tai61 Database
tags: [ailabs]

---

<style>
    #doc.markdown-body{
        max-width: 1200px;
    }
</style>
###### tags: `ailabs`

# Tai61 Database

- [initdb.sql](https://gitlab.corp.ailabs.tw/smart-city/cpl-platform/platform/aerial-map-backend/blob/master/db/initdb.sql) - referenced from yi-chun's aerial-map-backend db.

## 1. Learn how to use QGIS

- [QGIS tutorial](https://www.youtube.com/watch?v=kCnNWyl9qSE)

- Plugins
    - QuickMapService
    - Lat Lon Tools

## 2. Install postgresql postgis, learn how to use shp2pgsql

- Install packages  
  `brew install postgresql`  
  `brew install postgis`  
  `brew install pgrouting`
  
- Start service  
  `brew services start postgresql`
  
- [Postgis tutorial](https://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg)

## 3. Create database "tai61", grant access to user "postgres"

- Create "postgres" user  
  `CREATE USER postgres WITH SUPERUSER;`  
  
- Create database "tai61"  
  `CREATE DATABASE tai61 OWNER postgres;`  
  
- Grant access to "postgres" with database "tai61"  
  `GRANT ALL PRIVILEGES ON DATABASE tai61 TO postgres;`  

## 4. Create extensions in "tai61" database

- Connect to "tai61" database  
  `psql -h localhost -U postgres -d tai61`  
  
- Create extensions  
  `CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;`  
  `CREATE EXTENSION IF NOT EXISTS postgis;`  
  `CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;`  
  `CREATE EXTENSION IF NOT EXISTS postgis_topology;`  
  `CREATE EXTENSION IF NOT EXISTS pgrouting;`  
  `CREATE EXTENSION IF NOT EXISTS plpgsql;`  

## 5. Load shape file to database

- Attractions  
  `shp2pgsql -s 4326 attractions/attractions.shp public.attractions | psql -h localhost -d tai61 -U postgres`  
  
- Points  
  `shp2pgsql -s 4326 points/points.shp public.points | psql -h localhost -d tai61 -U postgres`  
  
- Lines  
  `shp2pgsql -s 4326 lines/lines.shp public.lines | psql -h localhost -d tai61 -U postgres`  
  

## 6. Create index

- Points  
  `CREATE INDEX points_spx ON points USING GIST(geom);`  
  
- Lines  
  `CREATE INDEX liness_spx ON lines USING GIST(geom);`  

## 7. Dump database

- [Dump tutorial](https://www.postgresql.org/docs/9.1/backup-dump.html)  
  `pg_dump -h localhost -d tai61 -U postgres -f dump.sql`  
  
## 8. Cut street video log 
- Job consumer at lab-engine (earthgen 03_RD_90001_98_1)
```
python -m tools.cut_street_video_log \
    --drone_gpx /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1/00000004.BIN.gpx \
    --camera_gpx /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1/03_RD_90001_98_1_edit.mp4.gpx \
    --drone_log /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1/00000004.BIN \
    --waypoint /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1/03_RD_90001_98.waypoints \
    --video /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1/03_RD_90001_98_1_edit.mp4 \
    --processed_video_dir /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1 \
    --processed_clip_dir /volume/smart-city-data/drone_video/tai61/cpl-earthgen/0813WORK/03_RD_90001_98_1 \
    --db_host tainan-db \
    --db_name tai61 \
    --db_user $PGUSER \
    --db_password $PGPASSWORD
```

- Local (earthgen 01_RD_12_90012_90013)
```
python -m tools.cut_street_video_log \
    --drone_gpx /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013/00000038.BIN.gpx \
    --camera_gpx /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013/01_RD_12_90012_90013_edit.mp4.gpx \
    --drone_log /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013/00000038.BIN \
    --waypoint /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013/00000038.waypoints \
    --video /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013/01_RD_12_90012_90013_edit.mp4 \
    --processed_video_dir /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013_edit \
    --processed_clip_dir /Users/qhan/ailabs/drone-video-processing/tai61/earth-gen-0815/01_RD_12_90012_90013_edit \
    --db_host localhost \
    --db_name tai61 \
    --db_user postgres
```
