3MU9es1u

1-- Function: public.insert_equal_geoms(text, text, text, text)
2
3-- DROP FUNCTION public.insert_equal_geoms(text, text, text, text);
4
5CREATE OR REPLACE FUNCTION public.insert_equal_geoms(
6    param_1 text, -- tindex 
7    param_2 text, --complete topo50 
8    param_3 text, -- error table 
9    param_4 text) -- spatially equal table 
10  RETURNS void AS
11$BODY$
12DECLARE
13   v_row_count integer;
14BEGIN
15
16v_row_count := NULL;
17
18   ---make error table if there are any instances of geometries not being equal to topo50 1k layer 
19EXECUTE format('
20   DROP TABLE IF EXISTS public.%s;
21   CREATE TABLE public.%s (
22   geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));
23
24   INSERT INTO public.%s
25   SELECT a.geom, b.indexname
26   FROM public.%s as a
27   LEFT JOIN public.%s as b ON ST_Equals(a.geom, b.geom)
28   WHERE b.indexname IS NULL;',param_3,param_3,param_3,param_1,param_2);
29
30GET DIAGNOSTICS v_row_count = ROW_COUNT;
31
32IF v_row_count = 0 THEN
33
34    EXECUTE format('DROP TABLE IF EXISTS public.%s;
35       CREATE TABLE public.%s (
36       geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));
37
38       INSERT INTO public.%s
39       SELECT b.geom, b.tilename, ''Spatially Equal''
40       FROM public.%s as a, public.%s as b
41       WHERE ST_Equals(a.geom, b.geom);',param_4, param_4, param_4, param_1, param_2);
42
43    RAISE NOTICE '%: Table updated.', timeofday()::timestamp;
44ELSE
45    RAISE NOTICE '%: % errors found. Check error table and resolve issues.', timeofday()::timestamp, v_row_count;
46END IF;
47
48END;
49   $BODY$
50  LANGUAGE plpgsql VOLATILE
51  COST 100;
52ALTER FUNCTION public.insert_equal_geoms(text, text, text, text)
53  OWNER TO postgres;
54
55SELECT public.insert_equal_geoms(public.sample1,public.sample2,public.sample3,public.sample4)