[postgis-users] Table w/existing geometry column (without AddGeometryColumn), SRID?
Stefan Keller
sfkeller at gmail.com
Thu Oct 23 15:09:18 PDT 2008
Thanks a lot, Kevin (and Kristian), for your help regarding
alternatives to the function AddGeometryColumn()!
No, I did'nt knew probe_geometry_columns() before.
What do you think about the remaining questions?
* Does any postgis-function or spatial index fail when there is no
entry in geometry_columns table (except of course where explicit
coordinate transformation is needed)?
* The value -1 is used to indicate no specified SRID. Does it make a
difference, if the SRID default is '0' instead of '-1' ?
* Given all data is stored with SRID set to 0, is it still possible to
use all spatial functions (similar to "SELECT ... USING SRID=-1")?
-- Stefan
2008/10/23 Kevin Neufeld <kneufeld at refractions.net>:
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
More information about the postgis-users
mailing list