Re: point types in "DISTINCT" queries - Mailing list pgsql-general

On Jun 29, 2011, at 10:42 AM, Magnus Hagander wrote:

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

Did a quick experiment:

Using =~

    ALTER TABLE a ADD EXCLUDE USING gist (geocode WITH ~=);

Results:

    ERROR:  could not create exclusion constraint "a_geocode_excl"
    DETAIL:  Key (geocode)=((33.8367126,-117.9164627)) conflicts with key (geocode)=((33.8367128,-117.9164627)).

Which means it *should* work, but first I would need to clean up the data and find the duplicates.  I was hoping this
mightwork: 

    SELECT geocode, count(*)
    FROM a
    GROUP BY a.geocode
    HAVING count(*) > 1;

But:

    ERROR:  could not identify an equality operator for type point

So I would have to just find the points one-by-one until the exclusion constraint passes.

Now, using the custom = operator:

    ALTER TABLE app_address ADD EXCLUDE USING gist (geocode WITH =);

Results:

    ERROR:  operator =(point,point) is not a member of operator family "point_ops"
    DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

Jonathan