Changes between Initial Version and Version 1 of UsersWikiCheckInvalidGeometriesFromGeometryColumns

Timestamp:

May 8, 2011, 9:07:27 AM (13 years ago)

Author:

aperi2007

Comment:

—

UsersWikiCheckInvalidGeometriesFromGeometryColumns

@@ +1 @@
+procedure to check all the tables listed in GeometryColumn table for invalid geometries.
+
+The procedure ask as parameter  table for the results.
+If all tables has valid geometries the table will be empty otherwise it has 1 record for every table with invalid geometries.
+The record will have two column the query used and the number of invalid geometries found.
+
+{{{
+CREATE OR REPLACE FUNCTION massive_check_geometry_validity(tabris text)
+RETURNS VOID AS $$
+DECLARE
+        sql text;
+        sql2 text;
+    _rec1 record;
+    ris integer;
+BEGIN
+        sql := 'DROP TABLE IF EXISTS ' || quote_ident(tabris) ||';';
+    execute(sql);
+    sql := 'CREATE TABLE ' || quote_ident(tabris) || ' (id serial, query TEXT, conteggio INTEGER);';
+        sql := 'select ''select count(*) from '' || quote_ident(f_table_schema) || ''.'' || quote_ident(f_table_name) || '' where ST_IsValid('' || f_geometry_column || '')=false;'' as query from public.geometry_columns';
+
+        FOR _rec1 IN execute (sql)
+                LOOP
+            raise notice 'executing: %', _rec1.query;
+            execute(_rec1.query) into ris;
+                END LOOP;
+
+        RETURN;
+END;
+$$ LANGUAGE 'plpgsql' STRICT;
+
+-- to use execute this query:
+--     select massive_check_geometry_validity('result_table');
+--
+}}}
+
+Happy checking, 
+Andrea Peri.