Hi all,

The purpose is to make distance labels along roads. Unfortunately, I cannot seem to make the join work to recreate in version 9 the SQL in this thread.

Attached is map file with a simple road sample and the query, which contains notes detailing what works and doesn't. It seems close. Appreciate any help if you are interested.

Attachments:
mile marker maker.map

Your SQL clauses' order was off.

--sql9

SELECT 

 SPLIT CALL SplitFunc([Geom], @unitXY, 0, 100000, 5280) as [Geom]

 ,

 v 

FROM

 [RoadT]

 INNER JOIN

 (select v from CALL f() ) as x

 ON (x.[v])*5280 < [RoadT].[length_feet]

;

Thanks for setting me straight! Also, I was using the wrong function. It works using this:

SELECT   GeomMakePoint(GeomCoordLine([geom], (x.v)*5280)) as [Geom], v, name

FROM RoadT 

left JOIN

(VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),

(11), (12), (13), (14), (15), (16), (17), (18), (19), (20),

(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),

(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),

(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),

(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),

(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),

(71), (72), (73), (74), (75), (76), (77), (78), (79), (80),

(81), (82), (83), (84), (85), (86), (87), (88), (89), (90)

AS (v)) as x 

ON (x.v)*5280 < [Length_feet]

Attachments:
mile marker maker2.map

An improvement would be the replacement of the list with hard coded values by something like this:

CALL ValueSequence(1, 90, 1)

Map Publishing Platform - Next Generation

Nice Chris. Thanks! It's added to the map project below.

Attachments:
mile marker maker3.map

You could also you the build in transform function to create a point every 1609 meters: Transform, Split, Line coordinates...

--sql9

-- $manifold$

--

-- Auto-generated

--

-- Split

--   Layer: Road

--   Field: Geom

--   Split into: line coordinates

--   Start: 0

--   End: 1000000

--   Step: 1609

--   Unit: Meter

--   Result: (new table)

--   Result type: geom

--   New drawing: Marker

--   New table: Marker Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

-- prepare begin

CREATE TABLE [Marker Table] (

  [mfd_id] INT64,

  [Name] NVARCHAR,

  [Label] NVARCHAR,

  [Type] NVARCHAR,

  [Community] NVARCHAR,

  [Date] DATETIME,

  [Alias] NVARCHAR,

  [Length_feet] INT32,

  [Length_miles] FLOAT64,

  [Label-Style] NVARCHAR,

  [l] FLOAT64,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([RoadT], 'Geom')

);

CREATE DRAWING [Marker] (

  PROPERTY 'Table' '[Marker Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

-- prepare end

FUNCTION SplitFunc(@geom GEOM, @scaleXY FLOAT64X2, @begin FLOAT64, @end FLOAT64, @step FLOAT64) TABLE AS (

  SELECT GeomScale(GeomMakePoint([xy]), @scaleXY)

  FROM CALL GeomToCoordsLineSequence(GeomScaleRev(@geom, @scaleXY), @begin, @end, @step)

) END;

VALUE @system NVARCHAR = ComponentFieldCoordSystem([RoadT], 'Geom');

VALUE @systemScaleXY FLOAT64X2 = CoordSystemScaleXY(@system);

VALUE @unitDegMeter FLOAT64 = CoordUnitScale(CoordUnitByName('Meter'));

VALUE @unitX FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 0);

VALUE @unitY FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 1);

VALUE @unitXY FLOAT64X2 = VectorMakeX2(@unitX, @unitY);

DELETE FROM [Marker Table];

INSERT INTO [Marker Table] (

  [Name], [Label], [Type], [Community], [Date], [Alias], [Length_feet], [Length_miles], [Label-Style], [l],

  [Geom]

) SELECT

  [Name], [Label], [Type], [Community], [Date], [Alias], [Length_feet], [Length_miles], [Label-Style], [l],

  SPLIT CALL SplitFunc([Geom], @unitXY, 0, 1000000, 1609)

FROM [RoadT];

Map Publishing Platform - Next Generation

Yes and that was my original method, but it doesn't provide an incrementing label for the marker.

I use mfd_id for that. Works for me.

Map Publishing Platform - Next Generation

Good. Looks like using that method puts the first point is at the beginning of the line and it doesn't work for a road network where you want numbering to start over for each line.