[Django]-How to make a geography field unique?

by

1👍

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

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


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

Turns out that the Django PostGIS backend, in this version, uses the “~=” operator to verify if a certain record already exists, but this is not supported by PostGIS on geography types. Not sure why GeoDjango devs didn’t used the “=” operator which is supported by both geography and geometry types.

So the solution I found was to monkey-patch the Django PostGIS backend by adding in the top of the models.py file (maybe there is more elegant ways to do this monkey-patching), but worked fine to 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 suppose that if you still are using older versions of Django and PostGIS on your project you may want to verify if the given operators are supported and how the backend operators are handled in that particular version of Django.

👤AndreLobato

0👍

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

You can enforce a unique geometry using something like:

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

This will create a unique index on the textual representation of the geometry column. There may be limitations on size (I don’t expect that indexes will work well for toasted values) but if you have that many points in your geometry fields then you are going to have issues otherwise with unique constraints.

👤Chris Travers

Leave a comment