|
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@xxxxxxxxxxx]
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. |
