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
>  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