TransWikia.com

Unique constraint on Geometry to resolve INSERT conflicts

Geographic Information Systems Asked by Chintan Pathak on December 5, 2020

I have a simple table with a geometry column like so:

CREATE TABLE geomtable 
(
  gid serial, 
  geom geometry, 
  trip_count integer
);

I want a unique constraint on the geometry column, such that if the same geometry is being inserted again then, the ON CONFLICT clause should add the trip_count to the existing row.

So my insert query looks something like this:

INSERT INTO geomtable (geom, trip_count) values (<geom>, 123) 
ON CONFLICT ON CONSTRAINT <equality_constraint>
DO UPDATE SET trip_count = geomtable.trip_count + EXCLUDED.trip_count;

I tried the approach to enforcing equality constraint as described here like so:

CREATE FUNCTION equality_constraint_func(
  id INT,
  gm GEOMETRY
)
RETURNS boolean AS
  $$
    SELECT NOT EXISTS (
      SELECT 1
      FROM   geomtable AS a
      WHERE  a.gid <> id
        AND  a.geom && gm
        AND  ST_Equals(a.geom, gm)
    );
  $$
  LANGUAGE sql
;

And adding a CHECK constraint like so :

ALTER TABLE geomtable
  ADD CONSTRAINT equality_constraint
  CHECK (equality_constraint_func(gid, geom))
;

However, this does not help me perform the DO UPDATE part of conflict resolution. It just raises an error on duplicate geometry. I of course can’t create a primary key out of my geom column as I get the index row requires 42632 bytes, maximum size is 8191 error. I get a similar error on creating a unique index on the geom column. I tried creating a unique constraint using the above syntax by replacing CHECK with UNIQUE, but I get a syntax error. Is unique constraint using the function the way to go here, or do I need to consider some other approach like a trigger, or exclusion constraint?

My version information is as under:

PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP