Subscribe to this thread
Home - General / All posts - GeomReverseLine in SQL9
rk
216 post(s)
#09-Oct-17 11:42

I want to define FUNCTION GeomReverseLine in SQL9.

I tried something by using SPLIT and GROUP BY, but that does not do what I thought it would.

Perhaps it is wiser to use FUNCTION ... AS SCRIPT?

My first try in SQL, not a function yet:

--SQL9

Select 

 mfd_id, GeomConvertToLine([Geom]as [Geom]

FROM

(

SELECT 

 mfd_id, GeomMergePoints(GeomMakePoint(XY)) as [Geom]

from

(

select * from

 (

 SELECT

 mfd_id, 

 SPLIT CALL GeomToSegments([Geom]

 FROM

 [D1]

 )

 ORDER BY mfd_id, [Coord] DESC

)

GROUP BY mfd_id

)

;

rk
216 post(s)
#09-Oct-17 12:16

I almost got what I was after.

But I could not join branches with GeomNormalize, because it changes line direction.

Project attached.

Attachments:
mfd9_GeomReverseLine.map

KlausDE
6,054 post(s)
#09-Oct-17 12:22

And BTW: What is so "abnormal" with a line directed Est -> West? Why is this subject to normalization?

adamw


7,307 post(s)
#27-Oct-17 09:14

We might try to save the direction for unaltered branches, but in general this calls for a feature like putting some markers into the metric and have them retained during operations.

You probably want to retain the coordinate order to tell what specific points are at the "start" vs at the "end" or where the line "goes", and relying on coordinate order for that just feels too brittle.

KlausDE
6,054 post(s)
#27-Oct-17 18:50

OK, not all lines are vectors but some are. Thats what I do in Mfd8 now:

Create a field named [StartPoint] type Geom and Update ... SET StartPoint = Startpoint(Geom (I)).

In case this same test fails I know I have to reverse all such lines. Quickly done with an update query. Of course there are many functions like JoinLines that leave MY StartPoint-marker coincidental and useless or may be missleading.

Do we | will we have Transfer Rules in MF to automatically NULL such fields.

adamw


7,307 post(s)
#28-Oct-17 10:34

The transfer rules are in the Options dialog in the Transfer pane. I don't think they are applicable here, though, because as far as I understand you want the transform to NULL the value of StartPoint as long as it sets the Geom, that's something else.

Relying on the coordinates of the first / last point is exactly what I am calling brittle. I understand it works with fixed workflows but imagine taking a snapshot of the start coordinate and then generalizing the line to remove unnecessary detail, for example - the snapshotted coordinate might very well be different from both the new start and the new end and you don't know whether or not you have to reverse the line, the information has been lost.

It would be useful to find some way to encode the info into a geom that would be more resistant to changes. This could be some "protected" coordinates with markers that cannot be altered, for example (or if they are altered, the marker is lost and you can detect that it has been lost).

rk
216 post(s)
#09-Oct-17 13:39

First simple working version in IronPython

# IronPython

def GeomReverseLine(geom):

 coords = geom.Coords

 app = Manifold.Application

 builder = app.CreateGeomBuilder()

 builder.StartGeomLine()

 builder.AddBranch()

 for index in range(coords.Count-1, -1 , -1):

 builder.AddCoord(coords[index])

 builder.EndBranch()

 return builder.EndGeom()

Attachments:
mfd9_GeomReverseLine.map

tjhb

7,545 post(s)
#10-Oct-17 06:56

I got a lot out of this idea today, thank you Riivo! It hadn’t occurred to me to try it.

Some good progress—report tomorrow or late tonight. (Today was mostly travel but I was always thinking about this. Wish I had been in a train not a car.)

tjhb

7,545 post(s)
#13-Oct-17 00:44

Sorry for the delay.

This uses a small trick which I'll explain first.

GeomMergeLines aggregates source lines, but leaves each source line as a separate branch. Here, if we split each line into segments, reverse each segment, then recombine, we end up with each reversed segment as a new branch. That's not what we want.

Riivo used GeomNormalize to dissolve away these internal branches, but as we all know, GeomNormalize disrupts line direction. (As Klaus says, that is something we need to discuss separately.)

The trick is to convert the merged line to a (multi)point, specifying <keepBranches> = FALSE to join all coordinates into one branch, then convert back to a line. The double conversion does not affect coordinate order (line direction).

GeomConvertToLine(

    GeomConvertToPoint(

        GeomMergeLines([Segment reversed]),

        FALSE)

    ) AS [Branch reversed]

The first query is not very good, because it uses ORDER within subqueries to reverse the order of line segments, and also to reverse the order of branches (if more than one). This is not good since in SQL9, ORDER using is not necessarily maintained from an inner query to an outer query. In particular, it is not thread safe. If an outer query uses mutliple threads, the order in which each thread takes rows from the inner query is undefined. If we limit the outer query to a single thread, then this works OK (for now). But that's not guaranteed, so this is bad design.

INSERT INTO [D2 Table] ([Geom])

SELECT GeomMergeLines([Branch reversed]-- in listed order

FROM

    (

    SELECT

        [mfd_id],

        GeomConvertToLine(

            GeomConvertToPoint(

                GeomMergeLines([Segment reversed]), -- in listed order

                FALSE)

            ) AS [Branch reversed]

    FROM

        (

        SELECT

            [mfd_id][Branch],

            GeomMakeSegment([XYNext][XY]AS [Segment reversed]

        FROM

            (

            SELECT [mfd_id],

            SPLIT CALL GeomToSegments([Geom])

            FROM [D1]

            )

        ORDER BY 

            --[mfd_id] ASC,

            --[Branch] DESC,

            [CoordInBranch] DESC

        )

    GROUP BY [mfd_id][Branch]

    THREADS 1

    ORDER BY 

        --[mfd_id] ASC,

        [Branch] DESC

    )

GROUP BY [mfd_id]

THREADS 1

;

Here is a better version, performing the ORDER inside a COLLECT expression, where it is thread-safe, and passing the reversed geometry for each branch to a custom aggregate function.

The function:

FUNCTION JoinLines(t TABLE) GEOM

    -- rebuild line, combining segments or sections per branch

    -- in listed order

    -- [Branch] index

    -- [result] segments or sections

    AS (

    SELECT GeomMergeLines([Joined]) -- all branches

    FROM

        (SELECT

            -- combine segments or sections per branch

            -- passing via (multi)point

            -- to join internal branches

            GeomConvertToLine(

                GeomConvertToPoint(

                    GeomMergeLines([result]),

                    FALSE) -- join internal branches

            ) AS [Joined]

        FROM t

        GROUP BY [Branch]

        )

    )

    END

;

The query calling it:

INSERT INTO [D2 Table] ([Geom])

SELECT 

    JoinLines(

        (COLLECT 

            [Branch]-- index

            GeomMakeSegment([XYNext][XY]-- [result]

                -- each segment reversed

            ORDER BY [Branch] DESC[CoordInBranch] DESC

                -- for recombination

        )

        )

FROM

    (

    SELECT [mfd_id],

    SPLIT CALL GeomToSegments([Geom])

    FROM [D1]

    )

GROUP BY [mfd_id]

;

tjhb

7,545 post(s)
#13-Oct-17 01:23

Here's a simpler version, using GeomToCoords rather than GeomToSegments.

At first I thought it was not possible to use GeomConvertToLine directly on a multipoint. It is possible, but if the multipoint contains branches, each branch is used to form a line. Specifically, if each point in the multipoint is a separate branch,* the resulting line will be composed of the same number of pathological (zero-length) lines. That's not what we want! So again we use GeomConvertToPoint, with <keepBranches> = FALSE, to join internal branches in the multipoint before converting to a line.

(*That is the result returned by GeomMergePoints. From the manual, it "Takes a set of points and returns a multipoint geom that contains branches". Each sorce object--here a point--forms an individual branch in the result.)

If looks a bit odd to use GeomConvertToPoint when the source is already a (multi)point. In this case we only "convert" for the side effect.

Function:

FUNCTION BuildLine(t TABLE) GEOM

    -- combine points per branch in listed order

    -- [Branch] index

    -- [XY] point

    AS (

    SELECT GeomMergeLines([Line]) -- all branches

    FROM

        (SELECT

            GeomConvertToLine(

                GeomConvertToPoint(

                    -- pro forma (re)conversion to dissolve internal branches

                    -- (otherwise resulting line would have one zero-length branch

                    -- for each point)

                    GeomMergePoints(GeomMakePoint([XY])),

                    FALSE) -- join internal branches

            ) AS [Line]

        FROM t

        GROUP BY [Branch]

        )

    ) 

    END

;

Query:

INSERT INTO [D2 Table] ([Geom])

SELECT BuildLine(

    (COLLECT [Branch][XY]

    ORDER BY [Branch] DESC[CoordInBranch] DESC

    )

    )

FROM

    (

    SELECT [mfd_id],

    SPLIT CALL GeomToCoords([Geom])

    FROM [D1]

    )

GROUP BY [mfd_id]

;

Attachments:
GeomReverseLine c m9 forum.map

adamw


7,307 post(s)
#27-Oct-17 09:10

A version that retains branches:

#IronPython

 

def GeomReverseLineBranched(geom):

  app = Manifold.Application

  builder = app.CreateGeomBuilder()

  builder.StartGeomLine()

  for branch in geom.Branches:

    builder.AddBranch()

    coords = branch.Coords

    for index in range(coords.Count-1, -1 , -1):

      builder.AddCoord(coords[index])

    builder.EndBranch()

  return builder.EndGeom()

MAP file attached.

Attachments:
mfd9_GeomReverseLineBranched.map

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.