0

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 enter image description here

from b table, I erased (differenced) a table. enter image description here

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.

  • You need to provide the complete SQL statement, not just a fragment, and the EXPLAIN plan. All questions should always contain the exact software in use (name plus version). – Vince Jul 30 '18 at 11:00
  • 1
    It's unwise to name your tables "a" and "b", because these are usually the aliases given. Best practice is to use JOIN when joining tables, which is basically your problem -- failure to specify any join constraint. ST_Difference has no reason to use a spatial index, so you have a Cartesian product of 105+ million difference operations. – Vince Jul 30 '18 at 11:15
  • @Vince, Thank you so much for your correcting and guiding. You suggested me to join the two tables though but I am not clear why you suggested that. – Pil Kwon Jul 30 '18 at 11:22
  • @Vince, I just googled cartesian product. I kind of understood what you meant. But I am still not clear joining the table. What should I do after joining the two tables? – Pil Kwon Jul 30 '18 at 11:32
  • 1
    If you had created tables named a_subset and b_subset, with 9 and 11 rows, instead of using a singleton table for testing, you would have seen the problem -- 99 rows would be returned. By using the JOIN syntax, you need to consider what you are joining on, e.g. ST_Intersects(a.shape,b.shape) which will then utilize the spatial index. – Vince Jul 30 '18 at 11:35
3

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.

  • I am trying your logic. I will share the result! by the way, the update query should look like update rd_link_tunnel_erased set shape = st_difference (b.shape, a.shape) from a, b where st_intersects(b.shape, a.shape) in my case – Pil Kwon Aug 1 '18 at 2:23
  • @PilKwon actually, I don't think it should; I used aliases here that are equal to your structure and there is no need for a cross join. try both and see yourself. – ThingumaBob Aug 1 '18 at 6:49
  • actually you are right. the results was not what I expected. I will keep you updated – Pil Kwon Aug 1 '18 at 10:13
  • It gave me a same segment as Table B.. – Pil Kwon Aug 1 '18 at 10:24
  • 1
    @ziggy yeah that's not allowed...use a CTE, it's much faster anyway; check the update above. – ThingumaBob Aug 1 '18 at 15:47
3

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.

0

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. enter image description here

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 enter image description here

I am sorry...

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.