I am running this on a local laptop computer. Meanwhile I have been working on this I made some renaming and changes to the initial coding, which probably will read easier. ******** SATRT CODE ************ -- $manifold$ -- -- Auto-generated -- -- Overlay -- Layer: Buildings -- Field: Geom -- Operation: identity -- Overlay: Parcels -- Overlay selection only: FALSE -- Overlay fields: parcels_{name} -- Result: (new table) -- Result type: geom -- New drawing: Buildings_cookieCut_Parcels -- New table: Buildings_cookieCut_Parcels Table -- Resources: all CPU cores, all GPU cores -- Transform selection only: FALSE -- -- prepare begin CREATE TABLE [Buildings_cookieCut_Parcels Table] ( [mfd_id] INT64, [OIDN] INT32, [UIDN] INT32, [gemNISCODE] NVARCHAR, [gemNAAM] NVARCHAR, [calcArea] FLOAT64, [parcels_mfd_id] INT64, [parcels_UIDN] INT32, [parcels_OIDN] INT32, [parcels_calcArea] FLOAT64, [calcArea_CutBldg] FLOAT64, [pctCutBldgonSourceBldg] FLOAT64, [pctCutBldgonParcel] FLOAT64, [Geom] GEOM, INDEX [mfd_id_x] BTREE ([mfd_id]), INDEX [Geom_x] RTREE ([Geom]), PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([Buildings Table], 'Geom') ); CREATE DRAWING [Buildings_cookieCut_Parcels] ( PROPERTY 'Table' '[Buildings_cookieCut_Parcels Table]', PROPERTY 'FieldGeom' 'Geom' ); -- prepare end VALUE @drawing TABLE = CALL ComponentFieldDrawing([Buildings Table], 'Geom'); VALUE @overlay TABLE = [Parcels]; DELETE FROM [Buildings_cookieCut_Parcels Table]; INSERT INTO [Buildings_cookieCut_Parcels Table] ( [OIDN], [UIDN], [gemNISCODE], [gemNAAM], [calcArea], [parcels_mfd_id], [parcels_UIDN], [parcels_OIDN], [parcels_calcArea], [calcArea_CutBldg], [pctCutBldgonSourceBldg],[pctCutBldgonParcel], [Geom] ) SELECT [s_OIDN], [s_UIDN],[s_gemNISCODE], [s_gemNAAM], [s_calcArea], [o_mfd_id], [o_UIDN], [o_OIDN], [o_calcArea], geomArea([s_geom],0), geomArea([s_geom],0)/[s_calcArea], geomArea([s_geom],0)/[o_calcArea], [s_Geom] FROM CALL GeomOverlayTopologyIdentityPar(@drawing, @overlay, 0, ThreadConfig(SystemCpuCount())) -- WHERE [s_gemNISCODE] = 11001 ******** END CODE ************ Now with the above code I cookie cut the Buildings drawing with the Parcels drawing, get the individual area for each (part of) building , calculate the percent ratio for each (part of) building versus the original building area, and likewise versus the parcel area. This is running well. I tested adding a WHERE clause which runs well. Two more aims I would like to achieve : 1. As the million Buildings records x million Parcels records is too large for my computer's memory to handle swiftly, I aim to filter the Buildings and Parcels drawing tables on a common field : gemNISCODE Have a loop over all gemNiscode values and execute the above Transform-Overlay as a sub-routine How could I put this into a script or EXECUTE from another query passing parameters for the gemNISCODE values ? 2. Calculate the final footprint ratio of all the (parts of) buildings for each Parcel they are contained in. I suppose that a mere Select parcels_mfd_id , SUM([pctCutBldgonParcel]) as footprintratio from [Buildings_cookieCut_Parcels Table]GROUP BY [parcels_mfd_id] will do. As usual , any help is welcome.
|