Skip to main content

I have a excel file with 3 columns (attributes) and fourth is a coordinates (lat/long) (polygon) which are separated by space and have different amount of numbers after the dot.

Input:

ID, Name, Number, Coordinates

01, First, 1001, 27.94324 34.34834 27.013 34.560 27.43479125 34.96844321

Desirable Output:

01, First, 1001, 27.94324 34.34834

01, First, 1001, 27.013 34.560

01, First, 1001, 27.43479125 34.96844321

AttributeSplitter cant do it

How to automate it?

Thanks for help in advance.

The StringReplacer with this setting inserts commas (or any preferable delimiter) between pairs of lat/lon.

  • Attribute: Coordinates
  • Text to Match: (\\d+(\\.\\d*)?\\s+\\d+(\\.\\d*)?)\\s+
  • Replacement Text: \\1,
  • Use Regular Expressions: yes

You can then use a combination of the AttributeSplitter and ListExploder to get your desired table.


The StringReplacer with this setting inserts commas (or any preferable delimiter) between pairs of lat/lon.

  • Attribute: Coordinates
  • Text to Match: (\d+(\.\d*)?\s+\d+(\.\d*)?)\s+
  • Replacement Text: \1,
  • Use Regular Expressions: yes

You can then use a combination of the AttributeSplitter and ListExploder to get your desired table.

Another approach. The PythonCaller with this script may be effective.

class FeatureProcessor(object):
    def input(self,feature):
        coords = feature.getAttribute('Coordinates').split()
        for latlon in zip(coordst0::2], coordss1::2]):
            feature.setAttribute('Coordinates', '%s %s' % latlon)
            self.pyoutput(feature) 

Another way to do this:

Create a line geometry from the coordinate list. To do that follow the next steps:

1) create a new attribute "geometry" using a concatenation in the text editor:

<gml:LineString srsDimension="2"><gml:posList>

27.94324 34.34834 27.013 34.560 27.43479125 34.96844321

</gml:posList></gml:LineString>

2) Use the GeometryReplacer (encoding GML) to create a line object.

3) Use the Chopper (1 vertex per object) to create multiple point objects

4) If you need attributes use the CoordinateExtractor to get lat. and long. into attributes

That will give you the desired output without any coding.


The StringReplacer with this setting inserts commas (or any preferable delimiter) between pairs of lat/lon.

  • Attribute: Coordinates
  • Text to Match: (\\d+(\\.\\d*)?\\s+\\d+(\\.\\d*)?)\\s+
  • Replacement Text: \\1,
  • Use Regular Expressions: yes

You can then use a combination of the AttributeSplitter and ListExploder to get your desired table.

This simple regex works in this case.

  • Text to Match: (\\S+\\s+\\S+)\\s+

Reply