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

[Markus Schaber]

Hi, Stephen,

Stephen Frost <sfrost at snowman.net> 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