Re: point types in "DISTINCT" queries

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

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

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