Re: [postgis-users] create tables smarter

classic Classic list List threaded Threaded
48 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Paul Ramsey
On Wed, Jun 4, 2008 at 8:34 AM, Frank Warmerdam <[hidden email]> wrote:

> I did a presentation at PGCon and the issue of AddGeometryColumn() being
> necessary to populate the geometry_columns table came up.  Some of the
> postgres techies suggested there has been work so that extension defined
> types could actually take extra arguments when used in the CREATE TABLE
> statement and that this might let us avoid the need to use
> AddGeometryColumn().
>
> ie.
>
> CREATE TABLE ROADS
>  (id   INT,
>   name VARCHAR(255),
>   geom GEOMETRY(<dimension>,<srid>,<type>) )

Really truly? This is something we've been asking for, for some time,
but I wasn't really holding my breath. Time to go code spelunking and
see if it's there.

P

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
Hi, Paul,

"Paul Ramsey" <[hidden email]> wrote:

> On Wed, Jun 4, 2008 at 8:34 AM, Frank Warmerdam <[hidden email]> wrote:
> > I did a presentation at PGCon and the issue of AddGeometryColumn() being
> > necessary to populate the geometry_columns table came up.  Some of the
> > postgres techies suggested there has been work so that extension defined
> > types could actually take extra arguments when used in the CREATE TABLE
> > statement and that this might let us avoid the need to use
> > AddGeometryColumn().
> >
> > ie.
> >
> > CREATE TABLE ROADS
> >  (id   INT,
> >   name VARCHAR(255),
> >   geom GEOMETRY(<dimension>,<srid>,<type>) )
>
> Really truly? This is something we've been asking for, for some time,
> but I wasn't really holding my breath. Time to go code spelunking and
> see if it's there.

At least the Developer Dokumentation of PostgreSQL 8.4 mentions it:

http://developer.postgresql.org/pgdocs/postgres/sql-createtype.html

Search for "typmod".

I did not yet look whether older versions like 8.3 provide it, or 8.4
is the first.


Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Paul Ramsey
Very cool. Interesting limitation though:

"The optional type_modifier_input_function and
type_modifier_output_function are needed if the type supports
modifiers, that is optional constraints attached to a type
declaration, such as char(5) or numeric(30,2). PostgreSQL allows
user-defined types to take one or more simple constants or identifiers
as modifiers. However, this information must be capable of being
packed into a single non-negative integer value for storage in the
system catalogs"

Could make support for srid values tricky, to say the least...
currently it looks like we can do it, but it would requires some
constraining of spatial_ref_sys.

test=# select max(srid) from spatial_ref_sys;
  max
-------
 32766
(1 row)


Fun!!!!

P

On Wed, Jun 4, 2008 at 8:57 AM, Markus Schaber <[hidden email]> wrote:

> Hi, Paul,
>
> "Paul Ramsey" <[hidden email]> wrote:
>
>> On Wed, Jun 4, 2008 at 8:34 AM, Frank Warmerdam <[hidden email]> wrote:
>> > I did a presentation at PGCon and the issue of AddGeometryColumn() being
>> > necessary to populate the geometry_columns table came up.  Some of the
>> > postgres techies suggested there has been work so that extension defined
>> > types could actually take extra arguments when used in the CREATE TABLE
>> > statement and that this might let us avoid the need to use
>> > AddGeometryColumn().
>> >
>> > ie.
>> >
>> > CREATE TABLE ROADS
>> >  (id   INT,
>> >   name VARCHAR(255),
>> >   geom GEOMETRY(<dimension>,<srid>,<type>) )
>>
>> Really truly? This is something we've been asking for, for some time,
>> but I wasn't really holding my breath. Time to go code spelunking and
>> see if it's there.
>
> At least the Developer Dokumentation of PostgreSQL 8.4 mentions it:
>
> http://developer.postgresql.org/pgdocs/postgres/sql-createtype.html
>
> Search for "typmod".
>
> I did not yet look whether older versions like 8.3 provide it, or 8.4
> is the first.
>
>
> Regards,
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in Europe! www.ffii.org
> www.nosoftwarepatents.org
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Tom Lane
"Paul Ramsey" <[hidden email]> writes:
> Very cool. Interesting limitation though:
> ... However, this information must be capable of being
> packed into a single non-negative integer value for storage in the
> system catalogs"

> Could make support for srid values tricky, to say the least...
> currently it looks like we can do it, but it would requires some
> constraining of spatial_ref_sys.

The argument that carried the day for implementing it like that was
that the stored typmod could just be an index into a table, lookup
key in a catalog, etc.  So in principle you can associate as much
data as you want with any column.

IIUC, setting up such a catalog entry is pretty much what
AddGeometryColumn does already --- so in some sense, the user-defined
typmod stuff would just be pretty syntactic sugar for a call to
AddGeometryColumn.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Stephen Frost
* Tom Lane ([hidden email]) wrote:
> "Paul Ramsey" <[hidden email]> writes:
> > Could make support for srid values tricky, to say the least...
> > currently it looks like we can do it, but it would requires some
> > constraining of spatial_ref_sys.

Right, I brought up that issue when the feature was being discussed on
hackers.  At around the same time I asked what the max SRID allowed was
on the PostGIS lists, but I don't recall getting an answer.  (also, it
was me at PGCon, hi Frank!)

> The argument that carried the day for implementing it like that was
> that the stored typmod could just be an index into a table, lookup
> key in a catalog, etc.  So in principle you can associate as much
> data as you want with any column.

This is true, but having a seperate table to handle it, where the values
stored in the system catalog are essentially meaningless across
installs, is kind of ugly.  I'm much more in favor of encoding the data
into the integer.  For some reason, I had thought it was a 2-byte
integer but the documentation appears to claim it's 'integer', which is
a full 4-bytes.

My suggestion, assuming 4 bytes, would be:

Negative: 1 bit
COORD_DIMENSION: 2 bits
TYPE: 6 bits
SRID: 23 bits

That allows for 4 coordinate dimensions (only 3 currently exist), 64
types (only 15? or so now), and over 8M SRIDs.  If we think that SRIDs
won't go over 1M, we could give it only 20 bits and keep the other 3
bits for 'future expansion'.

I don't see this as being particularly difficult to implement...  If
there's agreement on this approach then I'd be happy to write the C code
necessary.  Is there a preference on the structure of the typmod values,
or is Frank's suggestion of:

GEOMETRY(<dimension>,<srid>,<type>)

acceptable?  The other question that crops up in my mind is, what do we
want to do about the current situation where data will be dumped out
with just 'geometry' as the column?

        Thanks!

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Tom Lane
Stephen Frost <[hidden email]> writes:
> This is true, but having a seperate table to handle it, where the values
> stored in the system catalog are essentially meaningless across
> installs, is kind of ugly.  I'm much more in favor of encoding the data
> into the integer.

If you can make it fit, that's certainly the easiest way to do it.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Kevin Neufeld
In reply to this post by Stephen Frost

Stephen Frost wrote:

> My suggestion, assuming 4 bytes, would be:
>
> Negative: 1 bit
> COORD_DIMENSION: 2 bits
> TYPE: 6 bits
> SRID: 23 bits
>
> That allows for 4 coordinate dimensions (only 3 currently exist), 64
> types (only 15? or so now), and over 8M SRIDs.  If we think that SRIDs
> won't go over 1M, we could give it only 20 bits and keep the other 3
> bits for 'future expansion'.
>
>  
I think there are actually 4 dimensions now. X,Y,Z,and M.  There once
was talk of working towards having N dimensions.  Do we still want to go
there?  In which case, I would recommend that, for now, we use 4-5 bits
for COORD_DIMENSION and 20-21 for SRID.

-- Kevin

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
In reply to this post by Stephen Frost
Hi, Stephen,

Stephen Frost <[hidden email]> wrote:

> This is true, but having a seperate table to handle it, where the values
> stored in the system catalog are essentially meaningless across
> installs, is kind of ugly.  I'm much more in favor of encoding the data
> into the integer.

I fully agree here, if only for plain speed reasons.

> Negative: 1 bit
> COORD_DIMENSION: 2 bits
> TYPE: 6 bits
> SRID: 23 bits
>
> That allows for 4 coordinate dimensions (only 3 currently exist), 64
> types (only 15? or so now), and over 8M SRIDs.

For the SRID, we can also add a primary key to the spatial_ref_sys
table which is enumerated tightly, without gaps, and encode this one
into the int. But I'd prefer having the SRID there directly.

> If we think that SRIDs
> won't go over 1M, we could give it only 20 bits and keep the other 3
> bits for 'future expansion'.

We can also use the negative bit, AFAICS.

> I don't see this as being particularly difficult to implement...  If
> there's agreement on this approach then I'd be happy to write the C code
> necessary.  Is there a preference on the structure of the typmod values,
> or is Frank's suggestion of:
>
> GEOMETRY(<dimension>,<srid>,<type>)
>
> acceptable?

I think this is acceptable, feel free to go ahead and present
patches. :-)

> The other question that crops up in my mind is, what do we
> want to do about the current situation where data will be dumped out
> with just 'geometry' as the column?

For type, please use an ENUM that also contains "geometry" which allows
arbitrary geometries to be inserted, being the default. The same for
SRID and Dimension, add default values which just allow mixed SRID /
Dimension as plain "geometry" type does now.

I would not like to give up the possibility of "unspecified" types, for
backwards compatibility, and for flexibility in projects which are
not-so-strictly OpenGIS-compliant (like some we have inhouse).

Mixing geometry types is especially useful when using intersection()
and similar, because it can deliver anything.

Maybe we can also use NULL for default values.


Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
In reply to this post by Kevin Neufeld
Hi, Kevin,

Kevin Neufeld <[hidden email]> wrote:

> I think there are actually 4 dimensions now. X,Y,Z,and M.  There once
> was talk of working towards having N dimensions.  Do we still want to go
> there?  In which case, I would recommend that, for now, we use 4-5 bits
> for COORD_DIMENSION and 20-21 for SRID.

I don't know the exact use cases, but for me personally, even M is a
misuse of the model - Measures belong into their own row in the table,
not mangled into a coordinate.

Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Stephen Frost
In reply to this post by Kevin Neufeld
Kevin,

* Kevin Neufeld ([hidden email]) wrote:
> I think there are actually 4 dimensions now. X,Y,Z,and M.  There once  
> was talk of working towards having N dimensions.  Do we still want to go  
> there?  In which case, I would recommend that, for now, we use 4-5 bits  
> for COORD_DIMENSION and 20-21 for SRID.

There are only 3 *options* currently, that being 2, 3, and 4.  I don't
believe we have 1-dimension support, or would ever want it, and it seems
pretty unlikely we'd have 5 but we have support to go there if we really
want to...

        Thanks,

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Stephen Frost
In reply to this post by Markus Schaber
Hi Markus,

* Markus Schaber ([hidden email]) wrote:
> Stephen Frost <[hidden email]> wrote:
> > That allows for 4 coordinate dimensions (only 3 currently exist), 64
> > types (only 15? or so now), and over 8M SRIDs.
>
> For the SRID, we can also add a primary key to the spatial_ref_sys
> table which is enumerated tightly, without gaps, and encode this one
> into the int. But I'd prefer having the SRID there directly.

Right, having the SRID directly encoded is best.  Where do we think the
limit on SRIDs is though?  1M?  8M?  Higher?  That's really what drives
this issue..

> > If we think that SRIDs
> > won't go over 1M, we could give it only 20 bits and keep the other 3
> > bits for 'future expansion'.
>
> We can also use the negative bit, AFAICS.

The documentation indicates that the value must be non-negative
(negative meaning an error, I expect).

> I think this is acceptable, feel free to go ahead and present
> patches. :-)

Sounds good. :)

> I would not like to give up the possibility of "unspecified" types, for
> backwards compatibility, and for flexibility in projects which are
> not-so-strictly OpenGIS-compliant (like some we have inhouse).

Right, we would certainly want to support this, the issue I was thinking
about was that we'll have data from prior PostGIS setups that *do* have
specified types and SRIDs and whatnot, and we'll be loading them
*without* that into the new system.  We need to figure out a good way to
handle that, perhaps some script or something which goes through and
fixes it after the data is loaded.

        Thanks,

                Stephen

signature.asc (189 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
In reply to this post by Stephen Frost
Hi, Stephen,

Stephen Frost <[hidden email]> wrote:

> * Kevin Neufeld ([hidden email]) wrote:
> > I think there are actually 4 dimensions now. X,Y,Z,and M.  There once  
> > was talk of working towards having N dimensions.  Do we still want to go  
> > there?  In which case, I would recommend that, for now, we use 4-5 bits  
> > for COORD_DIMENSION and 20-21 for SRID.
>
> There are only 3 *options* currently, that being 2, 3, and 4.  I don't
> believe we have 1-dimension support, or would ever want it, and it seems
> pretty unlikely we'd have 5 but we have support to go there if we really
> want to...

The 4th option, actually, is "don't care", and I don't want to lose
that one.



Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
In reply to this post by Stephen Frost
Hi, Stephen,

Stephen Frost <[hidden email]> wrote:

> > We can also use the negative bit, AFAICS.
>
> The documentation indicates that the value must be non-negative
> (negative meaning an error, I expect).

Yes, you're right here.




Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Mark Cave-Ayland-4
In reply to this post by Markus Schaber
Markus Schaber wrote:

>> I don't see this as being particularly difficult to implement...  If
>> there's agreement on this approach then I'd be happy to write the C code
>> necessary.  Is there a preference on the structure of the typmod values,
>> or is Frank's suggestion of:
>>
>> GEOMETRY(<dimension>,<srid>,<type>)
>>
>> acceptable?
>
> I think this is acceptable, feel free to go ahead and present
> patches. :-)


Hi Markus,

Welcome back from holiday! ;)

I must admit that I find GEOMETRY(<dimension>,<srid>,<type>) fairly
unintuitive; I'd prefer GEOMETRY(<type>,<dimension>,<srid>) as an
alternative. Also, when we determine the maximum size of the SRID field,
we can easily add a CHECK constraint on the table to return an ERROR if
someone tries to go above the limit.

Given the massive re-working of the codebase (see
http://postgis.refractions.net/pipermail/postgis-devel/2008-May/003010.html 
and
http://postgis.refractions.net/pipermail/postgis-users/2008-May/019886.html 
for a catch-up) I'm happy to work on this in SVN trunk when we decide on
the final layout. The hardest bit will be trying to keep the maintenance
of both the new code for 8.3+ and the existing code relatively sane.


ATB,

Mark.

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

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Paul Ramsey
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.

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 wonder if the utility process running inside ANALYZE might not be
the more helpful solution in the end.

P.


On Thu, Jun 5, 2008 at 5:53 AM, Mark Cave-Ayland
<[hidden email]> wrote:

> Given the massive re-working of the codebase (see
> http://postgis.refractions.net/pipermail/postgis-devel/2008-May/003010.html
> and
> http://postgis.refractions.net/pipermail/postgis-users/2008-May/019886.html
> for a catch-up) I'm happy to work on this in SVN trunk when we decide on the
> final layout. The hardest bit will be trying to keep the maintenance of both
> the new code for 8.3+ and the existing code relatively sane.

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Obe, Regina     DND\MIS-3
Let us not forget as rare as the use case is of having mixed srids in a
table I can think of some.  What is the proposition for dealing with
those?

Will we have one that takes no srid and or will we just not allow that
anymore?
The datatype declaration can exclude the srid parameter which would
allow for any kind of geometry?

I also recall having this same discussion before 8.3 came out, although
it was less clear how the added parameters to datatype would help us
although I recall Paul saying "cool" or something like that so he really
must be asleep or I must be dreaming :).

The only problem with analyze is I fail to see how this helps with
views.  The main PITA I have with the geometry_columns thing is that I
have to manually register my views or as Kevin says - write some hack to
query the system tables to derive the SRID of my views.


Regarding this
>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.

If we go with the extra param thing - seems setsrid just should not be
legal.  You would accomplish the same trick with an

ALTER TABLE  column... USING (ST_SETSRID(...))

just like when you convert from one data type to another.  

Just my two cents,
Regina

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Paul
Ramsey
Sent: Thursday, June 05, 2008 9:13 AM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] Re: [postgis-users] create tables smarter

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.

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 wonder if the utility process running inside ANALYZE might not be
the more helpful solution in the end.

P.


On Thu, Jun 5, 2008 at 5:53 AM, Mark Cave-Ayland
<[hidden email]> wrote:

> Given the massive re-working of the codebase (see
>
http://postgis.refractions.net/pipermail/postgis-devel/2008-May/003010.h
tml
> and
>
http://postgis.refractions.net/pipermail/postgis-users/2008-May/019886.h
tml
> for a catch-up) I'm happy to work on this in SVN trunk when we decide
on the
> final layout. The hardest bit will be trying to keep the maintenance
of both
> the new code for 8.3+ and the existing code relatively sane.
_______________________________________________
postgis-devel mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-devel
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Paul Ramsey
On Thu, Jun 5, 2008 at 6:32 AM, Obe, Regina <[hidden email]> wrote:
> The only problem with analyze is I fail to see how this helps with
> views.  The main PITA I have with the geometry_columns thing is that I
> have to manually register my views or as Kevin says - write some hack to
> query the system tables to derive the SRID of my views.

I'd propose the utility process by basically a side effect of ANALYZE
and clean up everything about GEOMETRY_COLUMNS. Remove dead entries,
find new ones, etc, rather than just restricting itself to the one
table it was (potentially) called on.

P.

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Mark Cave-Ayland-4
In reply to this post by Paul Ramsey
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

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Markus Schaber
Hi, Mark,

Mark Cave-Ayland <[hidden email]> wrote:

> 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).

But when you do operations like string concatentation, or intersections
etc, the result type may require different typemods.



Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Reply | Threaded
Open this post in threaded view
|

Re: [postgis-users] create tables smarter

Obe, Regina     DND\MIS-3

>>Hi, Mark,

>>Mark Cave-Ayland <[hidden email]> wrote:

>> 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).

>>But when you do operations like string concatentation, or
intersections
>>etc, the result type may require different typemods.


Which brings back to the point that you still need to support the
untypemod version.  Is that allowed similar to like how we have?

varchar (which is really unlimited)   vs.   varchar(255)

and when you concatenate you get -> text

And also a construct such as

SELECT CAST(ST_Intersection(a.the_geom, b.the_geom) As
Geometry('POLYGON',....))

which would throw an error if the intersection does not return the right
cast type you note.

Thanks,
Regina


-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


123