Multi-valued user-defined types

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Does anyone have examples of real user-defined types that would need two
> fields? If not it may not be worth spending time on.

Guess I'm jumping in a little late on this, but when reading the threads
linked to from the TODO items on user-defined types and typmod
parameters I immediately thought of PostGIS and the current 'geometry'
type. I've discussed this some with the PostGIS folks and I think they
have a definite real-world use-case for multi-valued user-defined types.
They achieve this at the moment by using a table in the public sceham to
keep the extra information regarding the column since they can't put it
in pg_attribute (which is certainly where it belongs).

The information for each attribute is:

Number of dimensions
Spatial Referencing System (identified by the SRID)
Type (ie: Point, Polygon, etc)

This is pretty standard among GIS databases (indeed, the table they
keep this information in is actually defined by the OpenGIS
specification and includes these attributes). Ideally, this would be a
view rather than a table and the actual information would be stored in
pg_attribute (attypmod). It would also mean that the input/output
functions could ensure only valid information is put into the columns
instead of having to rely on constraints put on the table. The options
for how to handle this, in order of what I believe the preference is:

POINT(dims, srid) - eg: POINT(2,4269)
geometry(dims, srid, type) - eg: geometry(2,4269,'POINT')
POINT_2D(srid) - eg: POINT_2D(4269)
geometry - eg: geometry (constraints, side-table)

SRID is pretty uniformly defined to be an int4 itself, though the
PostGIS folks seemed to think it could be cut to 30-bits (2 bits for
dimension is enough for them) if necessary.

I'd really like to see custom types able to support mutli-values (and to
have numeric changed to whatever the new mechanism is). As for how this
might be handled in the backend, my thinking was to have a function for
parsing the parameters which is passed in a cstring/varchar/etc and then
returns a complex type of some kind. My original thought was to return
a bytea but an anyarray may also work. Another thought which was
mentioned was to add more 'attypmod' columns, ala pg_statistic's
stanumbersN columns.

I did read through the threads linked from the TODO item but wasn't very
clear from those where things stand now. My intention (and
understanding based on the threads) is that the goal would be to have
this in 8.3. I'm interested in helping to realize this goal as I've
become very annoyed at having to deal with this side-table or use
functions to add geometry columns. :)

Thanks,

Stephen

----- Forwarded message from Markus Schaber <schabi(at)logix-tt(dot)com> -----

Date: Wed, 06 Sep 2006 11:48:36 +0200
From: Markus Schaber <schabi(at)logix-tt(dot)com>
To: PostGIS Users Discussion <postgis-users(at)postgis(dot)refractions(dot)net>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Thunderbird 1.5.0.5 (X11/20060812)
Reply-To: PostGIS Users Discussion <postgis-users(at)postgis(dot)refractions(dot)net>
X-Spam-Status: No, score=-2.5 required=5.0 tests=BAYES_00,FORGED_RCVD_HELO
autolearn=ham version=3.1.3
Subject: Re: [postgis-users] What's the Purpose of the Geometry_Columns Table?

Hi, Strk,

strk(at)refractions(dot)net wrote:

>> Agreement with Markus.
>>
>> CREATE TABLE thetable ( thegeom GEOMETRY(4326,2,'POLYGON') );
>
> Also:
>
> CREATE TABLE thetable ( thegeom ST_POLYGON2D(4326) )
>
> (this won't need *multi_valued* typmod)

That's right.

Due to the small number of geometry and dimension combinations, we could
possibly implement them using domains over GEOMETRY, but that's not
feasible for the SRIDs.

I don't know yet how DOMAINs affect the type names transmitted in the
client protocol, so it's possible that the jdbc / j2ee / python geotypes
have to be updated, but that might be the case for multi-valued
typemods, too. And, finally, shielding the applications via abstraction
over such changes is the whole point of those projects.

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

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
_______________________________________________
postgis-users mailing list
postgis-users(at)postgis(dot)refractions(dot)net
http://postgis.refractions.net/mailman/listinfo/postgis-users

----- End forwarded message -----