On Jun 29, 2011, at 10:42 AM, Magnus Hagander wrote:
> On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz
> <[email protected]> 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
