populate_snc.py
1.75 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
import json
import psycopg2
from psycopg2.extras import execute_values
def populate_table(json_file, table_name, mapping):
print(f"Cargando {json_file} en {table_name}...")
with open(json_file, 'r', encoding='utf-8') as f:
data = json.load(f)
conn = psycopg2.connect(
host="localhost",
database="sigem",
user="sigem_user",
password="sigem_pass",
port="5432"
)
cur = conn.cursor()
values = []
for feature in data['features']:
props = feature['properties']
geom = json.dumps(feature['geometry'])
row = []
for field in mapping:
row.append(props.get(field))
row.append(geom)
values.append(tuple(row))
placeholders = ",".join(["%s"] * len(mapping))
query = f"INSERT INTO {table_name} ({','.join(mapping)}, geom) VALUES %s"
# Transform geometry from GeoJSON string using ST_GeomFromGeoJSON
# Wait! execute_values doesn't easily support SQL functions in values.
# Better use direct insert loop or formatted values.
cur.execute(f"TRUNCATE TABLE {table_name}")
for val in values:
sql = f"INSERT INTO {table_name} ({','.join(mapping)}, geom) VALUES ({placeholders}, ST_SetSRID(ST_GeomFromGeoJSON(%s), 4326))"
cur.execute(sql, val)
conn.commit()
cur.close()
conn.close()
print(f"Carga finalizada: {len(values)} registros.")
# Mapping para Distritos
populate_table(
'/yvyape/proyectos/sigem-gis/snc_ly_dist.json',
'public.snc_raw_distritos',
['nom_dist', 'cod_dist', 'cod_dpto']
)
# Mapping para Departamentos
populate_table(
'/yvyape/proyectos/sigem-gis/snc_ly_dpto.json',
'public.snc_raw_departamentos',
['nom_dpto', 'cod_dpto']
)