Subscribe to this thread
Home - General / All posts - Split lines - GeomOverlayTopologyIdentityPar
rk
621 post(s)
#09-Jan-18 08:54

I have 700K lines and 112 areas covering about 1% of the lines. I want to split the lines at area borders.

I have a query:

CREATE TABLE [SplitLines] (

  [mfd_id] INT64,

  [s_mfd_id] INT64,

  [ObjID] INT64,

  [o_mfd_id] INT64,

  [o_ObjID] INT64,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' 'EPSG:25832'

);

INSERT INTO [SplitLines] (

  [s_mfd_id][ObjID][o_mfd_id][o_ObjID][Geom]

SELECT

  [s_mfd_id][s_ObjID],

  [o_mfd_id][o_ObjID][s_Geom]

FROM CALL GeomOverlayTopologyIdentityPar(

  [Lines Drawing] ([mfd_id][ObjID][Geom]),

  [Area Drawing] ([mfd_id][ObjID][AreaGeom]),

  0,

  ThreadConfig(SystemCpuCount()));

I got

Unknown error. Unexpected operation.

I had run a version of this query previously and it completed in 14h.

That seemed slow so I simplified it (removed many columns). Tried with smaller samples.

3600 lines split with 1 area, 28 sec, adding ~100 lines

3600 lines split with 60 areas, 29 sec, adding ~2400 lines

26000 lines split with 112 areas, 282 sec, adding ~5000 lines

54000 lines split with 112 areas, 638 sec, adding ~6000 lines

700000 lines split with 112 areas Unknown error. Unexpected operation.

At first all CPU usage is 100%, all 8(16) cores are occupied. after few minutes usage drops to 1/8 12%.

Dimitri


7,413 post(s)
#09-Jan-18 08:59

What software are you using (build number)? Can you post a sample of the data as an .mxb?

rk
621 post(s)
#09-Jan-18 09:14

Build 9.0.164.1. I try to prepare a sample dataset.

rk
621 post(s)
#11-Jan-18 08:26

New results with rewritten 5-step SQL.

712454 lines split with 2606 areas, ~1800 sec, adding 450533 lines

Areas are very jagged, those 450533 added lines came from 83794 original lines that intersected an area.

5 steps

1. Find touching lines (only mfd_id)

INSERT INTO [Touching_Ids] (

  [mfd_id]

)

SELECT

  [s_mfd_id]

FROM CALL GeomOverlayTouchingPar(

  [Lines Drawing] ([mfd_id][Geom]),

  [Area Drawing] ([mfd_id][AreaGeom]),

  0,

  ThreadConfig(SystemCpuCount()))

WHERE

  [o_mfd_id] is not null

group by [s_mfd_id]

;

-- 83794 

2. Copy Touching lines (all attributes). Also important to have drawing based on this table, because GeomOverlayTopologyIdentityPar in step 4. requires a Drawing.

INSERT INTO [Touching_Lines] 

 ([mfd_id][Geom], <all other...>)

SELECT

 [Lines].[mfd_id][Geom], <all other...>

FROM  

 [Lines]

 JOIN

 [Touching_Ids]

 ON [Lines].[mfd_id] = [Touching_Ids].[mfd_id]

-- 83794

3. Delete Touching lines from original.

DELETE FROM 

 [Lines]

WHERE 

 [mfd_id] in (SELECT [mfd_id] FROM [Touching_Ids])

;

-- 83794

4. Split lines (only mfd_ids and Geom)

INSERT INTO [Lines Split] (

  [s_mfd_id][o_mfd_id][Geom]

SELECT

  [s_mfd_id]

  [o_mfd_id][s_Geom]

FROM CALL GeomOverlayTopologyIdentityPar(

  [Touching_Lines Drawing] ([mfd_id][Geom]),

  [Area Drawing] ([mfd_id][AreaGeom]),

  0,

  ThreadConfig(SystemCpuCount()))

;

-- 450533

-- 

5. Insert split lines with original attributes copied. Add Boolean attribute [InsideArea] with obvious meaning.

INSERT INTO [Lines] 

[Geom][InsideArea], <all other...>)

SELECT

 [Geom]

 

 CASE WHEN [o_mfd_id] IS NULL THEN FALSE ELSE TRUE END as [InsideArea]

 

 <all other...>

FROM

 [Lines Split] ([Geom][o_mfd_id][s_mfd_id])

 JOIN

 [Touching_Lines] ([mfd_id], <all other...>)

 ON [Lines Split].[s_mfd_id] = [Touching_Lines].[mfd_id]

;

-- 450533

I had a small error at p5, and I didn't see the exact timing of previous steps in Log window. I think it was about 30min total.

Very quick! I hope it is possible to make the first straightforward GeomOverlayTopologyIdentityPar version as quick.

adamw


10,447 post(s)
#11-Jan-18 08:56

I hope it is possible to make the first straightforward GeomOverlayTopologyIdentityPar version as quick.

Yes, it is possible. The current implementation has multiple significant limitations, we will remove them.

Good job splitting the task into smaller portions!

rk
621 post(s)
#11-Jan-18 10:17

Updated timings

1. 517 sec

2. 95 sec

3. 119 sec

4. 2436 sec

5. 79 sec

Total 54min

Save (but not only those changes) still in progress after 15min.

lionel

995 post(s)
#11-Jan-18 13:34

Hi

Can you post capture screenshots before/After or mxb file to let us understand or test the SQL queries ? N'

Does mfd9 support run a SQL Query file to Call all those Queries store in indépendant file to be execute in a serie order?

Thank's


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

rk
621 post(s)
#11-Jan-18 15:53

mxb attached

Attachments:
mf9_Split_Lines.mxb

lionel

995 post(s)
#11-Jan-18 16:06

thank's a lot

here link to documentation http://www.manifold.net/doc/mfd9/sql_functions.htm

par in the end of function name mean parallelized !!!

transform overlay topology is not transform topology !!! ( save replace so delete old value , save without replace so insert in new column )

GeomOverlayTopologyIdentityPar(<drawing>, <overlay>, <tolerance>, <config>) : <table>

A parallelized form of the GeomOverlayTopologyIdentity function that takes a parameter for the suggested number of threads as encoded in the JSON <config> string as "threads". If the number of threads is less than or equal to 1 the GeomOverlayTopologyIdentity function will be executed. Use ThreadConfig to generate a JSON string with the desired number of threads.

GeomOverlayTopologyIdentity(<drawing>, <overlay>, <tolerance>) : <table>

Using the area boundaries of objects in the overlay drawing, slice all objects in the source drawing into regions that are overlapped by objects in the overlay drawing and that are not overlapped by objects in the overlay drawing. Save all the pieces thus created as geoms into the result table. See the discussion and illustrations for Topology Overlay, Identity in the Transform: Overlay Topology topic. Given a drawing and an overlay drawing, returns a table with the following characteristics:

See the SQL Example: GeomOverlayTopologyUnion Functiontopic for an example of how functions like this are used.


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Dimitri


7,413 post(s)
#12-Jan-18 05:19

Does mfd9 support run a SQL Query file to Call all those Queries store in indépendant file to be execute in a serie order?

See the Queries topic. A query component can contain multiple queries. A component can be saved into a .map or .mxb and a .map can be linked into another map.

lionel

995 post(s)
#12-Feb-18 01:20

query documentation


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.