Skip to main content
  • EN

I am trying to think through the best way to approach this in FME. I need to create polylines from a CSV file. I have two files in the following format:

 

File1 contains one row per line, including the start and end coordinates for all lines:

Line123, StartX, StartY, EndX, EndY

Line456, StartX, StartY, EndX, EndY

 

File2 may or may not have additional midpoints. Each midpoint is on a new row. So two midpoints for Line123 would look like:

 

Line123, X1, Y1

Line123, X2, Y2

 

I need to combine these in the correct order to rebuild the line. For each unique LineID, I need eStartXY] + Midpoints] + ]EndXY], and the midpoints need to be in the same order as originally presented.

 

There are likely many ways to approach this. Here is my first attempt. I know I can use LineBuilder to construct any midpoints from the second file. It's set to wait for all inputs and process at the end, grouping by LineID. I know the order of vertices depends on the input.

 

image 

 

I want the LineBuilder to process the span START, then the midpoints, then the END. I bring the start and end into the LineBuilder. But now I'm realizing I don't have control over execution order. I can't guarantee I'll get points in the order I want. Now I'm trying to brainstorm and think about how to restructure...

image

Use point ID's. Give the start point for each line number 0. Give the end point number 99 (or 999). Give the points from File2 number 1, 2, 3 and so on. Sort the points, and build your line.

Use point ID's. Give the start point for each line number 0. Give the end point number 99 (or 999). Give the points from File2 number 1, 2, 3 and so on. Sort the points, and build your line.

I thought about that, but I'm not sure how to approach the collecting and sorting.

 

Starting with the lines in File 2 - if I was in SQL, I would use "row_number OVER partition by" queries. How can this be accomplished in FME?

 

Line123, X1, Y1 -- 1

Line123, X2, Y2 -- 2

Line456, X1, Y1 -- 1

Line456, X2, Y2 -- 2

 

Edit: Nevermind for the moment - the Counter transformer looks like a good option.

Use point ID's. Give the start point for each line number 0. Give the end point number 99 (or 999). Give the points from File2 number 1, 2, 3 and so on. Sort the points, and build your line.

Yes, the Counter should help. Something like this:

CreateLine

Hi @mlauer​ ,

a possible way I can think of is, create GeoJSON "LineString" texts from the two CSV tables for each line ID with JSONTemplater and generate lines from the resulting GeoJSON texts with GeometryReplacer.

See the attached screenshot and these expressions for the JSONTemplater.

Root Template Expression:
{
    "type" : "LineString",
    "coordinates" :  
        L
            xs:double(fme:get-attribute("StartX")),
            xs:double(fme:get-attribute("StartY"))
        ],
        fme:process-features("SUB"),
        t
            xs:double(fme:get-attribute("EndX")),
            xs:double(fme:get-attribute("EndY"))
        ]
    ]
}
 
Sub Template Expression:
 
    xs:double(fme:get-attribute("X")),
    xs:double(fme:get-attribute("Y"))
]

create_line_from_csv 

Hi @mlauer​ ,

a possible way I can think of is, create GeoJSON "LineString" texts from the two CSV tables for each line ID with JSONTemplater and generate lines from the resulting GeoJSON texts with GeometryReplacer.

See the attached screenshot and these expressions for the JSONTemplater.

Root Template Expression:
{
    "type" : "LineString",
    "coordinates" :  
        L
            xs:double(fme:get-attribute("StartX")),
            xs:double(fme:get-attribute("StartY"))
        ],
        fme:process-features("SUB"),
        t
            xs:double(fme:get-attribute("EndX")),
            xs:double(fme:get-attribute("EndY"))
        ]
    ]
}
 
Sub Template Expression:
 
    xs:double(fme:get-attribute("X")),
    xs:double(fme:get-attribute("Y"))
]

create_line_from_csv 

Interesting, and using only 2 transformers!

Hi @mlauer​ ,

a possible way I can think of is, create GeoJSON "LineString" texts from the two CSV tables for each line ID with JSONTemplater and generate lines from the resulting GeoJSON texts with GeometryReplacer.

See the attached screenshot and these expressions for the JSONTemplater.

Root Template Expression:
{
    "type" : "LineString",
    "coordinates" :  
        L
            xs:double(fme:get-attribute("StartX")),
            xs:double(fme:get-attribute("StartY"))
        ],
        fme:process-features("SUB"),
        t
            xs:double(fme:get-attribute("EndX")),
            xs:double(fme:get-attribute("EndY"))
        ]
    ]
}
 
Sub Template Expression:
 
    xs:double(fme:get-attribute("X")),
    xs:double(fme:get-attribute("Y"))
]

create_line_from_csv 

FeatureMerger has a capability to build a geometry from Supplier features, sometimes it's useful.create_line_from_csv_with_featuremerger

FeatureMerger has a capability to build a geometry from Supplier features, sometimes it's useful.create_line_from_csv_with_featuremerger

Very nice as well! You taught me something new today.

FeatureMerger has a capability to build a geometry from Supplier features, sometimes it's useful.create_line_from_csv_with_featuremerger

I want to thank everyone for their answers. I was able to solve this with the original suggestion, assigning my own counters and shoving everything through a Sorter before LineBuilder. It's conceptually easy to think through, which is good for when I share it with other team members.

 

I like the simple elegance of the JSON solution, though that's not an area I'm experienced with.

FeatureMerger has a capability to build a geometry from Supplier features, sometimes it's useful.create_line_from_csv_with_featuremerger

Your original concept - using LineBuilder is also a good solution of course, but I don't think the Counter and the Sorter are essential if you can make the workspace to read the File1 (Start X/Y, End X/Y) at first.

 

Make sure that the CSV reader for reading File1 is located upper than the reader for File2 in the Navigator window. Then, FME will read the File1 first, so you can build a line in the order of start node first and mid point(s) second with the LineBuilder and then finally add end node to the line with a VertexCreator.

See also the attached screenshot. 

create_line_from_csv_with_linebuilder 

Just be aware that the order of features may not be intended one when you run the workspace with Feature Cashing. It would be better to use Counter and Sorter if you would like to keep the order of features in a case too where you run the workspace with Feature Caching.

The next screenshot illustrates a workflow based on your original concept and geomancer's insight on Counter and Sorter.

create_line_from_csv_with_linebuilder_and_sorter

Reply


Community Stats

30,108
Posts
114,295
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.