Révisions pour newMyMnt.sql

1 file changed, 82 insertions

newMyMnt.sql(fichier créé)

		@@ -0,0 +1,82 @@
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;

Plus récent Plus ancien