<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