SHARE
TWEET

Untitled

a guest Jun 20th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: public.insert_equal_geoms(text, text, text, text)
  2.  
  3. -- DROP FUNCTION public.insert_equal_geoms(text, text, text, text);
  4.  
  5. CREATE 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$
  12. DECLARE
  13.    v_row_count integer;
  14. BEGIN
  15.  
  16. v_row_count := NULL;
  17.  
  18.    ---make error table if there are any instances of geometries not being equal to topo50 1k layer
  19. EXECUTE 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.  
  30. GET DIAGNOSTICS v_row_count = ROW_COUNT;
  31.  
  32. IF 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;
  44. ELSE
  45.     RAISE NOTICE '%: % errors found. Check error table and resolve issues.', timeofday()::timestamp, v_row_count;
  46. END IF;
  47.  
  48. END;
  49.    $BODY$
  50.   LANGUAGE plpgsql VOLATILE
  51.   COST 100;
  52. ALTER FUNCTION public.insert_equal_geoms(text, text, text, text)
  53.   OWNER TO postgres;
  54.  
  55. SELECT public.insert_equal_geoms(public.sample1,public.sample2,public.sample3,public.sample4)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top