-- Function: at_sync_mapinfo_mapcatalog()
-- DROP FUNCTION at_sync_mapinfo_mapcatalog();
CREATE OR REPLACE FUNCTION at_sync_mapinfo_mapcatalog()
RETURNS trigger AS
$BODY1$
--$BODY$
DECLARE
r record;
insertStr varchar;
deleteStr varchar;
spatialtype varchar;
symbol varchar;
projection varchar;
count integer;
message varchar;
default_symbol_point varchar;
default_symbol_line varchar;
default_symbol_polygon varchar;
-- default_symbol_mixed varchar;
BEGIN
-- extract default symbology from mapinfo_mapcatalog_configuration
-- create some default defaults first in case table data doesn't exist
default_symbol_point := 'SYMBOL (34,12632064,10) ';
default_symbol_line := 'PEN (1, 2, 0) ';
default_symbol_polygon := 'PEN (1, 2, 8388608) BRUSH (16, 16776960, 16777215) ';
-- default_symbol_mixed := 'SYMBOL (34,12632064,10) PEN (1, 2, 8388608) BRUSH (16, 16776960, 16777215) ';
FOR r in SELECT * FROM mapinfo.mapinfo_mapcatalog_configuration LOOP
IF (r.setting_name = 'SYMBOL_POINT') THEN
default_symbol_point := r.setting_value;
END IF;
IF (r.setting_name = 'SYMBOL_LINE') THEN
default_symbol_line := r.setting_value;
END IF;
IF (r.setting_name = 'SYMBOL_POLYGON') THEN
default_symbol_polygon := r.setting_value;
END IF;
--IF (r."SETTING_NAME" = 'SYMBOL_MIXED') THEN
-- default_symbol_mixed := r."SETTING_VALUE";
--END IF;
END LOOP;
-- first, select entries in geometry_columns which don't exist in mapinfo_mapcatalog
-- and add them to mapinfo_mapcatalog
count := 0;
FOR r in SELECT * FROM geometry_columns T2 WHERE NOT EXISTS (SELECT * FROM mapinfo.mapinfo_mapcatalog T1 WHERE T1.ownername = T2.f_table_schema AND T1.tablename = T2.f_table_name) LOOP
spatialtype := '19.3';
symbol := default_symbol_point || default_symbol_polygon;
IF (r.type = 'LINESTRING' OR r.type = 'MULTILINESTRING') THEN
spatialtype := '19.1';
symbol := default_symbol_line;
END IF;
IF (r.type = 'POLYGON' OR r.type = 'MULTIPOLYGON') THEN
spatialtype := '19.2';
symbol := default_symbol_polygon;
END IF;
IF (r.type = 'POINT' OR r.type = 'MULTIPOINT') THEN
spatialtype := '19';
symbol := default_symbol_point;
END IF;
projection := E'Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (-7845061.1011, -15524202.1641) (8645061.1011, 4470074.53373)';
insertStr := E'INSERT INTO mapinfo.mapinfo_mapcatalog ('
|| 'spatialtype, tablename, ownername, spatialcolumn, db_x_ll, db_y_ll, '
|| 'db_x_ur, db_y_ur, view_x_ll, view_y_ll, view_x_ur, view_y_ur, coordinatesystem, symbol, xcolumnname, '
|| 'ycolumnname, renditiontype, renditiontable) '
|| 'VALUES (' || spatialtype || E',\'' || r.f_table_name || E'\', \'' || r.f_table_schema || E'\', \'' || r.f_geometry_column || E'\', 0, 0, '
|| E'700000, 1300000, 0, 0, 700000, 1300000, \'' || projection || E'\', \'' || symbol || E'\', \'NO_COLUMN\', '
|| E'\'NO_COLUMN\', 0, \'\');';
EXECUTE insertStr;
count := count + 1;
END LOOP;
message := count || ' inserted, ';
-- second, delete entries in mapinfo_mapcatalog which don't exist in geometry_columns
count := 0;
FOR r in SELECT * FROM mapinfo.mapinfo_mapcatalog T1 WHERE NOT EXISTS (SELECT * FROM geometry_columns T2 WHERE T1.ownername = T2.f_table_schema AND T1.tablename = T2.f_table_name) LOOP
deleteStr := E'DELETE FROM mapinfo.mapinfo_mapcatalog WHERE ownername = \'' || r.ownername || E'\' AND tablename = \'' || r.tablename || E'\';';
EXECUTE deleteStr;
count := count + 1;
END LOOP;
message := 'mapinfo_mapcatalog: ' || message || count || ' removed';
--return message;
RETURN NULL;
END;
--$BODY$
$BODY1$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION at_sync_mapinfo_mapcatalog() OWNER TO postgres; |