Re: point types in "DISTINCT" queries

On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz
<jonathan(dot)katz(at)excoventures(dot)com> wrote:
> 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

Have you tried using an exclusion constraint? Not entirely sure, but I
think that might work.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/