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)"
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP