Re: point types in "DISTINCT" queries

From: "Jonathan S(dot) Katz" <jonathan(dot)katz(at)excoventures(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: point types in "DISTINCT" queries
Date: 2011-06-29 14:38:03
Message-ID: 46484EE6-4AFB-456E-B34C-772C9A07803E@excoventures.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Jun 29, 2011, at 10:25 AM, Magnus Hagander <magnus(at)hagander(dot)net>
wrote:

> On Wed, Jun 29, 2011 at 06:53, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote:
>>
>>> I looked into the mailing list archives and found a potential answer
>>> on this thread:
>>> http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php
>>> However I wanted to see if it was still necessary that I would need
>>> the complete btree operator class to run such a query.
>>
>> Yes, the default btree operator class is used to find the equality
>> operator. Even though you have defined the operator "=", postgresql
>> doesn't rely on that meaning "equals" -- the btree operator class is
>> what imparts that meaning.
>>
>>> Are there plans to have a defined "=" operator on the point type? I
>>> can understand how the other geometric types, "=" would represent
>>> area, but AFAIK I think "=" could be safely applied on a point type
>>> (and i realize I could submit a patch for that :-) maybe depending
>>> on
>>> the resolution to this / refreshing my C...).
>>
>> The built-in geometric types haven't received a lot of attention
>> lately.
>> Most people who use geometric data use the PostGIS extension, which
>> is a
>> sophisticated extension that can deal with that kind of data. You
>> might
>> want to check that out and see if it meets your needs.
>>
>> Perhaps someone is interested in bringing the built-in geometric
>> types
>> up to speed; but I think most of the interest is moving things like
>> this
>> out to extensions where they can be more easily be maintained by
>> interested parties.
>
> Given that they are the only ones supporting knn-gist, I would expect
> them to actually become *more* popular with 9.1 - at least until such
> time as postgis adds support for it...

In fact that is my use-case - I will be performing nearest-neighbor
lookups (and will be running 9.1b2 on this data set shortly).
However, because most of the geospatial work is relatively
straightforward, I didn't want to use PostGIS for this application.
But that might change in the near future depending on the requirements.

But for now tasks like ensuing uniqueness amongst points are slightly
more difficult. My current solution is breaking out the (x,y) coords
into different columns

Jonathan

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2011-06-29 14:42:21 Re: point types in "DISTINCT" queries
Previous Message Sim Zacks 2011-06-29 14:28:37 Re: PLPGSQL SETOF functions