Does M9 have an ability to make lines out of point data, the way that M8 does? I can't tell by looking over the new function list.

Yes. Pass an ordered multipoint to GeomConvertToLine.

Sorry, that was a bit terse.

It would make a nice example, if you have sample that would be useful?

Ensuring point order in SQL9 (in a thread-safe way) involves use of COLLECT.

Terse is fine. Use of COLLECT will almost certainly require an example.

I wrote my own code to import gpx files but tracks come in as track points. I used to use Manifold 8 to rebuild the tracks.

Sample data would help focus the example.

Or is that too hard.

Try this:

--SQL9

FUNCTION makeline(t TABLE) GEOM AS (

  SELECT

    GeomConvertToLine(GeomConvertToPoint(GeomMergePoints([Geom]), false))

  FROM t

) END;

DELETE FROM [Lines];

INSERT INTO [Lines] ([Geom])

SELECT makeline((COLLECT [Geom] ORDER BY [LineCoord])) FROM [Points]

  GROUP BY [Line];

The query assumes points are in [Points] and each point has [Line] which specifies which line it belongs to and [LineCoord] which specified the coordinate order on that line.

The query takes these points, joins them into lines and writes the resulting geoms into [Lines]. The coordinates for each line are first grouped using SELECT ... GROUP, then ordered internally using COLLECT ... ORDER. The result - a table of ordered coordinates - is then passed to a function which joins individual points into a single geom using GeomMergePoints, then removes branches and puts all points into a single branch, then converts the result to line.

See attached MAP file.

Attachments:
join-points-into-lines.mxb

Hi Tim,

I am sometimes out of the office. Please find the attached mxb file. It includes some driving through country and a search through rainforest for rare flora, which is a very messy trail. I am yet to test my data against Adams query.

Attachments:
TrackPoints.mxb

Well, here is what I did:

Opened the MXB. Created a new drawing on a new table, named it Lines.

Opened command window, copy / pasted the query from above, adjusted the names, and ran it:

--SQL9

FUNCTION makeline(t TABLE) GEOM AS (

  SELECT

    GeomConvertToLine(GeomConvertToPoint(GeomMergePoints([Geom]), false))

  FROM t

) END;

DELETE FROM [Lines];

INSERT INTO [Lines] ([Geom])

SELECT makeline((COLLECT [Geom] ORDER BY [TrackPointID])) FROM [TrackData]

  GROUP BY [LineID];

This created 2 lines.

Opened the drawing. Noticed that I forgot to set the coordinate system, pressed Ctrl-1 to go to the Component pane. Clicked the button next to the coordinate system readout, selected Repair Initial Coordinate System - Latitude / Longitude.

Dropped the original drawing into the window with the lines. They lined up.

I have been meaning to post a detailed question on this, regarding coordinate order.

We can now intentionally (or just maybe, inadvertently) mess up the operation of a custom aggregate function like makeline() above, by adding a THREADS specifier (> 1) to the function’s embedded SELECT statement. Then the table passed to the function is not handled strictly in order, making line(s) short-circuited by spurious arcs (between branches constructed by different threads).

Is it always safe to assume—now and in the future—that an optimization like this will never be applied automatically (that is, with no explicit THREADS specifier, or with explicit THREADS 1)? Even when passing a very large table?

If it is not completely safe, or might not be later, then that seems a good reason to always prefer a script function to build a custom aggregate, rather than an SQL function, I think?

[Added.] If this kind of automatic optimization might come later (even for SELECT inside FUNCTION), perhaps it would be good (and enough) to add a PRAGMA allowing automatic multi-threading to be disabled (per node I think?).

We really don't think we will be inserting THREADS automatically. The way to tell the system "use threads if this is beneficial and figure out how many is best as well" will be THREADS AUTO or something like this.

Great. That sounds clear and simple. Thanks Adam.

Thanks, Forest, for the .mxb and thanks, Adam, for the query! This has been used in a video. :-)