Subscribe to this thread
Home - General / All posts - GeomReverseLine in SQL9
rk
198 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
198 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,022 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?

rk
198 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,452 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,452 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,452 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

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