Hi,

I have to get rid of small areas (< 100 sq. meters) in a drawing. Small areas have to be merged with their biggest neighbour (if any) belonging to the same parcel.

I attached a .map file with my script and some data. The script is working but it gets slower as the dataset gets bigger. My full dataset contains over 3M areas. I'm really looking to get more speed while processing it.

Areas smaller than 100 sq. m. are in the "small_poly" drawing and in the "PEE" drawing. Query 1 is selecting the biggest neighbour from the same parcel. Query 2 is making the union in PEE and query 3 is deleting the small area from PEE once the union is done. The script is looping through the small_poly drawing.

Questions :

1- Is it doable (and faster) just using SQL ? Any example of looping through data with SQL would be appreciated.

2- Does using GPU would bring speed with this kind of processing ? I only tested without GPU, but I have another computer with one device installed.

Any helps or though on how to speed things up is welcome !

Regards

Attachments:
Area_Union.zip

The script :

' VBScript

Sub Main

Set app = Manifold.Application

Set db = app.GetDatabaseRoot()

Dim sqlquery, tbl, count, seq, SP, fieldData1, fieldData2, lot_nb, uuid, values, values2, i, UUbig, startTime, stopTime

startTime = Now

i = 0

sqlquery = "SELECT COUNT(*) AS rec_count FROM [small_poly_Table]"

Set tbl = db.Run(sqlquery)

Set seq = tbl.SearchAll(Array("rec_count"))

   If seq.Fetch() Then

        Set values = seq.GetValues()

        count = values(0).Data

 app.Log  "count = " & count

 End If

Set SP = db.Search("small_poly_Table")

Set fieldData1 = SP.SearchAll(Array("NO_LOT"))

Set fieldData2 = SP.SearchAll(Array("Uuid_64"))

Do While i < count

'app.log i

 fieldData1.Fetch() 

        lot_nb = fieldData1.GetValues()(0).Data

 'app.log lot_nb

 fieldData2.Fetch() 

 uuid = fieldData2.GetValues()(0).Data

 'app.log uuid

'query #1 : identify the neighbouring polygon 

sqlquery = "Select A.[Uuid_64] AS UU from [PEE TABLE] as A , [small_poly_Table] as B where B.Uuid_64 = " & uuid & " and A.[NO_LOT] = '" & lot_nb & "' and A.Uuid_64 <> B.Uuid_64 and GeomDistance(A.[Geom (I)], B.[Geom (I)],0) <= 0.01 order by A.[SHAPE_Area] DESC fetch 1"

Set tbl = db.Run(sqlquery)

Set seq = tbl.SearchAll(Array("UU"))

 If seq.Fetch() Then

 UUbig = seq.GetValues()(0).Data

'query #2 : Update geometry

sqlquery = "Update [PEE Table] Set [Geom (I)] = (Inline GeomUnionAreas((select [Geom (I)] from [PEE Table] where [Uuid_64] =" & UUbig & "), (select [Geom (I)] from [PEE Table] where [Uuid_64] =" & uuid & ") )) where [Uuid_64] =" & UUbig

db.Run(sqlquery)

'app.log "updated objects : " & UUbig & " avec " & uuid

'query #3 : Delete object

sqlquery = "Delete from [PEE Table] where [Uuid_64] = " & uuid

db.Run(sqlquery)

'app.log "deleted object : " & uuid

End if

i = i + 1

Loop

stopTime = Now

app.log "start time = " & startTime & " / stop time = " & stopTime

End sub

As a reference, it takes 37 minutes to process 111 small areas with the 3.2M areas dataset, with my non-GPU computer.

I wrote a query to replace the query #1 in the script. The new query produces the full list of pairs "small area / biggest neighbour in the same parcel". The query #1 is then not necessary anymore. The script is looping the new list and runs query #2 (union areas) and #3 (deleting small area).

This is way faster than before. But still, I'm interested to obtain answer to my questions in the first post.

If I understood correctly your target the pure SQL would be something like this code in M9. This one is not altering some of your attribute that might be corrected as the surface or the densities maybe. It runs in a second on my laptop. I used "touches" instead of a minimal distance like you did, but easily changed...

-- ADD INDEX ON COLUMNS OF INTEREST (GROUP BY, WHERE AND JOIN CLAUSES)

ALTER TABLE [Pee Table] (

  ADD INDEX [lot_x] BTREEDUP ([NO_LOT] COLLATE 'neutral')

);

-- CREATE NEW TABLE WITH THE MERGED AREAS

SELECT BIGAREAS.[mfd_id],[ID],[NO_LOT],[No_seq],[code_ess],[Age_simple],[DENSITE],[HAUTEUR],[ORIGINE],[An_orig],[PERTURB],[An_pertur],[volume],[ETA_ESS_PC],[lot_id],[sup_m2],[type],[Shape_Area],[Uuid_64], CASE WHEN FULLGEOM IS NULL THEN [Geom (I)] ELSE FULLGEOM END AS [Geom (I)]

INTO NEWPEE

FROM (

    -- IDENTIFY BIGGER AREAS

    SELECT [PEE].*, GeomArea([Geom (I)], 0) AS GEOMAREA

    FROM [PEE]

    WHERE GeomArea([Geom (I)], 0) >= 100

) AS BIGAREAS

LEFT JOIN (

    -- MERGING BIGAREA WITH ALL THE SMALLS TOUCHING

    SELECT BIGID, INLINE GeomUnionAreas(FIRST(BIGGEOM), GeomUnionAreas(SMALLGEOM)) AS FULLGEOM

    FROM (

        SELECT BIGAREAS.mfd_id AS BIGID, SMALLID, BIGAREAS.[Geom (I)] AS BIGGEOM, SMALLGEOM

        FROM (

            -- RETRIEVE COUPLES OF SMALL-BIG USING THE BIGGER TOUCHING AREA ONLY

            SELECT SMALLAREAS.[mfd_id] AS SMALLID, FIRST(SMALLAREAS.[Geom (I)]) AS SMALLGEOM, SPLIT ( COLLECT BIGAREAS.mfd_id, BIGAREAS.[Geom (I)] ORDER BY GEOMAREA FETCH 1)

            FROM (

                -- IDENTIFY SMALL AREAS

                SELECT *

                FROM [PEE]

                WHERE GeomArea([Geom (I)], 0) < 100

            ) AS SMALLAREAS

            INNER JOIN (

                -- IDENTIFY BIGGER AREAS

                SELECT [PEE].*, GeomArea([Geom (I)], 0) AS GEOMAREA

                FROM [PEE]

                WHERE GeomArea([Geom (I)], 0) >= 100

            ) AS BIGAREAS

            ON SMALLAREAS.[NO_LOT] = BIGAREAS.[NO_LOT]

            AND GeomTouches(SMALLAREAS.[Geom (I)], BIGAREAS.[Geom (I)],0)

            GROUP BY SMALLAREAS.[mfd_id]

        )

    ) AS NEWGEOM

    GROUP BY BIGID

) AS NEWGEOMS

ON BIGAREAS.mfd_id = BIGID

;

ALTER TABLE [NEWPEE] (

  ADD PROPERTY 'FieldCoordSystem.Geom (I)' 'EPSG:32198'

);

CREATE DRAWING [NEWPEE Drawing] (

  PROPERTY 'FieldGeom' 'Geom (I)',

  PROPERTY 'Table' '[NEWPEE]'

);

Wow, great SQL coding ! I really appreciate. Your strategy is very interesting and a good example for learning.

I understand all of it, but intuitively I would have order the SPLIT section with DESC. Im going to test this section alone for a better understanding.

Thank you very much for your time and skills.

I added DESC to Order By in the SPLIT section, otherwise small areas are not merged with the biggest neighbour.

oops sorry you're right to have bigger first, sorry for this....