# homework
## A:
### a.
這題想不太到要怎麼自動化的得到所有不同的顏色，因為還有AIR-BLUE這種顏色也沒辦法用-來分割,又有closet這種不是顏色的，因此我先自己建了一個table記錄所有的顏色
```sql=
CREATE TABLE colorlist (color varchar);
INSERT INTO geometries VALUES
('AIR-BLUE'),('BLUE'),('BROWN'),('GREEN'),('GREY'),
('LIME'),('ORANGE'),('PURPLE'),('RED'),('SI-BLUE'),('YELLOW');
```
後續的code:
```sql=
SELECT SUM(subtable.population), colorlist.color FROM
(SELECT
station.color as station_color,
SUM(cen.popn_total) as population
FROM nyc_subway_stations as station,
nyc_census_blocks AS cen
where ST_DWithin(station.geom, cen.geom, 200)
GROUP BY station.color) as subtable, colorlist
WHERE subtable.station_color LIKE CONCAT('%', colorlist.color, '%')
GROUP BY colorlist.color;
```
裡面的SELECT是各station經過的route以及population數量，印出來結果是:
最後結果:
### b.
code:
```sql=
SELECT subset.name,
subset.population/size as density,
subset.size FROM
(
SELECT neighbor.name,
SUM(block.popn_total) as population
,ST_Area(neighbor.geom)/1000000 as size
FROM nyc_neighborhoods as neighbor,
nyc_census_blocks as block
WHERE ST_DWITHIN(
neighbor.geom,block.geom
,50
)
GROUP BY name,size
LIMIT 10) as subset;
```
參考lab handout使用neighbor 50公尺以內的 nyc_census_blocks total population來計算人數
裡面的table分別記錄name,sum of population, size
外面則再抓裡面的population和size算出density
OUTPUT:
### c.
原本的速度:9852.457 ms
使用INDEX:新增nyc_census_blocks popn)total的POPN_TOTAL
```sql=
CREATE Index IX_tbl_population
ON nyc_census_blocks (POPN_TOTAL ASC);
```
新的速度:9507.370 有所提升
建立geom的效果可能會更好 但空間需求太大無法建立
## B:
第一個DATASET為芬蘭水災區域的分布，geom型態為MultiPolygon
第二個DATASET是芬蘭戲水區的點位,geom型態為point
Data來源:https://www.syke.fi/en-US/Open_information/Spatial_datasets/Downloadable_spatial_dataset#C
- [ ] 使用的query:
找出離發生過水災區域距離5以內的戲水區的名字以及point，可以用來劃分戲水區危險程度
QGIS上的圖:
```sql=
SELECT uimavesini as name, ST_AsText(bath.geom) as point
FROM euuimavesi as bath
,maartlvveskohde as flood
WHERE ST_DWithin(bath.geom,
flood.geom, 5)
LIMIT 100;
```
output: 芬蘭文 有些字非英文字母沒辦法正常顯示
QGIS:
危險點:
GOOGLE EARTH:
{"metaMigratedAt":"2023-06-16T15:18:31.488Z","metaMigratedFrom":"Content","title":"homework","breaks":true,"contributors":"[{\"id\":\"537a9f98-0719-42a9-9e4e-f0c6c19122ea\",\"add\":2801,\"del\":445}]"}