Revisioni di 2025임상도갱신.py

OGG ha revisionato questo gist 1773041484. Vai alla revisione

1 file changed, 27 insertions

foresttype_2025.sql(file creato)

		@@ -0,0 +1,27 @@
1	+	CREATE SEQUENCE public.foresttype_2025_gid_seq;
2	+
3	+	CREATE TABLE public.foresttype_2025 (
4	+	gid int4 DEFAULT nextval('foresttype_2025_gid_seq'::regclass) NOT NULL,
5	+	objectid int8 NULL,
6	+	storunst varchar(254) NULL,
7	+	fror_cd varchar(254) NULL,
8	+	frtp_cd varchar(254) NULL,
9	+	koftr_grou varchar(254) NULL,
10	+	dmcls_cd varchar(254) NULL,
11	+	agcls_cd varchar(254) NULL,
12	+	dnst_cd varchar(254) NULL,
13	+	height varchar(2) NULL,
14	+	ldmark_stn varchar(254) NULL,
15	+	map_label varchar(10) NULL,
16	+	갱신년도 varchar(8) NULL,
17	+	etc_pcmtt varchar(100) NULL,
18	+	frtp_nm varchar(100) NULL,
19	+	koftr_nm varchar(100) NULL,
20	+	dmcls_nm varchar(100) NULL,
21	+	agcls_nm varchar(100) NULL,
22	+	dnst_nm varchar(100) NULL,
23	+	shape_length float8 NULL,
24	+	shape_area float8 NULL,
25	+	geom public.geometry(multipolygon, 5186) NULL,
26	+	CONSTRAINT foresttype_2025_pkey PRIMARY KEY (gid)
27	+	);

OGG ha revisionato questo gist 1773036906. Vai alla revisione

1 file changed, 195 insertions

2025임상도갱신.py(file creato)

		@@ -0,0 +1,195 @@
1	+	import os
2	+	import subprocess
3	+	from datetime import datetime
4	+	import sys
5	+
6	+	PGHOST = "127.0.0.1"
7	+	PGPORT = 5433
8	+	PGDB = "postgres"
9	+	PGUSER = "postgres"
10	+	PGPASS = "postgres123"
11	+	PGSCHEMA = "public"
12	+	PGTABLE = "foresttype_2025"
13	+
14	+	S_SRS = "EPSG:5179"
15	+	T_SRS = "EPSG:5186"
16	+
17	+	GDB_PATH = r"C:\Users\ASUS\Desktop\dadrim\T2025imsangdo.gdb"
18	+
19	+	# GDB내에 존재하는 레이어명 명시
20	+	SOURCE_LAYERS = [
21	+	"T2025_일반임상도",
22	+	"T2025_접경임상도",
23	+	]
24	+
25	+	TRUNCATE_BEFORE_LOAD = False
26	+
27	+	LOG_DIR = os.path.dirname(GDB_PATH)
28	+	LOGFILE = os.path.join(LOG_DIR, f"import_log_{datetime.now().strftime('%Y%m%d_%H%M%S')}.txt")
29	+
30	+
31	+	def logger(message: str):
32	+	ts = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
33	+	line = f"[{ts}] {message}"
34	+	with open(LOGFILE, "a", encoding="utf-8") as f:
35	+	f.write(line + "\n")
36	+	print(line)
37	+
38	+
39	+	def run_cmd(cmd, env=None, timeout=None):
40	+	try:
41	+	res = subprocess.run(
42	+	cmd,
43	+	env=env,
44	+	capture_output=True,
45	+	text=True,
46	+	encoding="utf-8",
47	+	errors="replace",
48	+	timeout=timeout
49	+	)
50	+	return res.returncode, res.stdout, res.stderr
51	+	except subprocess.TimeoutExpired as e:
52	+	return 999, e.stdout or "", f"TIMEOUT after {timeout}s"
53	+
54	+
55	+	def psql_cmd(sql: str, timeout=60):
56	+	env = os.environ.copy()
57	+	env["PGPASSWORD"] = PGPASS
58	+	return run_cmd(
59	+	["psql", "-h", PGHOST, "-p", str(PGPORT), "-U", PGUSER, "-d", PGDB, "-v", "ON_ERROR_STOP=1", "-c", sql],
60	+	env=env,
61	+	timeout=timeout
62	+	)
63	+
64	+
65	+	def check_connection() -> bool:
66	+	code, out, err = psql_cmd("SELECT 'DB_CONNECTED';", timeout=10)
67	+	if code == 0 and "DB_CONNECTED" in out:
68	+	logger(">> DB 접속 확인 성공!")
69	+	return True
70	+	logger(f">> DB 접속 실패: {err.strip() or out.strip()}")
71	+	return False
72	+
73	+
74	+	def table_exists(schema: str, table: str) -> bool:
75	+	sql = (
76	+	"SELECT EXISTS ("
77	+	" SELECT 1 FROM information_schema.tables"
78	+	f" WHERE table_schema = '{schema}' AND table_name = '{table}'"
79	+	");"
80	+	)
81	+	env = os.environ.copy()
82	+	env["PGPASSWORD"] = PGPASS
83	+
84	+	code, out, err = run_cmd(
85	+	["psql", "-h", PGHOST, "-p", str(PGPORT), "-U", PGUSER, "-d", PGDB, "-t", "-A", "-c", sql],
86	+	env=env,
87	+	timeout=30
88	+	)
89	+	if code != 0:
90	+	raise RuntimeError(err.strip() or out.strip())
91	+
92	+	return out.strip().lower() == "t"
93	+
94	+
95	+	def truncate_target_table():
96	+	sql = f'TRUNCATE TABLE "{PGSCHEMA}"."{PGTABLE}";'
97	+	code, out, err = psql_cmd(sql, timeout=60)
98	+	if code != 0:
99	+	raise RuntimeError(err.strip() or out.strip())
100	+	logger(f'대상 테이블 TRUNCATE 완료: {PGSCHEMA}.{PGTABLE}')
101	+
102	+
103	+	def run_ogr2ogr_layer(layer_name: str):
104	+	conn_str = f"PG:host={PGHOST} port={PGPORT} dbname={PGDB} user={PGUSER} password={PGPASS}"
105	+
106	+	cmd = [
107	+	"ogr2ogr",
108	+	"-f", "PostgreSQL",
109	+	conn_str,
110	+	GDB_PATH,
111	+	layer_name,
112	+	"-nln", f"{PGSCHEMA}.{PGTABLE}",
113	+	"-append",
114	+	"-nlt", "PROMOTE_TO_MULTI",
115	+	"-s_srs", S_SRS,
116	+	"-t_srs", T_SRS,
117	+	"--config", "PG_USE_COPY", "YES",
118	+	"-gt", "10000",
119	+	"-progress"
120	+	]
121	+
122	+	logger(f"ogr2ogr 실행 레이어명: {layer_name}")
123	+
124	+	code, out, err = run_cmd(cmd, timeout=60 * 60 * 6)
125	+	if code != 0:
126	+	return False, (err.strip() or out.strip())
127	+	return True, ""
128	+
129	+
130	+	def create_index_and_analyze():
131	+	sql = (
132	+	f'CREATE INDEX IF NOT EXISTS "{PGTABLE}_geom_idx" '
133	+	f'ON "{PGSCHEMA}"."{PGTABLE}" USING GIST (geom); '
134	+	f'ANALYZE "{PGSCHEMA}"."{PGTABLE}";'
135	+	)
136	+	code, out, err = psql_cmd(sql, timeout=60 * 60)
137	+	if code != 0:
138	+	raise RuntimeError(err.strip() or out.strip())
139	+
140	+
141	+	def main():
142	+	if not check_connection():
143	+	logger("DB 접속 실패로 프로그램을 종료합니다.")
144	+	sys.exit(1)
145	+
146	+	exists = table_exists(PGSCHEMA, PGTABLE)
147	+	logger(f'대상 테이블 존재 여부: {"EXISTS" if exists else "NOT EXISTS"} ({PGSCHEMA}.{PGTABLE})')
148	+	if not exists:
149	+	logger("대상 테이블이 없습니다. Postgres에 테이블을 미리 생성한 뒤 다시 실행하세요.")
150	+	sys.exit(1)
151	+
152	+
153	+	if TRUNCATE_BEFORE_LOAD:
154	+	try:
155	+	truncate_target_table()
156	+	except Exception as e:
157	+	logger(f"TRUNCATE 실패: {e}")
158	+	sys.exit(1)
159	+
160	+	total_start = datetime.now()
161	+	error_layers = []
162	+
163	+	for i, layer in enumerate(SOURCE_LAYERS, start=1):
164	+	logger(f"[{i}/{len(SOURCE_LAYERS)}] 레이어 적재 시작: {layer}")
165	+	start = datetime.now()
166	+
167	+	ok, msg = run_ogr2ogr_layer(layer)
168	+	sec = (datetime.now() - start).total_seconds()
169	+
170	+	if ok:
171	+	logger(f"[{i}/{len(SOURCE_LAYERS)}] 적재 완료: {layer} ({sec:.2f}s)")
172	+	else:
173	+	logger(f"[{i}/{len(SOURCE_LAYERS)}] 적재 실패: {layer} ({sec:.2f}s) / 사유: {msg}")
174	+	error_layers.append(layer)
175	+
176	+	logger("GIST 인덱스 생성 및 ANALYZE 시작")
177	+	try:
178	+	create_index_and_analyze()
179	+	logger("GIST 인덱스 생성 및 ANALYZE 완료")
180	+	except Exception as e:
181	+	logger(f"인덱스 생성/ANALYZE 실패(수동 확인 필요): {e}")
182	+
183	+	total_sec = (datetime.now() - total_start).total_seconds()
184	+	logger("=" * 60)
185	+	logger("전체 종료")
186	+	logger(f"총 소요시간: {total_sec:.2f}초 ({total_sec/60:.1f}분)")
187	+	logger(f"로그: {LOGFILE}")
188	+	logger("=" * 60)
189	+
190	+	if error_layers:
191	+	print("\n!! 실패한 레이어 목록:", error_layers)
192	+
193	+
194	+	if __name__ == "__main__":
195	+	main()

Più nuovi Più vecchi