Hi Michael,
Thanks for taking interest in my problem.
My biggest problem is that I’m not so familiar with SQL, and having problems to
see how I can realize your suggestion.
I’ve been testing in pgAdmin with 2 tables:
linesegments and buffered.
linesegments (splitted line strings into segments)
fid (integer, the same value for all line segments from the original line)
the_geom (Postgis geometry LineString)
edited, integer set to 0 (afterwards, 1 = shall be deleted, 2 = not to be
deleted)
gid (integer, PRIMARY key)
buffered (polygons from bluffering original lines, thin buffer
fid (integer, the same value as the original line strings)
the_geom (Postgis geometry Polygon)
gid (integer, PRIMARY key)
This is what I’ve tried in pgAdmin4 from reading your comment
UPDATE linesegments l
set edited = CASE
WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE
ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited = null)
AND
NOT b.fid=l.fid) THEN
WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE
ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited = null)
AND
b.fid=l.fid) THEN 2
END
But the line segments that are within several buffer-polygon is set to 1 and
the line segments that are within only one buffer polygon is set to 2. I
thought this SQL-command would at least have on segment line set to 2 where
there are duplicates. And it is very slow, although I created gist index on the
two tables.
Can you see what I’m doing wrong?
Kind regards,
Paul
Från: Michael Lewis [mailto:mle...@entrata.com]
Skickat: den 24 juni 2020 21:33
Till: Malm, Paul (Operations AIM)
Kopia: PostgreSQL General
Ämne: Re: SQL delete and update at the same time
But how can I set the edited vale = 1 on the objects (line segments) that are
not deleted (in the current buffer) at the same time so it won’t be deleted in
the next run with an adjacent buffer?
You might want to create a temporary table to hold unique identifiers of all
records that you want to either delete or edit, and make use of a RETURNING
clause to determine which got deleted, then update the records NOT IN that
returning clause values (or use EXCEPT). Perhaps that would get you where you
want to end up.
