Here is a thread-safe version using COLLECT. The COLLECT expression gathers all point geoms for each group, ordering them by date and time. The resulting table is passed to BuildLine, a custom aggregate function returning one line for the table (for the group). Function: FUNCTION BuildLine(p TABLE) GEOM AS -- p -> ([Geom (I)]) ( SELECT GeomConvertToLine( GeomConvertToPoint( -- dissolve branches GeomMergePoints([Geom (I)]), FALSE ) ) FROM p ) END ; Query calling it: SELECT --[STORM_NUM], [NAME], [YEAR_], AVG([WIND]) AS [avgwind], MAX([PRESSURE]) AS [maxpressure], BuildLine( ( COLLECT [Geom (I)] ORDER BY [YEAR_], [MON], [DATE_], [TIME_] ) ) AS [Line] FROM [huricanepts] GROUP BY [STORM_NUM], [NAME], [YEAR_] --THREADS SystemCpuCount() ; (The custom aggregate function could calculate the AVG and MAX results too, returning a table rather than a scaler, but I don't think that would be cleaner. And--now!--BuildLine could equally be a script function instead of SQL.)
|