Table w/existing geometry column (without AddGeometryColumn), SRID?

[Stefan Keller]

Dear all,

When creating a new table with a geometry column, most of the instructions say:

1. create a non-spatial table
2. add a spatial column to the table using the "AddGeometryColumn" function.

Now I've encountered at least three situations in which a table with
all columns - including the geometry column - gets created:
HibernateSpatial, ArcGIS and GeoKettle.

My questions:

* Is there a trick to add metadata to the "geometry_columns" table,
given a table already exists with one geometry column?

* Given, no AddGeometryColumn function has been issued (and all
geodata is kept in the same coord ref sys): Does any postgis-function
or spatial index fail because of this? If yes, which ones?

* 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")?

[Kristian Thy]

Post by Stefan Keller
* 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

[Kevin Neufeld]

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

Post by Kristian Thy

Post by Stefan Keller
* 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

[Kristian Thy]

Post by Kevin Neufeld
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.

Interesting. I searched the docs just now, and only found one reference
to the function - it's mentioned as having received a bugfix, but
nowhere can you see what it does :)

Thanks for the pointer.

Post by Kevin Neufeld
Alternatively, you can use the attached plpgsql that I use all the time.

Saved to the scratchpad, thanks.

\\kristian

[Kevin Neufeld]

Yup, it looks like the docs are missing for that function in 1.3.3 as
well as the current 1.3.4SVN development branch.

However, a simple explanation did manage to get into the new
documentation we are working on for version 1.4.

http://postgis.refractions.net/documentation/manual-svn/Probe_Geometry_Columns.html

I just added the same description to the 1.3.4SVN development branch so
it should make it into 1.3.4 when it's finally released (I think
1.3.4rc2 should come out soon).

Cheers,
Kevin

Post by Kristian Thy

Post by Kevin Neufeld
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.

Interesting. I searched the docs just now, and only found one reference
to the function - it's mentioned as having received a bugfix, but
nowhere can you see what it does :)
Thanks for the pointer.

Post by Kevin Neufeld
Alternatively, you can use the attached plpgsql that I use all the time.

Saved to the scratchpad, thanks.
\\kristian

[Stefan Keller]

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

Post by Kevin Neufeld
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

Post by Kristian Thy

Post by Stefan Keller
* 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
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Kevin Neufeld]

Post by Stefan Keller
* 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)?

As far as I'm aware, there aren't any functions in PostGIS that actually
use the values stores in the geometry_columns table. This includes
coordinate transformation.

ie. SELECT ST_Transform('SRID=3005;POINT(940245 567728)', 4326);
This can be done on the fly ... the point doesn't have to be in a table
that's registered with geometry_columns.

The geometry_columns table really only exists to be compliant with the
OpenGIS Simple Features Specification for SQL so that external programs,
like Mapserver, can use the table instead of hunting through the system
tables to find tables with geometry columns.

Post by Stefan Keller
* The value -1 is used to indicate no specified SRID. Does it make a
difference, if the SRID default is '0' instead of '-1' ?

Nope, but you can't change the SRID default in PostGIS. Right now, it's
hard coded in many functions (ie. the WKT parser) and external programs
(ie. pgsql2shp) to use -1.

SELECT GetSRID('POINT(0 0)'::geometry);
getsrid
---------
-1
(1 row)

However, there's nothing stopping you from setting the SRID = 0 on all
your geometries. Interestingly, I believe the SQL/MM standard now uses
0 as the default instead of -1.

Post by Stefan Keller
* 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

I'm not sure I understand. As long as the SRIDs are the same for
operations on pairs of geometries, PostGIS doesn't care what the SRID is
(-1, 0, 3005, a custom SRID, ...)

Hope this helps,
Cheers,
Kevin.

[Stefan Keller]

Kevin: Many thanks for the explanations.

To summarize, I think there are some "best practices" to be changed as
well as some quick fixes and open issues regarding metadata and SRID.
Let's begin with metadata (public.geometry_columns table):

* Quick fix: Document probe_geometry_columns()

* "Best practice" (to be discussed and probably changed): I think that
the usual/"best" way to define a geometry attribute is *not* through
AddGeometryColumn but to create it with all attributes and possibly
(at most) one attribute of type GEOMETRY).

* The current solution managing SRID information on two places is
suboptimal: Why don't we define a view called 'geometry_columns' to be
compliant with OGC and e.g. MapServer? Stored procedures could be
triggeres if needed.

* If this is not possible, best practice should be to issue
'populate_geometry_columns' as a postprocessing step once after the
table has been created.

* Quick fix: Integrate and document populate_geometry_columns().


Regarding the management of SRIDs:

* You wrote: "There aren't any functions in PostGIS that actually use
the values stored in the geometry_columns table. This includes
coordinate transformation."
=> Does this mean, they use the table/attribute constraint values or
just some mandatory parameters?

* This fact as well as your explanations ("The geometry_columns table
exists to be compliant with the OpenGIS Simple Features Specification
for SQL. This table allows to find tables with geometry columns
quickly so that they don't have to hunt through the system tables.")
could be documented in 'Table geometry_columns()'.

* Decide whether to keep '-1' as default in PostGIS, or to change it
to '0' (or NULL?) as SQL/MM seems to propose.


Finally, when trying to insert geometries I've seen so many situations
where functions throw exceptions because of SRID constraint
violations. The problem could be the WKT parser, we also had problems
using myPoint.setSRID(4326) (from JTS-package
"com.vividsolutions.jts.geom.Point").
=> So, SRID defaults, and SRID (default / value) setting seems to be
still an open issue (in WKT-parser, pgsql2shp, JTS)?

- Stefan

Post by Stefan Keller
* 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)?

As far as I'm aware, there aren't any functions in PostGIS that actually use
the values stores in the geometry_columns table. This includes coordinate
transformation.
ie. SELECT ST_Transform('SRID=3005;POINT(940245 567728)', 4326);
This can be done on the fly ... the point doesn't have to be in a table
that's registered with geometry_columns.
The geometry_columns table really only exists to be compliant with the
OpenGIS Simple Features Specification for SQL so that external programs,
like Mapserver, can use the table instead of hunting through the system
tables to find tables with geometry columns.

Post by Stefan Keller
* The value -1 is used to indicate no specified SRID. Does it make a
difference, if the SRID default is '0' instead of '-1' ?

Nope, but you can't change the SRID default in PostGIS. Right now, it's
hard coded in many functions (ie. the WKT parser) and external programs (ie.
pgsql2shp) to use -1.
SELECT GetSRID('POINT(0 0)'::geometry);
getsrid
---------
-1
(1 row)
However, there's nothing stopping you from setting the SRID = 0 on all your
geometries. Interestingly, I believe the SQL/MM standard now uses 0 as the
default instead of -1.

Post by Stefan Keller
* 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

I'm not sure I understand. As long as the SRIDs are the same for operations
on pairs of geometries, PostGIS doesn't care what the SRID is (-1, 0, 3005,
a custom SRID, ...)
Hope this helps,
Cheers,
Kevin.
_______________________________________________
postgis-users mailing list
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paragon Corporation]

Stefan,

Let me try to address some of your questions/comments:

To summarize, I think there are some "best practices" to be changed as well
as some quick fixes and open issues regarding metadata and SRID.

Post by Stefan Keller
* Quick fix: Document probe_geometry_columns()

Kevin has done this for 1.3.4 - it was already done for 1.4. Actually 1.4
is probably going to be a better documentation source than 1.3.4 since we
are putting all our efforts into that - particularly the new PostGIS
reference section. For the most part all the functions in 1.3.4 are in 1.4
and 1.4 hasn't released any new functions yet to my knowledge that is not in
1.3.4. Even if it did, we have tried to explicitly state the version a
function came into existence.
http://postgis.refractions.net/documentation/manual-svn/ch07.html

Post by Stefan Keller
* "Best practice" (to be discussed and probably changed): I think that the

usual/"best" way to define a geometry attribute is *not* > through
AddGeometryColumn but to create it with all attributes and possibly (at
most) one attribute of type GEOMETRY).

Post by Stefan Keller
The current solution managing SRID information on two places is
suboptimal: Why don't we define a view called 'geometry_columns' to be

compliant with OGC and e.g. MapServer? Stored procedures > could be
triggeres if needed.

AddGeometryColumn currently simply creates a geometry column and puts in
constraints on the table to ensure only goemetries of that type will be put
in that field and then logs it to geometry_columns table. This is needed
beause there is no easy way with older versions of PostgreSQL to stuff in
more attribute info in the geometry type. There are talks for in future to
do this taking advantage of 8.3+ feature to store additional info for custom
data types and then making a view st_geometry_columns against this
information.

Refere to the create tables smarter thread on postgis-dev
http://postgis.refractions.net/pipermail/postgis-devel/2008-June/thread.html
#3080

Post by Stefan Keller
If this is not possible, best practice should be to issue

'populate_geometry_columns' as a postprocessing step once after the table >
has been created.

Post by Stefan Keller
Quick fix: Integrate and document populate_geometry_columns().

I like this idea.

Post by Stefan Keller
* You wrote: "There aren't any functions in PostGIS that actually use the

values stored in the geometry_columns table.

Post by Stefan Keller
This includes coordinate transformation."
Does this mean, they use the table/attribute constraint values or just

some mandatory parameters?

No. Each geometry actually has metadata that denotes its SRID and type so
it just looks at the raw geometry. Conceivably you can have a table with a
mixed bag of geometry types. I actually have that in one of my projects
(well I segregate them by having them in an inheritance hierarchy and I
think others do similar things.

Post by Stefan Keller
Decide whether to keep '-1' as default in PostGIS, or to change it to '0'

(or NULL?) as SQL/MM seems to propose.
Paul really really wants to change to 0. The question is how much code will
that break?

Hope that helps,
Regina