Re: Beginner's questions about creating a custom data type in PostgreSQL... - Mailing list pgsql-general

On 7/25/06, Redefined Horizons <redefined.horizons@gmail.com> wrote:
> Merlin,
> I am actually working on a spatial extension for PostgreSQL that is
> similar to PostGIS, but compatible with a geometry library I am
> developing.
>
> I had first thought of using a "table" only desgin, with no custom
> data types, to store the geometry information in the database.
>
> For example, the "many_lines" table might store records representing
> multi-segmented lines. (One geometry made up of smaller connected line
> segments.) A separate table called "single_lines" would contain the
> actual lines. The link between the line segment geometries and the
> "many lines" geometries would be maintained by a one-to-many
> relationship.

ah, that changes things completely.

If you are going the custom route, I would try this approach first and
discard it only when you have determined to to be not fast enough.
the major problems here are going to be inefficient data size and
problems with spatial indexing.  postgis makes use of the gist index
and if you want fast spatial indexing you will need to generate index
ops for any custom type regardless of it's implementation.

this means you have to have a pretty good understanding of how gist
works and how the postgresql type system works.  to be perfectly
honest, if you pull this off and make it work reasonably efficient,
you should be giving me advice, not vice versa :)

have you considered implementing postgis and cutting some c code to do
the type translation on the backend?

> However, I began to realize that as my geometries became more complex
> I would begin to have more and more interdependent relationships. this
> in itself isn't a problem, until you consider that in a typical GIS
> system I might be dealing with thousands or tens of thousands of
> geometries. In the example above, if I wanted to find all of the line
> segment geometries that belonged to a"many line" geometry I would have
> to run a query on the "single_lines" table.
>
> I am concerned about the performance of a system that would frequently
> rely on a cascade of these types of searches.
>
> If I instead implement a custom "many lines" geometry data type I can
> now directly access the line segment geometries.
>
> Do you think this reasoning is sound? Are custom data types the right
> solution? Or am I being overly concerned about the performance issues?

that really depends on your requriements.  you are asking some really
broad questions.  generally though, in GIS environments performance is
extremely relevant and you need to carefully consider your
implementation approach.  the good news is postgresql is the right
place to be.

> If custom data types aren't a good idea in this situation, when are
> they? I can think of almost no situation when I can't mirror the
> functionality of a custom data type with a series of related database
> tables.

I am differentiating between custom and composite types.  composite
types are rarely worthwhile although I do use them sometimes to pass
data in and out of functions.  I suggest reading the composite types
chapter of the documentation and making your own determination.  they
are analgous to anonymous 'C' structs as opposed to typedef'd C
structs which are closer to tables.

custom types OTOH are worthwhile if you are filling a need that is not
served by an existing type.  if you want a good example on
implementing a custom type check out the 'cube' example in contrib and
also 'earthdistance' which shows how the cube might be implemted in
gist scenarios.  you will also get a good feel for the complexity
involved, including but not limited to custom grammars, backend
coding, and deep knowledge of the postgresql type system.  It could
also be a great learning experience.

regards,
merlin