On Jun 29, 2011, at 10:42 AM, Magnus Hagander wrote: > On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz > <jonathan.katz@xxxxxxxxxxxxxxxx> 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 (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
