postgresql-hamradio/locator.sql at master · df7cb/postgresql-hamradio · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
-- locator

CREATE DOMAIN locator AS uctext
    CONSTRAINT valid_locator CHECK (VALUE ~ '^[A-R][A-R](?:[0-9][0-9](?:[A-X][A-X](?:[0-9][0-9](?:[A-X][A-X])?)?)?)?$');

CREATE OR REPLACE FUNCTION locator_as_linestring (loc locator) RETURNS text
STRICT LANGUAGE plpgsql
AS $$DECLARE
    a1 int := ascii(substr(loc, 1, 1)) - 65; -- field
    a2 int := ascii(substr(loc, 2, 1)) - 65;
    b1 int := ascii(substr(loc, 3, 1)) - 48; -- square
    b2 int := ascii(substr(loc, 4, 1)) - 48;
    c1 int := ascii(substr(loc, 5, 1)) - 65; -- subsquare
    c2 int := ascii(substr(loc, 6, 1)) - 65;
    d1 int := ascii(substr(loc, 7, 1)) - 48;
    d2 int := ascii(substr(loc, 8, 1)) - 48;
    e1 int := ascii(substr(loc, 9, 1)) - 65;
    e2 int := ascii(substr(loc, 10, 1)) - 65;
    lon_d double precision := 20;
    lat_d double precision := 10;
    lon double precision;
    lat double precision;
BEGIN
    lon := -180 + lon_d * a1;
    lat := -90 + lat_d * a2;
    IF b1 >= 0 THEN
        lon_d = 2;
        lat_d = 1;
        lon := lon + lon_d * b1;
        lat := lat + lat_d * b2;
        IF c1 >= 0 THEN
            lon_d = 2.0/24;
            lat_d = 1.0/24;
            lon := lon + lon_d * c1;
            lat := lat + lat_d * c2;
            IF d1 >= 0 THEN
                lon_d = .2/24;
                lat_d = .1/24;
                lon := lon + lon_d * d1;
                lat := lat + lat_d * d2;
                IF e1 >= 0 THEN
                    lon_d = .2/24/24;
                    lat_d = .1/24/24;
                    lon := lon + lon_d * e1;
                    lat := lat + lat_d * e2;
                END IF;
            END IF;
        END IF;
    END IF;
RETURN format('LINESTRING(%s %s,%s %s,%s %s,%s %s,%s %s)',
    lon, lat,
    lon + lon_d, lat,
    lon + lon_d, lat + lat_d,
    lon, lat + lat_d,
    lon, lat);
END$$;

CREATE OR REPLACE FUNCTION ST_Locator(loc locator) RETURNS geometry(POLYGON, 4326)
STRICT LANGUAGE SQL
AS $$SELECT ST_Polygon(ST_GeomFromText(locator_as_linestring(loc)), 4326)$$;

CREATE OR REPLACE FUNCTION locator_as_point (loc locator) RETURNS text
STRICT LANGUAGE plpgsql
AS $$DECLARE
    a1 int := ascii(substr(loc, 1, 1)) - 65; -- field
    a2 int := ascii(substr(loc, 2, 1)) - 65;
    b1 int := ascii(substr(loc, 3, 1)) - 48; -- square
    b2 int := ascii(substr(loc, 4, 1)) - 48;
    c1 int := ascii(substr(loc, 5, 1)) - 65; -- subsquare
    c2 int := ascii(substr(loc, 6, 1)) - 65;
    d1 int := ascii(substr(loc, 7, 1)) - 48;
    d2 int := ascii(substr(loc, 8, 1)) - 48;
    e1 int := ascii(substr(loc, 9, 1)) - 65;
    e2 int := ascii(substr(loc, 10, 1)) - 65;
    lon_d double precision := 20;
    lat_d double precision := 10;
    lon double precision;
    lat double precision;
BEGIN
    lon := -180 + lon_d * a1;
    lat := -90 + lat_d * a2;
    IF b1 >= 0 THEN
        lon_d = 2;
        lat_d = 1;
        lon := lon + lon_d * b1;
        lat := lat + lat_d * b2;
        IF c1 >= 0 THEN
            lon_d = 2.0/24;
            lat_d = 1.0/24;
            lon := lon + lon_d * c1;
            lat := lat + lat_d * c2;
            IF d1 >= 0 THEN
                lon_d = .2/24;
                lat_d = .1/24;
                lon := lon + lon_d * d1;
                lat := lat + lat_d * d2;
                IF e1 >= 0 THEN
                    lon_d = .2/24/24;
                    lat_d = .1/24/24;
                    lon := lon + lon_d * e1;
                    lat := lat + lat_d * e2;
                END IF;
            END IF;
        END IF;
    END IF;
RETURN format('POINT(%s %s)',
    lon + lon_d/2, lat + lat_d/2);
END$$;

CREATE OR REPLACE FUNCTION ST_LocatorPoint(loc locator) RETURNS geometry(POINT, 4326)
STRICT LANGUAGE SQL
AS $$SELECT ST_PointFromText(locator_as_point(loc), 4326)$$;

-- locator tables

CREATE TABLE locator2 (
  field varchar(2) PRIMARY KEY,
  geom geometry(POLYGON, 4326) NOT NULL
);
INSERT INTO locator2
  SELECT chr(lon)||chr(lat), ST_Locator((chr(lon)||chr(lat))::locator)
  FROM generate_series(65, 82) lon, generate_series(65, 82) lat;
CREATE INDEX ON locator2 USING gist(geom);

CREATE TABLE locator4 (
  field varchar(4) PRIMARY KEY,
  geom geometry(POLYGON, 4326) NOT NULL
);
INSERT INTO locator4
  SELECT chr(lon)||chr(lat)||chr(lon2)||chr(lat2), ST_Locator((chr(lon)||chr(lat)||chr(lon2)||chr(lat2))::locator)
  FROM generate_series(65, 82) lon, generate_series(65, 82) lat,
       generate_series(48, 57) lon2, generate_series(48, 57) lat2;
CREATE INDEX ON locator4 USING gist(geom);