2

I have a very large file with UK grid references that I need to get coordinates from

I'm on a Mac so I cant seem to use the solution provided in this link https://digimap.edina.ac.uk/webhelp/os/data_information/os_data_issues/ng_converter.htm

Furthermore, since it's too large of a file, I can't just copy the entire grid column onto a txt file.

I was thinking the best solution would just be to import the whole file onto the database and hope that there's some function that will do this.

They look like the following:

enter image description here

12
  • what do your grid references look like? what sort of coordinates do you want?
    – Ian Turton
    Jan 9 '20 at 12:06
  • @IanTurton I think he wants to convert the XY123456 to numeric X,Y values.
    – Erik
    Jan 9 '20 at 12:22
  • @IanTurton hi again. I wanted to convert to 27700 but WGS84 is fine too. Just need a set of coordinates that i can work with
    – Luffydude
    Jan 9 '20 at 13:10
  • The OS publish excel conversion functions here if that is of use to you: https://www.ordnancesurvey.co.uk/documents/resources/maptile-gridref-conversion.xlsm
    – JimT
    Jan 9 '20 at 13:39
  • @JimT I'm clearly not an excel pro but is there a way I can copy their formula onto my spreadsheet? I just tried it and it returned #NAME
    – Luffydude
    Jan 9 '20 at 13:49
2

The OSGB national grid uses nested 5x5 squares and two sets of digits to provide a location (see https://en.wikipedia.org/wiki/Ordnance_Survey_National_Grid#Grid_digits):

For example, the grid reference of the 100 m square containing the summit of 
Ben Nevis is NN 166 712. (Grid references may be written with or without spaces;
e.g., also NN166712.) NN has an easting of 200 km and northing of 700 km, so the
OSGB36 National Grid location for Ben Nevis is at 216600, 771200. 

The following link has an example of code to convert from e.g. NN166712 to 216600, 771200 https://www.movable-type.co.uk/scripts/latlong-os-gridref.html

create table letters (id serial primary key, letters varchar(2), 
                      x int, y int);
-- add the valid letters for the main squares
insert into letters (letters) values (
    unnest(array['SV', 'SQ', 'SL', 'SF', 'SA', 'NV', 'NQ', 'NL', 'NF',
                 'NA', 'HV', 'HQ', 'HL', 'SW', 'SR', 'SM', 'SG', 'SB', 
                 'NW', 'NR', 'NM', 'NG', 'NB', 'HW', 'HR', 'HM', 'SX',
                 'SS', 'SN', 'SH', 'SC', 'NX', 'NS', 'NN', 'NH', 'NC',
                 'HX', 'HS', 'HN', 'SY', 'ST', 'SO', 'SJ', 'SD', 'NY',
                 'NT', 'NO', 'NJ', 'ND', 'HY', 'HT', 'HO', 'SZ', 'SU',
                 'SP', 'SK', 'SE', 'NZ', 'NU', 'NP', 'NK', 'NE', 'HZ',
                 'HU', 'HP', 'TV', 'TQ', 'TL', 'TF', 'TA', 'OV', 'OQ', 
                 'OL', 'OF', 'OA', 'JV', 'JQ', 'JL', 'TW', 'TR', 'TM',
                 'TG', 'TB', 'OW', 'OR', 'OM', 'OG', 'OB', 'JW', 'JR', 
                 'JM' ]));
-- set the basic x and y values
update letters set x = 8, y = 8 where left(letters,1) = 'H';
update letters set x = 9, y = 9 where left(letters,1) = 'J';
update letters set x = 13, y = 13 where left(letters,1) = 'N';
update letters set x = 14, y = 14 where left(letters,1) = 'O';
update letters set x = 18, y = 18 where left(letters,1) = 'S';
update letters set x = 19, y = 19 where left(letters,1) = 'T';
-- ToDo - multiply x, y, value by 100000 and add offset
-- Grid:
-- ABCDE
-- FGHJK -- No I
-- LMNOP
-- QRSTU
-- VWXYZ
-- A|F|L|Q|V -- x offset 0, y offset 0
-- B|G|M|R|W -- offset 20000, y offset 0

Then set up a query to split the NN166712 into 'NN', 166::integer, 712::integer and generate your grid value.

2

Here's a quick Java translation of the OS visual basic code from the spread sheet mentioned in the comments.

import org.locationtech.jts.geom.Coordinate;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.Point;

public class OSGridref {
  static GeometryFactory GF = new GeometryFactory();
  static String[][] gridLetters = { { "SV", "SQ", "SL", "SF", "SA", "NV", "NQ", "NL", "NF", "NA", "HV", "HQ", "HL" },
      { "SW", "SR", "SM", "SG", "SB", "NW", "NR", "NM", "NG", "NB", "HW", "HR", "HM" },
      { "SX", "SS", "SN", "SH", "SC", "NX", "NS", "NN", "NH", "NC", "HX", "HS", "HN" },
      { "SY", "ST", "SO", "SJ", "SD", "NY", "NT", "NO", "NJ", "ND", "HY", "HT", "HO" },
      { "SZ", "SU", "SP", "SK", "SE", "NZ", "NU", "NP", "NK", "NE", "HZ", "HU", "HP" },
      { "TV", "TQ", "TL", "TF", "TA", "OV", "OQ", "OL", "OF", "OA", "JV", "JQ", "JL" },
      { "TW", "TR", "TM", "TG", "TB", "OW", "OR", "OM", "OG", "OB", "JW", "JR", "JM" } };

  static public Point convertGridRef(String gridref) {
    String ref = gridref.toUpperCase();

    int easting = getEasting(ref);
    int northing = getNorthing(ref);
    return GF.createPoint(new Coordinate(easting, northing));
  }

  /**
   * @param first
   * @param second
   */
  public static int getEasting(String ref) {

    char first = ref.charAt(0);
    if (first != 'H' && first != 'J' && first != 'N' && first != 'O' && first != 'S' && first != 'T') {
      throw new IllegalArgumentException("Invalid grid reference starting letter " + ref);
    }
    char second = ref.charAt(1);
    if (second < 65 || second > 90 || second == 'I') {
      throw new IllegalArgumentException("Invalid grid reference second letter " + ref);
    }
    int len = ref.length();
    if (len < 2 || len > 12 || len % 2 != 0) {
      throw new IllegalArgumentException("Invalid grid reference wrong length " + ref);
    }
    boolean quadExists = false;
    String quad = "";
    if (len > 6 && (ref.endsWith("SW") || ref.endsWith("NW") || ref.endsWith("NE") || ref.endsWith("SE"))) {
      quadExists = true;
      quad = ref.substring(len - 2);
    }
    String numbs = ref;

    if (!quadExists && len > 2) {
      numbs = ref.substring(2);
    } else if (quadExists) {
      numbs = ref.substring(2, len - 2);
    }
    if (len > 2) {
      double coords;
      try {
        coords = Double.parseDouble(numbs);
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    }

    // convert to a coordinate

    String hundreds = Character.toString(first) + Character.toString(second);
    boolean match = false;
    int easting1st = 0;
    for (int x = 0; x < 6; x++) {
      for (int y = 0; y < 12; y++) {
        if (gridLetters[x][y].equalsIgnoreCase(hundreds)) {
          easting1st = x;
          match = true;
        }
      }
    }
    if (!match)
      throw new IllegalArgumentException("Invalid tile square " + hundreds);
    String eastings;
    int lenNum = numbs.length();

    switch (len) {
    case 2:
      // 100km square
      return 100_000 * easting1st;
    case 4:
      // 10km Square
      eastings = Integer.toString(easting1st) + numbs.substring(0, lenNum / 2);
      try {
        return Integer.parseInt(eastings) * 10_000;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 6:
      // 5 or 1km square
      String qDigit = "";
      if ("SW".equalsIgnoreCase(quad) || "NW".equalsIgnoreCase(quad)) {
        qDigit = "0";
      } else if ("SE".equalsIgnoreCase(quad) || "NE".equalsIgnoreCase(quad)) {
        qDigit = "5";
      }
      eastings = Integer.toString(easting1st) + numbs.substring(0, lenNum / 2) + qDigit;
      try {
        return Integer.parseInt(eastings) * 1000;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 8:
      // 500 or 100m square
      qDigit = "";
      if ("SW".equalsIgnoreCase(quad) || "NW".equalsIgnoreCase(quad)) {
        qDigit = "0";
      } else if ("SE".equalsIgnoreCase(quad) || "NE".equalsIgnoreCase(quad)) {
        qDigit = "5";
      }
      eastings = Integer.toString(easting1st) + numbs.substring(0, lenNum / 2) + qDigit;
      try {
        return Integer.parseInt(eastings) * 100;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 10:
      // 10m square
      eastings = numbs.substring(0, lenNum / 2);
      try {
        return Integer.parseInt(eastings) * 10;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 12:
      // 1m square
      eastings = numbs.substring(0, lenNum / 2);
      try {
        return Integer.parseInt(eastings);
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    default:
      throw new IllegalArgumentException("Unexpected input in getEasting " + ref);
    }
  }

  public static int getNorthing(String ref) {
    char first = ref.charAt(0);
    if (first != 'H' && first != 'J' && first != 'N' && first != 'O' && first != 'S' && first != 'T') {
      throw new IllegalArgumentException("Invalid grid reference starting letter " + ref);
    }
    char second = ref.charAt(1);
    if (second < 65 || second > 90 || second == 'I') {
      throw new IllegalArgumentException("Invalid grid reference second letter " + ref);
    }
    int len = ref.length();
    if (len < 2 || len > 12 || len % 2 != 0) {
      throw new IllegalArgumentException("Invalid grid reference wrong length " + ref);
    }
    boolean quadExists = false;
    String quad = "";
    if (len > 6 && (ref.endsWith("SW") || ref.endsWith("NW") || ref.endsWith("NE") || ref.endsWith("SE"))) {
      quadExists = true;
      quad = ref.substring(len - 2);
    }
    String numbs = ref;

    if (!quadExists && len > 2) {
      numbs = ref.substring(2);
    } else if (quadExists) {
      numbs = ref.substring(2, len - 2);
    }
    if (len > 2) {
      double coords;
      try {
        coords = Double.parseDouble(numbs);
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    }

    // convert to a coordinate

    String hundreds = Character.toString(first) + Character.toString(second);
    boolean match = false;
    int northing1st = 0;
    for (int x = 0; x < 6; x++) {
      for (int y = 0; y < 12; y++) {
        if (gridLetters[x][y].equalsIgnoreCase(hundreds)) {
          northing1st = y;
          match = true;
        }
      }
    }
    if (!match)
      throw new IllegalArgumentException("Invalid tile square " + hundreds);
    String northings;
    int lenNum = numbs.length();

    switch (len) {
    case 2:
      // 100km square
      return 100_000 * northing1st;
    case 4:
      // 10km Square
      northings = Integer.toString(northing1st) + numbs.substring(lenNum / 2);
      try {
        return Integer.parseInt(northings) * 10_000;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 6:
      // 5 or 1km square
      String qDigit = "";
      if ("SW".equalsIgnoreCase(quad) || "SE".equalsIgnoreCase(quad)) {
        qDigit = "0";
      } else if ("NW".equalsIgnoreCase(quad) || "NE".equalsIgnoreCase(quad)) {
        qDigit = "5";
      }
      northings = Integer.toString(northing1st) + numbs.substring(lenNum / 2) + qDigit;
      try {
        return Integer.parseInt(northings) * 1000;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 8:
      // 500 or 100m square
      qDigit = "";
      if ("SW".equalsIgnoreCase(quad) || "NW".equalsIgnoreCase(quad)) {
        qDigit = "0";
      } else if ("SE".equalsIgnoreCase(quad) || "NE".equalsIgnoreCase(quad)) {
        qDigit = "5";
      }
      northings = Integer.toString(northing1st) + numbs.substring(lenNum / 2) + qDigit;
      try {
        return Integer.parseInt(northings) * 100;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 10:
      // 10m square
      northings = numbs.substring(lenNum / 2);
      try {
        return Integer.parseInt(northings) * 10;
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    case 12:
      // 1m square
      northings = numbs.substring(lenNum / 2);
      try {
        return Integer.parseInt(northings);
      } catch (NumberFormatException e) {
        throw new IllegalArgumentException("bad numeric format in " + ref, e);
      }
    default:
      throw new IllegalArgumentException("Unexpected input in getEasting " + ref);
    }
  }
}
-1

Have you tried any tool which converts your coordinates? Here you have an example: https://www.asturnatura.com/sinflac/calculadora-conversiones-coordenadas.php Good luck.

1
  • This is not related to my question at all
    – Luffydude
    Jan 9 '20 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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