I have a polygon column for which I would like to add a unique constraint that tests for spatial equality akin to ST_Equals so that it ignores vertex order. For instance, I would like these polygons to count as duplicates:

ST_GeomFromText('POLYGONZ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1))')
ST_GeomFromText('POLYGONZ((1 0 1, 1 1 1, 0 1 1, 0 0 1, 1 0 1))')

Is there a standard way to implement this other than a GIST index, which the docs say only indexes the bounding box?

One solution I'm thinking of is to index on a function that returns a sorted array of polygon vertices, but it seems like a hack.

  • I just realized that you are using POLYGONZ; ST_Equals does not account for Z values. Might be why you suggests a vertice array.
    – geozelot
    Mar 7 '19 at 16:20

You can run a CHECK constraint against a custom function.

Create the function:

CREATE FUNCTION equality_constraint_func(
  id INT,
RETURNS boolean AS
      SELECT 1
      FROM   <table> AS a
      WHERE  a.<id> <> id
        AND  a.<geom> && gm
        AND  ST_Equals(a.<geom>, gm)

and add the actual constraint:

  ADD CONSTRAINT equality_constraint
  CHECK (equality_constraint_func(<id>, <geom>))

Inspired by and adapted from this excellent answer, to adress this rather common use case directly.

Read there why e.g. the <id> is passed.

  • I'm accepting this because it's a good answer to the question I asked. Unfortunately, I didn't realize ST_Equals doesn't work with 3D geometry as you pointed out. In light of that, I think the easiest and most performant solution may in fact be a vertex array. Mar 7 '19 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.