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
|