SQL delete and update at the same time
Hi list, the question is in the ens of the Mail.
I have a problem with adjacent polygons when converting them to lines,
as I would like to only have one line in the boundaries between the former polygons. Now it could be up to 5.
I’ve been thinking of:
1. Creating a thin buffer around the line strings with the featureId from the line strings as a new table.
2. Then I should break the line strings to line segments into a new Table where the line segments gets the featureId from the line string.
3. Create a column (edited) in the new line-segment Table with a default value of 0
4. Then delete from the line segments table all segments that are within the buffer, but not the line segments that have the same featureId as the current buffer and edited value must be 0 to be deleted.
5. The line segments that are not deleted in the current buffer shall be updated (edited = 1).
6. Do step 4 and 5 until the all objects in the buffered table are executed.
I’ve come this far:
DELETE FROM "LineSegments" USING buffered as h WHERE
ST_Contains(h.the_geom, " LineSegments".the_geom) AND
"LineSegments".edited = 0 AND
NOT h.fid=" LineSegments".fid;
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?
Kind regards,
Paul
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.
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:mlewis@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.
Sorry, I don't know much about postgis at all. I assume you meant to have
THEN 1 in your update statement as well.
I notice b.fid=l.fid and NOT b.fid=l.fid in the two clauses. How about
separate update statements?
UPDATE linesegments l
set edited = 1
WHERE 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);
UPDATE linesegments l
set edited = 2
WHERE 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);
Something like this (and similar for b.fid=l.fid) should be equivalent to
the above as best I can figure. It might be more performant, but I don't
know the internals well enough to say for sure.
UPDATE linesegments l
set edited = 1
FROM buffered as b
WHERE
l.gid = l.gid and ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited =
null) AND
NOT b.fid=l.fid;