Creating GIS data from an online table

03/25/2022 4 Minutes Read

Data: https://en.wikipedia.org/wiki/List_of_power_stations_in_England if you want to follow along.

Step 1 — Split out the coordinates

Go to the link (above), then copy & paste the table into Excel (or Google sheets), then the next step is to remove the merged cells from the header.

Coordinates Table — **Figure 1: The pasted table should look like this**

Insert a column next to the coordinate field (when we split it, the new column needs to go somewhere).

The next step is to split the coordinates into separate columns. We also need to convert the coordinates from DMS (Degrees, Minutes, Seconds) to decimal degrees so that they can be easily read by a GIS software.

First is to split the column into eastings and northings. For this case, the letter ‘N’ provides a natural place to split the columns, these are UK-based coordinates and should all be north of the equator.

It is important that before you split the columns into further columns, there is space to expand to the right so that you don’t overwrite existing data. It is good practice to insert a column for each new column you aim to create.

Data Text Colums — **Figure 2: Go to ‘Data’ and then ‘text to columns’**

In Excel, the text to columns function can be found within the ‘data’ option, as in Figure 2. Here you should select the ‘delimit by character’ then tick the ‘other’ box and enter the lever ‘N’ as in Figure 3.

Use ‘N’ to delimit.

**Figure 3: Entering N provides a split as shown in the data preview window**

Step 2: Convert DMS to DD

Add another six columns, three to the right of the northings, and three to the right of the eastings.

Then, delimit by the degree symbol (°) and then (′) using the method shown above (Figures 2 & 3).

Delimited Columns — **Figure 4: What the resulting table should look like once the columns are delimited**

As we know, decimal degrees are derived by dividing the seconds by 3,600 (There are 3,600 seconds in a degree), the minutes by 60 (there are 60 minutes in a degree) and adding them together to the degrees to give a decimal degree. The formula looks like this:

dd = d + m/60 + s/3600

So, for the decimal northings in H3, the formula which needs putting into H3 is: =E3+(F3/60)+(G3/3600)

You can still apply the formula to H2 and will do no harm as it is already in decimal degrees. To save time you can then fill the formula to the bottom of the column, all the decimal northings should populate if the formatting is okay.

Next the eastings in column L needs calculating using columns I (degrees), J (Minutes) and K (Seconds).

For those eastings which are west of the meridian, you need to multiply by -1. So, to calculate the eastings in L3 you have to use: =-1*[I3 + (J3/60) + (K3/3600)].

Calculating Negative Decimal Degrees — **Figure 5: Calculating the negative decimal degrees in L3**

You don’t need to, but with a little bit of formatting, removing the underline, and changing the text color to black, then changing the columns to numbers, we have the final coordinates ready for GIS. Note that I have renamed the columns so that they are easy to pick up. Save the sheet as comma delimited csv.

Step 3: Showing the data in GIS

In Arcmap – find the ‘add xy data’ tool (add as a tool from the customize options if necessary).

The approach for adding spreadsheet coordinate data is the same in ArcMap and ArcGIS Pro, therefore follow the instructions below for ArcMap.

In ArcGIS Pro, go to the ‘add data’ icon and then choose ‘add xy point data’ add the fields, set the coordinate system to wgs84.

ArcGIS Pro Interface — **Figure 7: The ‘add xy data’ interface in ArcGIS Pro, identical to that in ArcMap**

QGIS is even easier to add the data by spreadsheet: simply add a new delimited text layer! To do this in QGIS go to the layer tab and at the dropdown choose ‘add delimited layer’ and import the csv
Set the easting and northing columns as geometry and coordinate system as wgs84 to ensure the points draw.