Morning! No worries on the delay and apologies for mine as well. I put this down for the weekend. I have a bit of flexibility on delivering this task so timing isn't driving me right now. I'm really just trying to understand better. As such, I had a few questions/comments. If I understand correctly.... (Let me know if I don't get it) - The GeomOverlay converts the BTREE to a BTREEDUP because of the 1:many relationship
- The Group by converts the BTREEDUP to a BTREE (otherwise the BTREE couldn't have been preserved in the scalar function)
- The Scalar function is as opposed to the vector split function. Use of the scalar function preserves the BTREE for use on the join.
- The join compares the original BTREE against the preserved BTREE for optimal performance.
My questions to you are.... - Do I understand things correctly?
- How might I speed this up? You suggested that this query could be improved. Right now, Query 1, 4 and 5 (included below) are virtually identical in performance. I've been testing them on a larger dataset. 4 is the better than 1 by about 0.5%, 5 is worse than 1 by about 10%.
- My only idea was to do the GeomOverlay on a BoundedBox version of the contours then add an extra GeomOverlay condition in the where statement to ensure the BoundedBoxes didn't pick up anything extra. However, this only slowed things down. I didn't attach this version.
Thanks for the tip on first/last too. This was from my Manifold 8 experience where I didn't have the collect statement.
Query 5 --CREATE TEMPORARY DATABASE CREATE ROOT x; USE ROOT x; CREATE DATASOURCE d AS ROOT; --LOAD CONTOURS INTO TEMPORARY DATABASE SELECT [MFD_ID],[GEOM],[HEIGHT] INTO [TCONTOURS] FROM d::[CONTOURS]; ALTER TABLE [TCONTOURS] (ADD INDEX [MFD_ID_XX] BTREE ([MFD_ID])); ALTER TABLE [TCONTOURS] (ADD INDEX [GEOM_XX] RTREE ([GEOM])); ALTER TABLE [TCONTOURS] (ADD PROPERTY 'FieldCoordSystem.Geom' 'PROJCS["Calgary_3TM_WGS_1984_W114",GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-114.0],PARAMETER["Scale_Factor",0.9999],PARAMETER["Latitude_Of_Origin",0.0],UNIT["Meter",1.0]]'); CREATE DRAWING [TCONTOURS Drawing] ( PROPERTY 'Table' '[TCONTOURS]', PROPERTY 'FieldGeom' 'GEOM' ); --CREATE LOOKUP TABLE SELECT [O_MFD_ID], ((COLLECT [S_MFD_ID] ORDER BY GeomArea([S_GEOM], 0) ASC FETCH 1)) INTO [TEMP] FROM CALL GeomOverlayContainedPar( [TContours DRAWING] ([mfd_id], [HEIGHT], [Geom]), [TContours DRAWING] ([mfd_id], [HEIGHT], [Geom]), 0,ThreadConfig(SystemCpuCount())) WHERE [S_MFD_ID] <> [O_MFD_ID] AND ABS([O_HEIGHT]-[S_HEIGHT]) < 0.1 GROUP BY [O_MFD_ID] --ADD INDEX TO LOOKUP TABLE ALTER TABLE [TEMP] (ADD INDEX [MFD_ID_XX] BTREE ([O_MFD_ID])); --RELATE BACK UPDATE ( SELECT [1].[MFD_ID], [1].[MINCON] AS [DESTINATION], [2].[S_MFD_ID] AS [DATA] FROM D::[CONTOURS] AS [1] INNER JOIN [TEMP] AS [2] ON [1].[MFD_ID] = [2].[O_MFD_ID] ) SET [DESTINATION] = [DATA]
|