I have two link tables
one is 9029 rows (and let's say this table is A) and the other is 11698 rows (and this table is B)
the both tables are road link and A is for tunnel link and B is road link
From table road link (table B), I need to erase segment where tunnels are (Table A)
Basically, it is Table B - Table A.
This is my spatial query though, it took me so much time and I do not know why.. It seems like other GIS specialists also experiences that st_difference takes quite lots of time.
CREATE TABLE rd_tunnel_erased AS SELECT b.objectid, b.roadclass, b.linkclass, b.linktype, st_difference (b.shape, a.shape) as shape FROM b, a
As I said b is road link and a is tunnel link (and the two table does have same schema but difference geometry)
Here are two screenshots that might help you
With only 1 link, the st_difference worked fine and fast.
Somehow, when it goes over my real data, it takes more than 3~4 days (in fact, I do not know how many days and hours it took.. because it failed for trivial mistakes. I am afraid that I might have to waste my time for re-running this query and ended up getting nothing.)
All my tables are spatially indexed.