Did you try probe_geometry_columns()?
It scans the system tables looking for the constraints usually assigned
to a geometry column and inserts the discovery into the
public.geometry_columns table.
Alternatively, you can use the attached plpgsql that I use all the time.
Using the provided table oid, it tries to determine the srid, dimension,
and geometry type of the geometry columns in the table, adds the
constraints to the table the column belongs in, and further populates
public.geometry_columns accordingly. This is useful if the constraints
don't already exists on a spatial column.
SELECT populate_geometry_columns('public.mytable'::regclass);
Cheers,
Kevin
Kristian Thy wrote:
On Thu, Oct 23, Stefan Keller wrote:
* Is there a trick to add metadata to the "geometry_columns" table,
given a table already exists with one geometry column?
It is trivial to insert it manually, but I would like to add my vote for
a RegisterGeometryColumn(...) function for easing the process.
\\kristian
-- Function: public.populate_geometry_columns(oid)
-- DROP FUNCTION public.populate_geometry_columns(oid);
CREATE OR REPLACE FUNCTION public.populate_geometry_columns(tbl_oid oid)
RETURNS text AS
$BODY$
DECLARE
gcs RECORD;
gc RECORD;
gsrid integer;
gndims integer;
gtype text;
query text;
gc_is_valid boolean;
BEGIN
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
AND n.nspname NOT ILIKE 'pg_temp%'
AND c.oid = tbl_oid
LOOP
RAISE DEBUG 'Processing %.%.%', gcs.nspname, gcs.relname, gcs.attname;
DELETE FROM geometry_columns
WHERE f_table_schema = quote_ident(gcs.nspname)
AND f_table_name = quote_ident(gcs.relname)
AND f_geometry_column = quote_ident(gcs.attname);
gc_is_valid := true;
-- 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(% = %');
IF (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;
gsrid := gc.srid;
-- Try to apply srid check to column
IF (gsrid IS NOT NULL) THEN
BEGIN
EXECUTE 'ALTER TABLE ONLY ' || 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 WARNING 'Not inserting \'%\' in \'%.%\' into
geometry_columns: could not apply constraint CHECK (srid(%) = %)',
quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname),
quote_ident(gcs.attname), gsrid;
gc_is_valid := false;
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(% = %');
IF (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;
gndims := gc.ndims;
-- Try to apply ndims check to column
IF (gndims IS NOT NULL) THEN
BEGIN
EXECUTE 'ALTER TABLE ONLY ' || 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 WARNING 'Not inserting \'%\' in \'%.%\' into
geometry_columns: could not apply constraint CHECK (ndims(%) = %)',
quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname),
quote_ident(gcs.attname), gndims;
gc_is_valid := false;
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(% = %');
IF (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;
gtype := gc.geometrytype;
IF (gtype IS NULL) THEN
gtype := 'GEOMETRY';
END IF;
-- Try to apply geometrytype check to column
BEGIN
EXECUTE 'ALTER TABLE ONLY ' || 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
-- No geometry check can be applied. This column contains a
number of geometry types.
RAISE WARNING 'Could not add geometry type check (%) to
table column: %.%.%', gtype,
quote_ident(gcs.nspname),quote_ident(gcs.relname),quote_ident(gcs.attname);
END;
END IF;
IF (gsrid IS NULL) THEN
RAISE WARNING 'Not inserting \'%\' in \'%.%\' into
geometry_columns: could not determine the srid', quote_ident(gcs.attname),
quote_ident(gcs.nspname), quote_ident(gcs.relname);
ELSIF (gndims IS NULL) THEN
RAISE WARNING 'Not inserting \'%\' in \'%.%\' into
geometry_columns: could not determine the number of dimensions',
quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname);
ELSE
-- Only insert into geometry_columns if table constraints could be
applied.
IF (gc_is_valid) THEN
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);
END IF;
END IF;
END LOOP;
RETURN 'done';
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
