How to make a geography field unique?

Question

I have this model:

class Marker(models.Model): location = models.PointField(geography=True, unique=True) 

When I try to add a Marker instance through the admin interface, it raises a ValueError :

 File "django/core/handlers/base.py" in get_response 111. response = callback(request, *callback_args, **callback_kwargs) File "django/contrib/admin/options.py" in wrapper 366. return self.admin_site.admin_view(view)(*args, **kwargs) File "django/utils/decorators.py" in _wrapped_view 91. response = view_func(request, *args, **kwargs) File "django/views/decorators/cache.py" in _wrapped_view_func 89. response = view_func(request, *args, **kwargs) File "django/contrib/admin/sites.py" in inner 196. return view(request, *args, **kwargs) File "django/utils/decorators.py" in _wrapper 25. return bound_func(*args, **kwargs) File "django/utils/decorators.py" in _wrapped_view 91. response = view_func(request, *args, **kwargs) File "django/utils/decorators.py" in bound_func 21. return func(self, *args2, **kwargs2) File "django/db/transaction.py" in inner 209. return func(*args, **kwargs) File "django/contrib/admin/options.py" in add_view 937. if form.is_valid(): File "django/forms/forms.py" in is_valid 124. return self.is_bound and not bool(self.errors) File "django/forms/forms.py" in _get_errors 115. self.full_clean() File "django/forms/forms.py" in full_clean 272. self._post_clean() File "django/forms/models.py" in _post_clean 338. self.validate_unique() File "django/forms/models.py" in validate_unique 347. self.instance.validate_unique(exclude=exclude) File "django/db/models/base.py" in validate_unique 633. errors = self._perform_unique_checks(unique_checks) File "django/db/models/base.py" in _perform_unique_checks 724. if qs.exists(): File "django/db/models/query.py" in exists 565. return self.query.has_results(using=self.db) File "django/db/models/sql/query.py" in has_results 441. return bool(compiler.execute_sql(SINGLE)) File "django/db/models/sql/compiler.py" in execute_sql 808. sql, params = self.as_sql() File "django/db/models/sql/compiler.py" in as_sql 82. where, w_params = self.query.where.as_sql(qn=qn, connection=self.connection) File "django/db/models/sql/where.py" in as_sql 91. sql, params = child.as_sql(qn=qn, connection=connection) File "django/db/models/sql/where.py" in as_sql 94. sql, params = self.make_atom(child, qn, connection) File "django/contrib/gis/db/models/sql/where.py" in make_atom 47. spatial_sql = connection.ops.spatial_lookup_sql(data, lookup_type, params_or_value, lvalue.field, qn) File "django/contrib/gis/db/backends/postgis/operations.py" in spatial_lookup_sql 497. '"%s" lookup.' % lookup_type) Exception Type: ValueError at /admin/coremap/marker/add/ Exception Value: PostGIS geography does not support the "exact" lookup. 

According to this , the geography type does not really have a search on the exact field. Is there any way to fulfill the unique constraint without using exact ?

I use:

• Postgresql 9.1
• PostGIS 1.5
• Django 1.4.3

Answer 1

I ran into a similar problem, but with a newer version of Django (1.10.5), PostGIS (2.0) and Postgres (9.4) (question, question 4 years from this answer)

The error that Django introduced to me was a bit different, but related:

ValueError: PostGIS geography does not support the "~=" function/operator.

It turns out that the Django PostGIS backend in this version uses the "~ =" operator to check if any particular record exists, but this is not supported by PostGIS by geography type. I don’t know why the GeoDjango developers did not use the "=" operator, which is supported by both geography and geometry.

So, the solution I found was to disarm the backend of the Django PostGIS server by adding models.py to the top of the file (maybe there are more elegant ways to do this monkey fix), but it worked fine for me ...

 from django.contrib.gis.db.backends.postgis.operations import (PostGISOperator, PostGISOperations, BILATERAL) PostGISOperations.gis_operators['exact'] = PostGISOperator(op='=', geography=True, raster=BILATERAL) PostGISOperations.gis_operators['same_as'] = PostGISOperator(op='=', geography=True, raster=BILATERAL) 

I suggest that if you are still using older versions of Django and PostGIS in your project, you can check if these statements are supported and how backend statements are handled in this particular version of Django.

Answer 2

I don’t know if you can do this directly in Django or you will need to add an index after the fact, but PostgreSQL supports unique functional indexes.

You can apply unique geometry using something like:

 CREATE UNIQUE INDEX tbl_geometry_idx_u ON mytable(geometry_to_text(mygeom)); 

This will create a unique index in the textual representation of the geometry column. There may be size restrictions (I do not expect indexes to work well for toasted values), but if you have many points in your geometries, then you will have problems with unique restrictions.