[postgis-devel] probe_geometry_columns
Chris Hodgson
chodgson at refractions.net
Tue Apr 11 11:02:07 PDT 2006
Sounds sweet. Perhaps "repair_geometry_columns"? Or, there used to be a
"fix_geometry_columns" I think... I'm not sure what the original "probe"
and/or fix did but this sounds pretty full-featured.
Chris
Kevin Neufeld wrote:
> Hello all,
>
> I've created a more robust version of the probe_geometry_columns
> function. Often, during development, many spatial tables are formed and
> more often than not, developers can't be bothered to religiously use the
> add/dropgeometrycolumn methods when creating or dropping tables.
> Unfortunately, the probe_geometry_columns does not deal with these
> spatial columns (ie. CREATE TABLE mytbl (LIKE somespatialtable) .... or
> CREATE TABLE mytbl AS SELECT * FROM somespatialtable...)
>
> This version of probe_geometry_columns (that I've temporarily dubbed
> populate_geometry_columns) will
> 1. truncate geometry_columns
> 2. using the system tables, insert all spatial columns to
> geometry_columns where spatial constraints exists
> 3. probe remaining columns for srid, ndims, and geotype, apply
> necessary table constraints, and insert the spatial column into
> geometry_columns.
> 4. gracefully throw a warning if the srid/ndims/geotype values could
> not be determined (ie. empty table or null geometries)
>
> Let me know if you think this might be useful.
>
> Cheers,
> Kevin.
>
>
> ------------------------------------------------------------------------
>
> -- Function: public.populate_geometry_columns()
>
> -- DROP FUNCTION public.populate_geometry_columns()
>
> CREATE OR REPLACE FUNCTION public.populate_geometry_columns()
> RETURNS text AS
> $BODY$
> DECLARE
> inserted integer;
> oldcount integer;
> probed integer;
> stale integer;
> gcs RECORD;
> gc RECORD;
> gsrid integer;
> gsrid_cnt integer;
> gndims integer;
> gndims_cnt integer;
> gtype text;
> gtype_cnt text;
> BEGIN
>
> SELECT count(*) INTO oldcount FROM geometry_columns;
> inserted := 0;
>
> EXECUTE 'TRUNCATE geometry_columns';
>
> SELECT count(*) INTO probed
> FROM pg_class c,
> pg_attribute a,
> pg_type t,
> pg_namespace n
> WHERE c.relkind = 'r'
> AND t.typname = 'geometry'
> AND a.attisdropped = false
> AND a.atttypid = t.oid
> AND a.attrelid = c.oid
> AND c.relnamespace = n.oid;
>
> -- Iterate through all non-dropped geometry columns
> FOR gcs IN
> SELECT n.nspname, c.relname, a.attname
> FROM pg_class c,
> pg_attribute a,
> pg_type t,
> pg_namespace n
> WHERE c.relkind = 'r'
> AND t.typname = 'geometry'
> AND a.attisdropped = false
> AND a.atttypid = t.oid
> AND a.attrelid = c.oid
> AND c.relnamespace = n.oid
>
> LOOP
>
> RAISE INFO 'Processing %.%.%', gcs.nspname, gcs.relname, gcs.attname;
>
> -- Try to find srid check from system tables (pg_constraint)
> gsrid :=
> (SELECT replace(split_part(s.consrc, ' = ', 2), ')', '')
> FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
> WHERE n.nspname = gcs.nspname
> AND c.relname = gcs.relname
> AND a.attname = gcs.attname
> AND a.attrelid = c.oid
> AND s.connamespace = n.oid
> AND s.conrelid = c.oid
> AND a.attnum = ANY (s.conkey)
> AND s.consrc LIKE '%srid(% = %');
> GET DIAGNOSTICS gsrid_cnt = ROW_COUNT;
> IF (gsrid_cnt = 0 OR gsrid IS NULL) THEN
> -- Try to find srid from the geometry itself
> EXECUTE 'SELECT public.srid(' || quote_ident(gcs.attname) || ')
> FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1'
> INTO gc;
> GET DIAGNOSTICS gsrid_cnt = ROW_COUNT;
> gsrid := gc.srid;
>
> -- Try to apply srid check to column
> IF (gsrid IS NOT NULL) THEN
> BEGIN
> EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> ADD CONSTRAINT ' || quote_ident('enforce_srid_' || gcs.attname) || '
> CHECK (srid(' || quote_ident(gcs.attname) || ') = ' || gsrid || ')';
> EXCEPTION
> WHEN check_violation THEN
> RAISE NOTICE 'Could not apply constraint CHECK (srid(%) = %) to %.%', quote_ident(gcs.attname), gsrid, quote_ident(gcs.nspname), quote_ident(gcs.relname);
> END;
> END IF;
> END IF;
>
> -- Try to find ndims check from system tables (pg_constraint)
> gndims :=
> (SELECT replace(split_part(s.consrc, ' = ', 2), ')', '')
> FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
> WHERE n.nspname = gcs.nspname
> AND c.relname = gcs.relname
> AND a.attname = gcs.attname
> AND a.attrelid = c.oid
> AND s.connamespace = n.oid
> AND s.conrelid = c.oid
> AND a.attnum = ANY (s.conkey)
> AND s.consrc LIKE '%ndims(% = %');
> GET DIAGNOSTICS gndims_cnt = ROW_COUNT;
> IF (gndims_cnt = 0 OR gndims IS NULL) THEN
> -- Try to find ndims from the geometry itself
> EXECUTE 'SELECT public.ndims(' || quote_ident(gcs.attname) || ')
> FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1'
> INTO gc;
> GET DIAGNOSTICS gndims_cnt = ROW_COUNT;
> gndims := gc.ndims;
>
> -- Try to apply ndims check to column
> IF (gndims IS NOT NULL) THEN
> BEGIN
> EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> ADD CONSTRAINT ' || quote_ident('enforce_dims_' || gcs.attname) || '
> CHECK (ndims(' || quote_ident(gcs.attname) || ') = '||gndims||')';
> EXCEPTION
> WHEN check_violation THEN
> RAISE NOTICE 'Could not apply constraint CHECK (ndims(%) = %) to %.%', quote_ident(gcs.attname), gndims, quote_ident(gcs.nspname), quote_ident(gcs.relname);
> END;
> END IF;
> END IF;
>
> -- Try to find geotype check from system tables (pg_constraint)
> gtype :=
> (SELECT replace(split_part(s.consrc, '\'', 2), ')', '')
> FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
> WHERE n.nspname = gcs.nspname
> AND c.relname = gcs.relname
> AND a.attname = gcs.attname
> AND a.attrelid = c.oid
> AND s.connamespace = n.oid
> AND s.conrelid = c.oid
> AND a.attnum = ANY (s.conkey)
> AND s.consrc LIKE '%geometrytype(% = %');
> GET DIAGNOSTICS gtype_cnt = ROW_COUNT;
> IF (gtype_cnt = 0 OR gtype IS NULL) THEN
> -- Try to find geotype from the geometry itself
> EXECUTE 'SELECT public.geometrytype(' || quote_ident(gcs.attname) || ')
> FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1'
> INTO gc;
> GET DIAGNOSTICS gtype_cnt = ROW_COUNT;
> gtype := gc.geometrytype;
>
> -- Try to apply geometrytype check to column
> IF (gtype IS NOT NULL) THEN
> BEGIN
> EXECUTE 'ALTER TABLE ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || '
> ADD CONSTRAINT ' || quote_ident('enforce_geotype_' || gcs.attname) || '
> CHECK ((geometrytype(' || quote_ident(gcs.attname) || ') = ' || quote_literal(gtype) || ') OR (' || quote_ident(gcs.attname) || ' IS NULL))';
> EXCEPTION
> WHEN check_violation THEN
> RAISE NOTICE 'Could not apply constraint CHECK ((geometrytype(%) = %) OR (% IS NULL)) to %.%', quote_ident(gcs.attname), quote_literal(gtype), quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname);
> END;
> END IF;
> END IF;
>
> IF (gsrid_cnt = 0 OR gsrid IS NULL) THEN
> RAISE WARNING 'Could not determine the srid of \'%\' in \'%.%\'', gcs.attname, gcs.nspname, gcs.relname;
> ELSIF (gndims_cnt = 0 OR gndims IS NULL) THEN
> RAISE WARNING 'Could not determine the number of dimensions of \'%\' in \'%.%\'', gcs.attname, gcs.nspname, gcs.relname;
> ELSIF (gtype_cnt = 0 OR gtype IS NULL) THEN
> RAISE WARNING 'Could not determine the geometry type of \'%\' in \'%.%\'', gcs.attname, gcs.nspname, gcs.relname;
> ELSE
> INSERT INTO geometry_columns (f_table_catalog,f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type)
> VALUES ('', gcs.nspname, gcs.relname, gcs.attname, gndims, gsrid, gtype);
> inserted := inserted + 1;
> END IF;
>
> END LOOP;
>
> IF oldcount > inserted THEN
> stale = oldcount-inserted;
> ELSE
> stale = 0;
> END IF;
>
> RETURN 'probed:' ||probed|| ' inserted:'||inserted|| ' conflicts:'||probed-inserted|| ' deleted:'||stale;
> END
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
More information about the postgis-devel
mailing list