Manifold Community Site: Making lines from point data

RE: Making lines from point data

Forest — Tue, 01 May 2018 08:00:19 GMT

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.

RE: Making lines from point data

tjhb — Tue, 01 May 2018 08:24:36 GMT

Yes. Pass an ordered multipoint to GeomConvertToLine.

RE: Making lines from point data

tjhb — Tue, 01 May 2018 21:13:13 GMT

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.

RE: Making lines from point data

Forest — Wed, 02 May 2018 00:46:08 GMT

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.

RE: Making lines from point data

tjhb — Wed, 02 May 2018 03:47:44 GMT

Sample data would help focus the example.

RE: Making lines from point data

tjhb — Thu, 03 May 2018 05:42:14 GMT

Or is that too hard.

RE: Making lines from point data

adamw — Sat, 05 May 2018 13:49:05 GMT

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

RE: Making lines from point data

Forest — Tue, 08 May 2018 00:42:02 GMT

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

RE: Making lines from point data

adamw — Tue, 08 May 2018 09:11:52 GMT

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.

RE: Making lines from point data

tjhb — Tue, 08 May 2018 09:54:05 GMT

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?).

RE: Making lines from point data

adamw — Tue, 08 May 2018 10:05:28 GMT

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.

RE: Making lines from point data

tjhb — Tue, 08 May 2018 10:09:12 GMT

Great. That sounds clear and simple. Thanks Adam.

RE: Making lines from point data

Dimitri — Tue, 08 May 2018 13:30:13 GMT

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