[postgis-devel] probe_geometry_columns
strk at refractions.net
strk at refractions.net
Tue Apr 11 11:08:52 PDT 2006
The fix_geometry_columns was a quick hack.
I'm sure this is better.
My only concern is compatibility back to PostgreSQL 7.2.
Is there any volunteer for back-porting ?
--strk;
On Tue, Apr 11, 2006 at 11:02:07AM -0700, Chris Hodgson wrote:
> 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
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
--
/"\ ASCII Ribbon Campaign
\ / Respect for low technology.
X Keep e-mail messages readable by any computer system.
/ \ Keep it ASCII.
More information about the postgis-devel
mailing list