Last active
May 23, 2025 14:49
Revisions
-
robshep revised this gist
Jul 24, 2017 . 2 changed files with 70 additions and 3 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -14,9 +14,20 @@ DECLARE e100km integer; n100km integer; easting varchar; northing varchar; BEGIN parts := regexp_matches(grid_ref, '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{5}) ?([0-9]{5})$'); if parts is null or array_length(parts, 1) = 0 then parts := regexp_matches(grid_ref, '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{4}) ?([0-9]{4})$'); end if; if parts is null or array_length(parts, 1) = 0 then parts := regexp_matches(grid_ref, '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{3}) ?([0-9]{3})$'); end if; -- abandon.. not a grid ref if parts is null or array_length(parts, 1) = 0 then RAISE NOTICE 'Invalid grid reference: %', grid_ref; return null; end if; -- // get numeric values of letter references, mapping A->0, B->1, C->2, etc: -- var l1 = gridref.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0); This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,56 @@ -- convert SRID:27700-based OSGB36 coordinate point geometry to a OS Grid Reference E.g. SH123456 -- standing on the shoulders of... -- http://www.movable-type.co.uk/scripts/latlong-os-gridref.html -- https://github.com/chrisveness/geodesy/blob/master/osgridref.js [MIT] -- consider this MIT licensed also. CREATE OR REPLACE FUNCTION get_grid_ref_from_geom(geom public.geometry) RETURNS character varying LANGUAGE plpgsql AS $function$ DECLARE e integer; n integer; e100k integer; n100k integer; l1 integer; l2 integer; letterPair varchar; begin e := ST_X(geom)::integer; n := ST_Y(geom)::integer; -- // get the 100km-grid indices e100k := floor(e/100000); n100k := floor(n/100000); -- // translate those into numeric equivalents of the grid letters -- var l1 = (19-n100k) - (19-n100k)%5 + Math.floor((e100k+10)/5); -- var l2 = (19-n100k)*5%25 + e100k%5; l1 := (19-n100k) - (19-n100k)%5 + floor((e100k+10)/5); l2 := (19-n100k)*5%25 + e100k%5; -- // compensate for skipped 'I' and build grid letter-pairs -- if (l1 > 7) l1++; -- if (l2 > 7) l2++; IF l1 > 7 THEN l1 := l1 + 1; END IF; IF l2 > 7 THEN l2 := l2 + 1; END IF; -- var letterPair = String.fromCharCode(l1+'A'.charCodeAt(0), l2+'A'.charCodeAt(0)); letterPair := chr( ascii('A') + l1 ) || chr( ascii('A') + l2 ); -- // strip 100km-grid indices from easting & northing, and reduce precision -- e = Math.floor((e%100000)/Math.pow(10, 5-digits/2)); -- n = Math.floor((n%100000)/Math.pow(10, 5-digits/2)); e := floor(e%100000); n := floor(n%100000); -- // pad eastings & northings with leading zeros (just in case, allow up to 16-digit (mm) refs) -- e = ('00000000'+e).slice(-digits/2); -- n = ('00000000'+n).slice(-digits/2); -- return letterPair + ' ' + e + ' ' + n; return letterPair || e || n; END $function$ -
robshep created this gist
Jul 21, 2017 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,50 @@ -- convert a grid-reference E.g. SH123456 into a northing easting geometry for postgis with SRID=27700 (OSGB36) -- standing on the shoulders of... -- http://www.movable-type.co.uk/scripts/latlong-os-gridref.html -- https://github.com/chrisveness/geodesy/blob/master/osgridref.js [MIT] -- consider this MIT licensed also. CREATE OR REPLACE FUNCTION get_geom_from_grid_ref(IN grid_ref character varying) RETURNS public.geometry LANGUAGE 'plpgsql' AS $BODY$ DECLARE parts text[]; l1 integer; l2 integer; e100km integer; n100km integer; easting varchar; northing varchar; BEGIN parts := regexp_matches(grid_ref, '([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{3,5}) ?([0-9]{3,5})'); IF parts is null or array_length(parts, 1) = 0 THEN return null; END IF; -- // get numeric values of letter references, mapping A->0, B->1, C->2, etc: -- var l1 = gridref.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0); -- var l2 = gridref.toUpperCase().charCodeAt(1) - 'A'.charCodeAt(0); -- // shuffle down letters after 'I' since 'I' is not used in grid: -- if (l1 > 7) l1--; -- if (l2 > 7) l2--; l1 := ascii(parts[1]) - ascii('A'); l2 := ascii(parts[2]) - ascii('A'); IF l1 > 7 THEN l1 := l1 - 1; END IF; IF l2 > 7 THEN l2 := l2 - 1; END IF; -- // convert grid letters into 100km-square indexes from false origin (grid square SV): -- var e100km = ((l1-2)%5)*5 + (l2%5); -- var n100km = (19-Math.floor(l1/5)*5) - Math.floor(l2/5); e100km := ((l1-2)%5)*5 + (l2%5); n100km := (19-floor(l1/5)*5) - floor(l2/5); IF (e100km<0 or e100km>6 or n100km<0 or n100km>12) THEN RAISE EXCEPTION 'Invalid grid reference: %', grid_ref; END IF; easting := e100km::varchar || rpad(parts[3], 5, '0'); northing := n100km::varchar || rpad(parts[4], 5, '0'); return public.ST_GeomFROMEWKT('SRID=27700;POINT(' || easting || ' ' || northing || ')'); END $BODY$;