0

We have this dataset with over >500 areas for which polygons are made. We don't know with which GPS these coordinates are registered. What we have is the Excel file with two columns.

Cell A: "area number" (1 - 500) &
Cell B: "Coordinates". 

R programming cannot read the xlx file.

Now we found this solution to make it a WKT file by rewriting cell B.

Coordinates:

(old) 5.72567034 -3.61719203,5.72567034 -3.61719203,5.72565126 -3.61720133, 5.72560167 -3.61723351,5.72557211 -3.61725235,5.72553444 -3.61727738, 5.72551298 -3.61730671,5.72548532 -3.61734009,5.72545910 -3.61736774, 5.72544241 -3.61739993,5.72542143 -3.61742473,5.72539997 -3.61745596, 5.72539568 -3.61750293,5.72540045 -3.61755085,5.72541189 -3.61760020, 5.72540236 -3.64654756

(new) POLYGON ((

5.72567034 -3.61719203,5.72567034 -3.61719203,5.72565126 -3.61720133, 5.72560167 -3.61723351,5.72557211 -3.61725235,5.72553444 -3.61727738, 5.72551298 -3.61730671,5.72548532 -3.61734009,5.72545910 -3.61736774, 5.72544241 -3.61739993,5.72542143 -3.61742473,5.72539997 -3.61745596, 5.72539568 -3.61750293,5.72540045 -3.61755085,5.72541189 -3.61760020, 5.72540236 -3.64654756

(new) ,5.72567034 -3.61719203))

How can I copy and paste the first coordinates behind the last coordinates, and how to write POLYGON (( in front and )) behind all of it?

Doing this manually for all in Excel is impossible.

closed as unclear what you're asking by BERA, Mark Ireland, Andre Silva, aldo_tapia, nmtoken Aug 31 '18 at 5:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Could you post some sample data or it is not possible to answer you question. This can be done using python pandas module which can read excel files. – BERA Aug 30 '18 at 5:18
1

when I understand you right, its a simple task in Excel: Assuming you have the list coors in column B enter the following formula e.g. in column c or any other column: =VERKETTEN("POLYGON((";B1;"))") (german Excel) or if you have an english Excel: =CONCATENATE("POLYGON((",B1,"))")

copy the formula downwards, finished!

UPDATE:

when you use this formula like it should do what you want:

=VERKETTEN("POLYGON((";B1;",";LINKS(B1;SUCHEN(",";B1)-1);"))")

gives: POLYGON((5.72567034 -3.61719203,5.72567034 -3.61719203,5.72565126 -3.61720133, 5.72560167 -3.61723351,5.72557211 -3.61725235,5.72553444 -3.61727738, 5.72551298 -3.61730671,5.72548532 -3.61734009,5.72545910 -3.61736774, 5.72544241 -3.61739993,5.72542143 -3.61742473,5.72539997 -3.61745596, 5.72539568 -3.61750293,5.72540045 -3.61755085,5.72541189 -3.61760020, 5.72540236 -3.64654756,5.72567034 -3.61719203))

explanation: you also need to look for the first postion of a comma and add the the text before this first comma as last point

  • Thank you very much, that is indeed a simple task. But its only part one. Because in the first now i also want to copy the first X / Y coordinates from the cell B1 and copy it behind the last X / Y coordinates, this should be resulting that the first and last of coordinates of each row in cell b are the same. actually i would like to add at the end of each cell: row 1: ,5.72567034 -3.61719203 row 2: ,6,4646464 -4.75686876 row 3: ,4,4654654 -3.578987654 these coordinates are the first of each row in column b – Nils de Boer Jul 30 '18 at 19:47
  • added a new forulma, which should do what you want – Kurt Jul 30 '18 at 21:01
  • Did The formula Work for you? – Kurt Jul 31 '18 at 13:00
  • Dear kurt, Yes, but i only was able to create 30 polygons out of the 500 polygons i needed. via QGIS or other methods. now trying to figure out how this comes. if you know how come please let me know. Kind regards, – Nils de Boer Aug 1 '18 at 13:25
  • 1
    Can you post the data? – Kurt Aug 1 '18 at 20:55
1

Excel has text functions that could be used (Find, Search, Left, etc.) to get the first two coordinate values. Since this seems to be numbers of the same length, you could find the (( and count the characters after that, then concatenate it to the end of the list and add the closing )).

But my preference would be to use the Data/Text to Columns tool on Column B (with space as delimiter and writing to Column C so you don't lose your original data). This would put each value in a separate column, eliminating the need to use text functions. Your first two coordinate values would always be in the same columns, so they can just be referenced by column/row. It is a little less automated, but super simple.

Not the answer you're looking for? Browse other questions tagged or ask your own question.