[postgis-devel] Re: [postgis-users] create tables smarter

[Mark Cave-Ayland]

Paul Ramsey wrote:
> We should think pretty hard before doing this. As Tom noted, this is
> not much different than just moving AddGeometryColumns into the CREATE
> TABLE statement, in terms of information required. We also have the
> problem of what to DO with geometry that is defined WITHOUT these
> options. If I don't miss by guess, geometry created with a CREATE
> TABLE AS SELECT... will be in this category, for example.

I think this part will be okay. At least, if you do a CREATE TABLE AS 
SELECT on a table with a field of varchar(255) then your new table 
should also contain a field of varchar(255).

> What happens when someone runs a UPDATE table SET
> geom=setsrid(geom,400) ? Will that be (a) allowed and (b) reflected in
> the metadata after it is done? If not then the usefulness of the
> change begins to fall off precipitately.

I think the way to think about this is to look again at the varchar() 
example. In the case of a varchar(255) field, then the typmod could be 
considered to be a column constraint involving string length. So this 
would suggest that what we would need to do is alter the CHECK 
constraint on a geometry table to compare the table typmod with each 
geometry and throw an ERROR on mismatch.

Carrying on with this idea, the theory would then go that you should be 
able to do your above update with:

ALTER TABLE foo ALTER COLUMN the_geom TYPE geometry(polygon, 2, 400)

...which would then go through and re-build the column in the same way 
as the update would (i.e. forcibly set the values within each geometry). 
  So the theory goes that if we can create a CAST between geometry and 
geometry then we should be good. Tom, is this something that is actually 
supported/possible?

> I wonder if the utility process running inside ANALYZE might not be
> the more helpful solution in the end.

Perhaps, although it seems that we are getting quite close and hence 
worth exploring that little bit further...


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063