newMyMnt.sql
· 1.4 KiB · MySQL
Raw
WITH land AS (
SELECT
pnu,
jibun,
geom
FROM lsmd_cont_ldreg
WHERE pnu LIKE '51760380%'
AND RIGHT(jibun, 1) NOT IN ('도', '구', '천')
), jukjung AS (
SELECT
a.pnu,
jukjung,
jukjung1
FROM filtered_customforest a
INNER JOIN land ON a.pnu = land.pnu
), jaebe AS (
SELECT
a.pnu,
jaebe,
jaebe1
FROM filtered_shortterm_forestproduct a
INNER JOIN land ON a.pnu = land.pnu
), namu AS (
SELECT
a.pnu,
na_imsang,
na_yunggub,
na_kyunggub,
na_mildo
FROM filtered_foresttype a
INNER JOIN land ON a.pnu = land.pnu
), toyang AS (
SELECT
a.pnu,
to_jiri,
to_jihyung,
to_moam,
to_tosim,
to_yugi,
to_tosung,
to_geunsup,
to_geunmildo
FROM filtered_soil a
INNER JOIN land ON a.pnu = land.pnu
), dem AS (
SELECT
a.pnu,
avg_height,
avg_slope,
avg_aspect
FROM filtered_dem a
INNER JOIN land ON a.pnu = land.pnu
)
SELECT
land.pnu,
land.jibun,
ROUND((ST_Area(land.geom) / 10000)::NUMERIC, 2) AS area_hectare,
jukjung,
jukjung1,
jaebe,
jaebe1,
na_imsang,
na_yunggub,
na_kyunggub,
na_mildo,
to_jiri,
to_jihyung,
to_moam,
to_tosim,
to_yugi,
to_tosung,
to_geunsup,
to_geunmildo,
avg_height,
avg_slope,
avg_aspect
FROM land
LEFT OUTER JOIN jukjung ON land.pnu = jukjung.pnu
LEFT OUTER JOIN jaebe ON land.pnu = jaebe.pnu
LEFT OUTER JOIN namu ON land.pnu = namu.pnu
LEFT OUTER JOIN toyang ON land.pnu = toyang.pnu
LEFT OUTER JOIN dem ON land.pnu = dem.pnu;
| 1 | WITH land AS ( |
| 2 | SELECT |
| 3 | pnu, |
| 4 | jibun, |
| 5 | geom |
| 6 | FROM lsmd_cont_ldreg |
| 7 | WHERE pnu LIKE '51760380%' |
| 8 | AND RIGHT(jibun, 1) NOT IN ('도', '구', '천') |
| 9 | ), jukjung AS ( |
| 10 | SELECT |
| 11 | a.pnu, |
| 12 | jukjung, |
| 13 | jukjung1 |
| 14 | FROM filtered_customforest a |
| 15 | INNER JOIN land ON a.pnu = land.pnu |
| 16 | ), jaebe AS ( |
| 17 | SELECT |
| 18 | a.pnu, |
| 19 | jaebe, |
| 20 | jaebe1 |
| 21 | FROM filtered_shortterm_forestproduct a |
| 22 | INNER JOIN land ON a.pnu = land.pnu |
| 23 | ), namu AS ( |
| 24 | SELECT |
| 25 | a.pnu, |
| 26 | na_imsang, |
| 27 | na_yunggub, |
| 28 | na_kyunggub, |
| 29 | na_mildo |
| 30 | FROM filtered_foresttype a |
| 31 | INNER JOIN land ON a.pnu = land.pnu |
| 32 | ), toyang AS ( |
| 33 | SELECT |
| 34 | a.pnu, |
| 35 | to_jiri, |
| 36 | to_jihyung, |
| 37 | to_moam, |
| 38 | to_tosim, |
| 39 | to_yugi, |
| 40 | to_tosung, |
| 41 | to_geunsup, |
| 42 | to_geunmildo |
| 43 | FROM filtered_soil a |
| 44 | INNER JOIN land ON a.pnu = land.pnu |
| 45 | ), dem AS ( |
| 46 | SELECT |
| 47 | a.pnu, |
| 48 | avg_height, |
| 49 | avg_slope, |
| 50 | avg_aspect |
| 51 | FROM filtered_dem a |
| 52 | INNER JOIN land ON a.pnu = land.pnu |
| 53 | ) |
| 54 | SELECT |
| 55 | land.pnu, |
| 56 | land.jibun, |
| 57 | ROUND((ST_Area(land.geom) / 10000)::NUMERIC, 2) AS area_hectare, |
| 58 | jukjung, |
| 59 | jukjung1, |
| 60 | jaebe, |
| 61 | jaebe1, |
| 62 | na_imsang, |
| 63 | na_yunggub, |
| 64 | na_kyunggub, |
| 65 | na_mildo, |
| 66 | to_jiri, |
| 67 | to_jihyung, |
| 68 | to_moam, |
| 69 | to_tosim, |
| 70 | to_yugi, |
| 71 | to_tosung, |
| 72 | to_geunsup, |
| 73 | to_geunmildo, |
| 74 | avg_height, |
| 75 | avg_slope, |
| 76 | avg_aspect |
| 77 | FROM land |
| 78 | LEFT OUTER JOIN jukjung ON land.pnu = jukjung.pnu |
| 79 | LEFT OUTER JOIN jaebe ON land.pnu = jaebe.pnu |
| 80 | LEFT OUTER JOIN namu ON land.pnu = namu.pnu |
| 81 | LEFT OUTER JOIN toyang ON land.pnu = toyang.pnu |
| 82 | LEFT OUTER JOIN dem ON land.pnu = dem.pnu; |