Guides

Topo COU Post Processing

Suggest Edits
--SQL to de duplicate the COU tables
DELETE FROM tm.topographicarea ta WHERE EXISTS (SELECT 1 FROM tm.topographicarea ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);
DELETE FROM tm.topographicline ta WHERE EXISTS (SELECT 1 FROM tm.topographicline ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);
DELETE FROM tm.topographicpoint ta WHERE EXISTS (SELECT 1 FROM tm.topographicpoint ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);
DELETE FROM tm.boundaryline ta WHERE EXISTS (SELECT 1 FROM tm.boundaryline ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);
DELETE FROM tm.cartographictext ta WHERE EXISTS (SELECT 1 FROM tm.cartographictext ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);
DELETE FROM tm.cartographicsymbol ta WHERE EXISTS (SELECT 1 FROM tm.cartographicsymbol ta1 WHERE ta1.fid = ta.fid AND ta1.ogc_fid > ta.ogc_fid);


--SQL for BoundaryLine tables
ALTER TABLE osmm.boundaryline ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.boundaryline SET status = 'L';
COMMIT;
ALTER TABLE osmm.boundaryline ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.boundaryline ADD COLUMN update_date date;
COMMIT;

UPDATE osmm.boundaryline SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.boundaryline SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.boundaryline); 
COMMIT;
INSERT INTO osmm.boundaryline (wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms FROM tm.boundaryline;
COMMIT;
UPDATE osmm.boundaryline SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.boundaryline) and update_date IS NULL and status IS NULL; 
COMMIT;

--SQL for CartoSymbol
ALTER TABLE osmm.cartographicsymbol ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.cartographicsymbol SET status = 'L';
COMMIT;
ALTER TABLE osmm.cartographicsymbol ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.cartographicsymbol ADD COLUMN update_date date;
COMMIT;

UPDATE osmm.cartographicsymbol SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.cartographicsymbol SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.cartographicsymbol); 
COMMIT;
INSERT INTO osmm.cartographicsymbol (wkb_geometry,fid,featurecode,version,versiondate,theme,changedate,reasonforchange,descriptivegroup,descriptiveterm,orientation,physicallevel,physicalpresence,referencetofeature,filename,themes,descriptivegroups,descriptiveterms,orientdeg)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,changedate,reasonforchange,descriptivegroup,descriptiveterm,orientation,physicallevel,physicalpresence,referencetofeature,filename,themes,descriptivegroups,descriptiveterms,orientdeg FROM tm.cartographicsymbol;
COMMIT;
UPDATE osmm.cartographicsymbol SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.cartographicsymbol) and update_date IS NULL and status IS NULL; 
COMMIT;

--SQL for CartoText tables
ALTER TABLE osmm.cartographictext ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.cartographictext SET status = 'L';
COMMIT;
ALTER TABLE osmm.cartographictext ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.cartographictext ADD COLUMN update_date date;
COMMIT;

UPDATE osmm.cartographictext SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.cartographictext SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.cartographictext); 
COMMIT;
INSERT INTO osmm.cartographictext (wkb_geometry,fid,featurecode,version,versiondate,theme,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,
anchorposition,font,height,orientation,textstring,filename,themes,descriptivegroups,descriptiveterms,orientdeg,fcode,anchor)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,
anchorposition,font,height,orientation,textstring,filename,themes,descriptivegroups,descriptiveterms,orientdeg,fcode,anchor FROM tm.cartographictext;
COMMIT;
UPDATE osmm.cartographictext SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.cartographictext) and update_date IS NULL and status IS NULL; 
COMMIT;


--SQL for TopoArea Table

ALTER TABLE osmm.topographicarea ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.topographicarea SET status = 'L';
COMMIT;
ALTER TABLE osmm.topographicarea ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.topographicarea ADD COLUMN update_date date;
COMMIT;

UPDATE osmm.topographicarea SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.topographicarea SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.topographicarea); 
COMMIT;
INSERT INTO osmm.topographicarea (wkb_geometry,fid,featurecode,version,versiondate,theme,calculatedareavalue,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms,fcode)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,calculatedareavalue,changedate,reasonforchange,descriptivegroup,descriptiveterm,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms,fcode FROM tm.topographicarea;
COMMIT;
UPDATE osmm.topographicarea SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.topographicarea) and update_date IS NULL and status IS NULL; 
COMMIT;


--SQL for TopoLine Table
ALTER TABLE osmm.topographicline ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.topographicline SET status = 'L';
COMMIT;
ALTER TABLE osmm.topographicline ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.topographicline ADD COLUMN update_date date;
COMMIT;




UPDATE osmm.topographicline SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.topographicline SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.topographicline); 
COMMIT;
INSERT INTO osmm.topographicline (wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,nonboundingline,
heightabovedatum,accuracyofheightabovedatum,heightabovegroundlevel,accuracyofheightabovegroundlevel,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms,fcode)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,nonboundingline,
heightabovedatum,accuracyofheightabovedatum,heightabovegroundlevel,accuracyofheightabovegroundlevel,make,physicallevel,physicalpresence,filename,themes,descriptivegroups,descriptiveterms,fcode FROM tm.topographicline;
COMMIT;
UPDATE osmm.topographicline SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.topographicline) and update_date IS NULL and status IS NULL; 
COMMIT;


--SQL for TopoPoint Table
ALTER TABLE osmm.topographicpoint ADD COLUMN status varchar(1);
COMMIT;
UPDATE osmm.topographicpoint SET status = 'L';
COMMIT;
ALTER TABLE osmm.topographicpoint ADD COLUMN delete_date date;
COMMIT;
ALTER TABLE osmm.topographicpoint ADD COLUMN update_date date;
COMMIT;

UPDATE osmm.topographicpoint SET status = 'D', delete_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.departedfeature); 
COMMIT;
UPDATE osmm.topographicpoint SET status = 'U', update_date = '141107' WHERE fid IN (SELECT distinct fid FROM tm.topographicpoint); 
COMMIT;
INSERT INTO osmm.topographicpoint (wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,
heightabovedatum,accuracyofheightabovedatum,make,physicallevel,physicalpresence,referencetofeature,filename,themes,descriptivegroups,descriptiveterms)
SELECT wkb_geometry,fid,featurecode,version,versiondate,theme,accuracyofposition,changedate,reasonforchange,descriptivegroup,descriptiveterm,
heightabovedatum,accuracyofheightabovedatum,make,physicallevel,physicalpresence,referencetofeature,filename,themes,descriptivegroups,descriptiveterms FROM tm.topographicpoint;
COMMIT;
UPDATE osmm.topographicpoint SET status = 'L' WHERE fid IN (SELECT distinct fid FROM tm.topographicpoint) and update_date IS NULL and status IS NULL; 
COMMIT;

Updated less than a minute ago