So just before Christmas I received my own “hand-crafted”, “unique”, and “hella-beautiful” integer. From Brooklyn Integers: 404578811.
I was not initially sure what to do with it. But I had some time while waiting on points to appear in polygons so I though I would map my integer.
So we have a few combinations for co-ordinates:
And of course:
I have to say these do not translate very well into my current de facto co-ordinate system of British National Grid (0, 404578811 and 404578811, 0 and 404578811, 404578811 not shown).
Luckily we can try some alternatives. Smathermather had a great post about mapping Null Island/Archipelago, so we can re-use some code. To map our integers to a selection of CRSs.
CREATE OR REPLACE FUNCTION integer_mapping2(
RETURNS geometry AS
SELECT ST_Transform(ST_SetSRID(ST_MakePoint(x,y),crs), 4326) INTO BODY;
WHEN SQLSTATE 'XX000' THEN
We use the EXCEPTION, because some of the more awkward co-ordinate combinations cannot be translated back to EPSG:4326 without causeing a SQL error.
Then we can create our our tables.
CREATE TABLE my_integer_ AS
SELECT srid, integer_mapping2(srid, 404578811,404578811) as geom FROM spatial_ref_sys
WHERE srid > 2000 AND srid < 4904;
Replacing 404578811_404578811 as needed.
It really shows how fragmented the EPSG codes are for New Zealand, and to a lesser extent the US.