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

[Obe, Regina]

Paul,

You can still turn it into a read-only view and I'm beginning to like
that idea a lot and actually would be better for people who use views.

As I mentioned - the system tables are well aware of the fields in a
view and the way it works as far as I can tell.

1) If you have a field coming directly from a table - the data type,
typemod of the column in the view is the same as the field in the table.
So if you have a geometry in a table and you just dump it out in the
view this works quite nicely actually.

2) The other case is as (was it Marcus?) mentioned where you have
something like ST_Intersection .. ST_Union, ST_Collect where the
behavior is not clear what it should return.

In this case I presume our geometry typemod approach would follow suit
with how varchar, text etc work.  E.g. in these cases I simply do a
CAST. As the example I mentioned

SELECT CAST(ST_Intersection(a.the_geom, b.the_gheom) As
geometry(polygon,....)) As newgeom

Again the system tables are smart enough to take a cast and respect it.
If you don't have a CAST then it just becomes a geometry with no
constraints similar to when you do something like
glue two varchars/text together without recasting.

So it seems like all this stuff would automatically be taken care of for
us by the existing PostgreSQL system and all we simply need to do is
create a geometry_columns view against the system tables.  Wow even I
almost feel like I can do this.

On a side note - I recall reading somewhere that the new name of
Geometry_columns should be

ST_Geometry_Columns

Documented here for one -
http://jtc1sc32.org/doc/N1101-1150/32N1107-WD13249-3--spatial.pdf

e.g. I see IBM seems to have adopted the new name
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/
com.ibm.db29.doc.spatial/rsbp4017.htm

Are we going to implement that too? Seems like we should if we are going
to go thru all this trouble.

Thanks,
Regina

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Friday, June 06, 2008 1:59 AM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] Re: [postgis-users] create tables smarter

Well, as Regina noted, having views in GEOMETRY_COLUMNS is required to
make them visible in some applications.

On Thu, Jun 5, 2008 at 8:27 AM, Tom Lane <tgl at sss.pgh.pa.us> wrote:
> "Paul Ramsey" <pramsey at cleverelephant.ca> writes:
>> 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.
>
> No, you don't want to go there.  If you try to implement it like that,
> then you'll get "tuple updated concurrently" failures because
concurrent
> ANALYZEs on different tables will try to clean up the same rows.
>
> If you go with the typmod implementation, might it be possible to
> turn GEOMETRY_COLUMNS into a read-only view on the system catalogs?
> Then it wouldn't need any clean-up.  It's not clear to me whether
> GEOMETRY_COLUMNS is supposed to carry any information that wouldn't
> be available from the typmod tags.
>
>                        regards, tom lane
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
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.