FdwService.java
7.72 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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
package com.sigem.gis.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class FdwService {
@Autowired
private GeoServerService geoServerService;
@Autowired
@Qualifier("masterJdbcTemplate")
private JdbcTemplate masterJdbcTemplate;
@Autowired
@Qualifier("gisJdbcTemplate")
private JdbcTemplate gisJdbcTemplate;
public void setupFdw(String entidadId) {
// 1. Obtener datos de la entidad desde el Directorio Maestro (.254)
String sqlEntidad = "SELECT sigem_site, sigem_dbname, boundno, boundse, latlong, zoom, maxzoom, minzoom " +
"FROM public.entidades WHERE entidad = ?";
List<Map<String, Object>> entidades = masterJdbcTemplate.queryForList(sqlEntidad, Integer.parseInt(entidadId));
if (entidades.isEmpty()) {
throw new RuntimeException("Entidad " + entidadId + " no encontrada en el directorio maestro.");
}
Map<String, Object> data = entidades.get(0);
String sigemSite = (String) data.get("sigem_site");
String sigemDbname = (String) data.get("sigem_dbname");
String boundNo = (String) data.get("boundno");
String boundSe = (String) data.get("boundse");
// Log de parámetros recuperados (Regla 5)
System.out.println("Configurando Entidad " + entidadId + ": Zoom=" + data.get("zoom") + ", Bounds=" + boundNo
+ "/" + boundSe);
// ... (lógica de extracción de host/port/user/pass igual) ...
String host = extractParam(sigemSite, "host", "127.0.0.1");
String port = extractParam(sigemSite, "port", "5432");
String user = extractParam(sigemSite, "user", "postgres");
String pass = extractParam(sigemSite, "password", "x25yvaga2017");
String serverName = "srv_mun_" + entidadId;
String schemaName = "fdw_" + entidadId;
// 2. Ejecutar comandos DDL en el servidor PostGIS (.123)
try {
// ... (verificación de infraestructura fdw igual hasta la creación de vistas)
// ...
String checkSql = "SELECT count(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = 'usuarios'";
Integer count = gisJdbcTemplate.queryForObject(checkSql, Integer.class, schemaName);
if (count == null || count == 0) {
// (creación de server, user mapping y esquema igual)
gisJdbcTemplate.execute("DROP SERVER IF EXISTS " + serverName + " CASCADE");
gisJdbcTemplate.execute(String.format(
"CREATE SERVER %s FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '%s', port '%s', dbname '%s')",
serverName, host, port, sigemDbname));
gisJdbcTemplate.execute(
String.format("CREATE USER MAPPING FOR sigem_user SERVER %s OPTIONS (user '%s', password '%s')",
serverName, user, pass));
gisJdbcTemplate.execute("CREATE SCHEMA IF NOT EXISTS " + schemaName);
gisJdbcTemplate.execute(String.format(
"IMPORT FOREIGN SCHEMA public LIMIT TO (v_liq_entidad_totalxobjeto, v_liq_entidad_percentiles, usuarios, ventanas_usuario) FROM SERVER %s INTO %s",
serverName, schemaName));
}
// 3. SIEMPRE Crear o Refrescar Vistas de Unión (JOIN)
String tableLotes = "public.e" + entidadId + "_lotes_conccc";
// Vista de Auditoría (MVT) - LIBERADA (Sin LIMIT)
String viewLotesName = "vw_lotes_morosidad_" + entidadId;
gisJdbcTemplate.execute(String.format(
"CREATE OR REPLACE VIEW public.%s AS SELECT l.*, m.inm_ficha, m.inm_ctacatastral, m.trb_total_deuda, m.trb_total_pago, m.ultimo_pago FROM %s l LEFT JOIN %s.v_liq_entidad_totalxobjeto m ON l.ccc = m.inm_ctacatastral",
viewLotesName, tableLotes, schemaName));
// Vista PNG FULL (WMS) - SIN LIMIT
String viewWmsName = "vw_lotes_wms_" + entidadId;
gisJdbcTemplate.execute(String.format(
"CREATE OR REPLACE VIEW public.%s AS SELECT l.*, m.inm_ficha, m.inm_ctacatastral, m.trb_total_deuda, m.trb_total_pago, m.ultimo_pago FROM %s l LEFT JOIN %s.v_liq_entidad_totalxobjeto m ON l.ccc = m.inm_ctacatastral",
viewWmsName, tableLotes, schemaName));
// 4. Sincronización con GeoServer
try {
// Publicar capa Auditoría (MVT)
geoServerService.publishLayer(viewLotesName, viewLotesName, null, boundNo, boundSe);
enableMvt(viewLotesName);
geoServerService.truncateCache(viewLotesName);
// Publicar capa PNG FULL (WMS) con Estilo
geoServerService.publishLayer(viewWmsName, viewWmsName, "morosidad_style_wms", boundNo, boundSe);
geoServerService.truncateCache(viewWmsName);
} catch (Exception e) {
System.err.println("Advertencia GS: " + e.getMessage());
}
System.out.println("Sincronización completa para Entidad " + entidadId);
} catch (Exception e) {
System.err.println("Error configurando FDW o Vistas: " + e.getMessage());
throw new RuntimeException("Fallo en la automatización SIG: " + e.getMessage(), e);
}
}
private String extractParam(String siteParam, String key, String defaultValue) {
if (siteParam != null && siteParam.contains(key + "=")) {
String[] parts = siteParam.split(key + "=");
if (parts.length > 1) {
return parts[1].split(" ")[0].trim();
}
}
if (key.equals("host") && siteParam != null && !siteParam.contains("=")) {
return siteParam.trim();
}
return defaultValue;
}
/**
* Habilita Vector Tiles (PBF) para una capa específica en el GeoServer local.
*/
public void enableMvt(String layerName) {
// Usar host interno 'geoserver' y password correcto 'geoserver'
String geoserverUrl = "http://localhost:8080/geoserver/gwc/rest/layers/sigem:" + layerName + ".xml";
String auth = "admin:geoserver";
String encodedAuth = java.util.Base64.getEncoder().encodeToString(auth.getBytes());
String xmlBody = "<GeoServerLayer><enabled>true</enabled><mimeFormats>" +
"<string>image/png</string><string>image/jpeg</string>" +
"<string>application/x-protobuf</string></mimeFormats></GeoServerLayer>";
System.out.println("Enviando configuración MVT para: " + layerName);
try {
java.net.URL url = new java.net.URL(geoserverUrl);
java.net.HttpURLConnection conn = (java.net.HttpURLConnection) url.openConnection();
conn.setRequestMethod("POST");
conn.setRequestProperty("Authorization", "Basic " + encodedAuth);
conn.setRequestProperty("Content-Type", "text/xml");
conn.setDoOutput(true);
// Establecer timeouts agresivos (1s) para no bloquear el LOGIN
conn.setConnectTimeout(1000);
conn.setReadTimeout(1000);
try (java.io.OutputStream os = conn.getOutputStream()) {
os.write(xmlBody.getBytes());
}
int code = conn.getResponseCode();
System.out.println("GeoServer GWC Response para " + layerName + ": " + code);
} catch (Exception e) {
System.err.println("Error habilitando MVT para " + layerName + ": " + e.getMessage());
}
}
}