Subscribe to this thread
Home - General / All posts - Inflection point index generator
rk
621 post(s)
#06-Sep-09 11:31

From another thread a useful solution emerged to a problem with the ‘split’ function Coords. Behind the problem is an important concept. The concept that Line (the type we are used to in GIS, not just anything you may call a line) can possibly be represented as sequence of Points i.e. set of Points with linear order.

The problem in Manifold is that SPLIT BY Coords function ‘loses the order’ and gives us only the set.

And here is the solution for Manifold by Tim (tjhb):

-- Line points with index

-- returns the coordinates of each line with a sequential index (zero-based).

OPTIONS COORDSYS("Drawing" AS COMPONENT);

SELECT

    [ID],

    CASE [Point] = StartPoint([ID])

        WHEN TRUE THEN 0

        ELSE CoordCount(Branch(IntersectLine([ID][Point]), 0)) - 1

    END AS [Index],

    [Point]

FROM [Drawing] 

WHERE IsLine([ID])

SPLIT BY Coords([ID]AS [Point];

I want to highlight the contribution in particular and the concept in general. This contribution is so useful that it deserves its own thread so that more people can find it and use it. The general concept is that for every (complex) type there is a possible representation (or many) of it in terms of table(s).

One possible representation of Line may be TABLE [Line] ([Index] Integer, [P] Point ) with accompanying constraint that [Index] is unique and min([Line],[Index]) = 1 and max([Line],[Index]) = count([Line]).

It is necessary that such representation in table form is available for user writing queries, because it gives the information which is ‘inside’ a complex value out to the query language (the wonderful world of JOIN et al).

Two points here. First, SPLIT BY Coords(Geom) is useful already and praise to Manifold for that. Second, it’s not enough - there should be ‘split’ operators giving full representation (and also corresponding aggregate operators for generating complex values out of ‘rows and cols’).

An example - In Oracle, there is function SDO_UTIL.GETVERTICES(SDO_GEOMETRY) which returns table of coordinates (not Points) with sequential number TABLE (x NUMBER, y NUMBER, z NUMBER, w NUMBER, id NUMBER). One can use it to similar effect as SPLIT BY Coords(Geom). Please note that while SDO_UTIL.GETVERTICES gives the index, its only good as representation for simple i.e. not multipart SDO_GEOMETRY’s. That must be kept in mind when it matters.

A completely different example might be a ‘split’ operator for Text type - TokensAndSeparators(Text, Separators) which, given arguments “/John*/*Paul/*George*Ringo” and "*/", returns

([Start position], [Text], [Is token])

(1, “/”, False)

(2, “John”,True)

(5, “*/*”,False)

(8, “Paul”,True)

(12, “/*”, False)

(14, “George”,True)

(20, “*”, False)

(21, “Ringo”,True)

Or Chars(Text) whose result should be obvious now.

Note: The term possible representation and in fact the roots of all these ideas come from Date&Darwen

menicoll6 post(s)
#30-Nov-15 00:14

I have adapted the above SQL as follows:

-- List all Geo Locations of points of a (poly)line

SELECT [ID],[Index],Point_Geo,

CentroidY(Point_Geo) as Lat,

CentroidX(Point_Geo) as Lon

INTO LineLatLons_Seg1184

FROM

(SELECT

[ID],

CASE [Point] = StartPoint([ID])

WHEN TRUE THEN 0

ELSE CoordCount(Branch(IntersectLine([ID], [Point]), 0)) - 1

END AS [Index],

Project([Point], CoordSys("Latitude / Longitude")) AS [Point_Geo]

FROM [CHRIS_LKI_201407 Table]

WHERE NE_UNIQUE = "1184"

SPLIT BY Coords([ID]) AS [Point]

);

... to get a table of all the points in a line.

I thought it was working nicely but have discovered that it occasionally produces a duplicate [Index].

E.g. I had a line with 500 inflexion points, and index 360 was duplicated.

The two points with index 360 have lat/lon locations which are about 40 metres apart.

I can't say as I understand exactly how "CoordCount(Branch(IntersectLine([ID], [Point]), 0)) - 1" works!

Is there now a simpler way to achieve this?

I imported the line data from a shapefile. Is it possible that the problem is due to an error in how the line is defined in the shapfile?

tjhb
10,094 post(s)
#30-Nov-15 01:41

I think the problem is very likely to be that (a) the source data includes lines with redundant vertices, that is, vertices whose removal would not change the shape of the line, since they fall on a straight line between the vertices either side of them; whereas (b) IntersectLine() removes redundant vertices before proceeding to its other work.

The two vertices following a redundant vertex will thus appear to have the same number of antecedent vertices (because they have the same number of non-redundant antecedent vertices), and therefore receive the same index value according to

CoordCount(Branch(IntersectLine([ID][Vertex]), 0)) - 1

This currently requires a workaround. Removing all redundant vertices in advance is one of them. (How?)

menicoll6 post(s)
#30-Nov-15 18:40

Thanks, that is it! The (Albers) Y values of the duplicate index=360 points, and the Y value of the previous point are identical.

As a work-around I think I'll write a VBscript to reset the Index in the resulting table to the row number. (I only have to do this a few times!)

I need the Index to be unique so that the following distance-between-points calculation will work:

SELECT A.ID, A.Index, A.Lat, A.Lon,

DistanceEarth(A.Point_Geo, B.Point_Geo,"m") As LengthTo,

B.Index

FROM LineLatLons_Seg1184 AS A

LEFT JOIN LineLatLons_Seg1184 AS B

ON A.Index - 1 = B.Index

ORDER BY A.Index;

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