Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL delete and update at the same time

SQL delete and update at the same time

From
Date:
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
featureIdfrom 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
thathave 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
thesame time so it won’t be deleted in the next run with an adjacent buffer?
 
Kind regards,
Paul

Re: SQL delete and update at the same time

From
Michael Lewis
Date:
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.

SV: SQL delete and update at the same time

From
Date:

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.

Re: SQL delete and update at the same time

From
Michael Lewis
Date:
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;