Increase spatial query performance (st_difference)

Question

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

this is overlayed a and b table

from b table, I erased (differenced) a table.

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.

Answer 1

If I get this right, you want a new table with the 'difference' of both networks. To achieve that, I´d go for a two step operation:

• create a copy of the roads table as your later result table
• update its geometries with the difference.

This will make sure all other geometries (and attributes) will be there, as with the query you intended (provided the proper use of a JOIN), the resulting table would only hold those roads that were actually split!

Note that you will most likely get Multi geometries in return, thus the result table has to be prepared to accept them. Using ST_Multi on the geometries will force the column type to be MULTILINESTRING.

Create the copy:

CREATE TABLE rd_tunnel_erased AS
  SELECT objectid,
         roadclass,
         linkclass,
         linktype,
         ST_Multi(shape) AS shape
  FROM b;

CREATE INDEX rd_tunnel_erased_shape_idx
  ON rd_tunnel_erased
  USING GIST (shape);

Update geometries:

UPDATE rd_tunnel_erased AS rd
  SET shape = ST_Difference(rd.shape, tn.shape)
  FROM a AS tn
  WHERE ST_Intersects(rd.shape, tn.shape);

VACUUM ANALYZE rd_tunnel_erased;

UPDATE:

If two or more tunnel segments fall on the same road segment, the implicit cross join in the UPDATE query will create an equal amount of the same road segment, each subtracted by one of the intersecting tunnel segment. One option would be to either ST_Collect them by an identifying attribute (e.g. if two-part tunnels have the same tunnel_id) or ST_Collect simply all tunnel segments prior to subtracting the roads:

WITH
  blade AS (
    SELECT ST_Collect(shape) AS shape
    FROM a
    --GROUP BY <tunnel_id>
)

UPDATE rd_tunnel_erased AS rd
  SET shape = ST_Difference(rd.shape, tn.shape)
  FROM blade AS tn
  WHERE ST_Intersects(rd.shape, tn.shape);

Uncomment the GROUP BY clause when there is a proper <tunnel_id>.


There are other ways, but I find this the most forward and efficient one.

Answer 2

Current SQL language best practice frowns on the use of the "FROM t1,t2" syntax, mostly because it's vulnerable to omission of the join constraint in the WHERE clause. Failure to place any constraint results in a full Cartesian product -- processing every row in the first table against every row in the second, which in your case results in 105,621,242 rows.

Assuming you only want a difference operation where the geometries intersect, a more conventional way to execute this query would be:

CREATE TABLE rd_tunnel_erased AS
SELECT b.objectid, b.roadclass, b.linkclass, b.linktype, st_difference (b.shape, a.shape) as shape 
FROM road_links b
JOIN tunnels a ON ST_Intersects(b.shape,a.shape)

There are a number of risks, even here, since the intersection could be at a point. You may need to add a WHERE clause to ensure the type of intersection is linear.

As a rule, queries involving 10k rows should no run longer than a few seconds, a minute tops. If you find a query which runs more than an hour, something is definitely wrong. Execution for days is an indication that you also have platform issues (unusably slow disk, too little RAM, misconfigured database). If in doubt, ask PostgreSQL how it intends to execute the query by adding "EXPLAIN" in front of your SQL statement, then include the output of that in your question, either here or in Database Administrators.

Answer 3

I am sure I am going to have bad reputation after posting this.. but I probably have to for those who helped me.

Again, I am very thankful to all those who gave me solutions. the soultion from @ThingumaBob took me less than 10 seconds but still did not get what I wanted to have In fact, the results gave me the same link and the original road link.

I just tried this in QGIS difference function for no reasons. Yet, instead differencing link to link, I buffered 3 meters to the tunnel link and tried the difference.

This is the process. 1. Buffered the tunnel link. So the result is in Polygon 2. Difference the Polygon from road link.

I managed to get the road link without real tunnel segment that I wanted to have.

I gave a buffer of 1 meter to the tunnel segment (table A) and tried the soultion from @ThingumaBob. That guided me the correct almost answers.

It's weird, ain't it? So, I zoomed all the way into where QGIS allowed me to

I am sorry...

but, still. It's weird though. the first testing (st_difference with only 1 link) worked.