How to convert UK grid to coordinates in PostgreSQL?

Question

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:

what do your grid references look like? what sort of coordinates do you want? — Ian Turton, Jan 9, 2020 at 12:06
@IanTurton I think he wants to convert the XY123456 to numeric X,Y values. — Erik, Jan 9, 2020 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, 2020 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, 2020 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, 2020 at 13:49

vinh · Accepted Answer · 2020-02-14 16:20:25Z

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.

Ian Turton · Accepted Answer · 2020-01-09 16:45:02Z

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);
    }
  }
}

BeaR · Accepted Answer · 2020-01-09 13:13:57Z

-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.

answered Jan 9, 2020 at 13:13

BeaR

893 bronze badges

This is not related to my question at all
– Luffydude
Jan 9, 2020 at 13:28

Add a comment |

Stack Exchange Network

How to convert UK grid to coordinates in PostgreSQL?

3 Answers 3

Your Answer

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

Hot Network Questions

How to convert UK grid to coordinates in PostgreSQL?

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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