pwd
help
\?
CREATE TABLE ghg (
    key UUID PRIMARY KEY,
    adr UUID NOT NULL,
    par UUID,
    lev int
) PARTITION BY LIST (lev);
CREATE TABLE ghg (
    key UUID PRIMARY KEY,
    adr UUID NOT NULL,
    par UUID,
    lev int
) PARTITION BY LIST (lev);
CREATE TABLE ghg (
    key UUID PRIMARY KEY,
    adr UUID NOT NULL,
    par UUID key,
    lev int key
) PARTITION BY LIST (lev);
CREATE TABLE ghg (
    key UUID PRIMARY KEY,
    adr UUID NOT NULL,
    par UUID key,
    lev int
);
CREATE TABLE ghg (
    key UUID PRIMARY KEY,
    adr UUID NOT NULL,
    par UUID,
    lev int
);
desc ghg
;
show create table ghg;
insert into ghg (key, addr, par, lev) values (a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12, b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, null);
\df
\df h9_
\df h9_
\df h9
\df h9*
drop function h9_bin_batch;
drop function h9_decode_batch;
drop function h9_encode_batch;
\df h9*
SELECT * FROM h9_encode(-1.8262, 51.1789);
SELECT * FROM h9_encode(-1.8262, 51.1789);
SELECT * FROM h9_decode(h9_encode_adr(-1.8262, 51.1789));
SELECT * FROM h9_encode_adr(-1.8262, 51.1789);
select * from h9_decode('43521606-0150-0483-8250-003401502085', 4);
select UUID('43521606-0150-0483-8250-003401502085');
select * from h9_decode(43521606-0150-0483-8250-003401502085, 4);
select * from h9_decode(uuid('43521606-0150-0483-8250-003401502085'), 4);
select * from h9_decode(uuid('43521606-0150-0483-8250-003401502085'), smallint(4));
select * from h9_decode(uuid('43521606-0150-0483-8250-003401502085'), 4);
WITH enc AS (SELECT * FROM h9_encode_adr(-1.8262, 51.1789)) SELECT * FROM h9_decode(enc.hex9, enc.adr);
create temporary table foo SELECT * FROM h9_encode_adr(-1.8262, 51.1789);
SELECT into temporary table `foo` select * FROM h9_encode_adr(-1.8262, 51.1789);
SELECT into temporary table 'foo' select * FROM h9_encode_adr(-1.8262, 51.1789);
SELECT into temporary table foo select * FROM h9_encode_adr(-1.8262, 51.1789);
SELECT * FROM h9_encode_adr(-1.8262, 51.1789) into temporary table foo;
select into temporary table foo from h9_encode_adr(-1.8262, 51.1789);
select * from foo;
drop table foo;
select * into temporary table foo from h9_encode_adr(-1.8262, 51.1789);
select * from foo;
select * from h9_decode(select * from foo);
with x as (select * from foo) select * from h9_decode(x.hex9, x.adr);
with enc as (select * from foo)
with x as (select * from foo) select * from x;
with enc as (select * from foo)
with x as (select * from foo) select * from x;
;
with x as (select * from foo) select * from x;
select * into temporary table foo from h9_encode_adr(-1.8262, 51.1789);
with x as (select * from foo) select * from x;
with x as (select * from foo) select * h9_decode(x.hex9, x.adr);
with x as (select * from foo) select * from h9_decode(x.hex9, x.adr);
with x as (select * from foo) select * from h9_decode(x.hex9, x.adr);
select * from h9_encode_adr(-1.8262, 51.1789);
with hx as (select * from h9_encode_adr(-1.8262, 51.1789)) SELECT * FROM h9_decode(hx.hex9, hx.adr) from hx;
WITH hx AS (
    SELECT * FROM h9_encode_adr(-1.8262, 51.1789)
)
SELECT d.* FROM hx, h9_decode(hx.hex9, hx.adr) AS d;
select * into temporary table foo from h9_encode_adr(-1.8262, 51.1789);
drop table foo;
select * into temporary table foo from h9_encode_adr(-1.8262, 51.1789);
drop uk_bounds;
drop table uk_bounds;
select * from uk_bounds;
select * from uk_bounds;
drop table uk_bounds;
select * from uk_bounds;
drop table uk_bounds;
select * from uk_bounds;
select * from ukpoly;
select h9_encode(x.lons, x.lats) as hex_uuid from uk_bounds x;
create table uk_h9 as select h9_encode(x.lons, x.lats) from uk_bounds x;
select * from uk_h9;
\df h9_encode
drop table uk_h9;
CREATE TABLE uk_h9 AS SELECT  h.hex9, h.adr FROM uk_bounds x, h9_encode(x.lons, x.lats) h;
CREATE TABLE uk_h9 AS SELECT  h.uuid, h.adr FROM uk_bounds x, h9_encode(x.lons, x.lats) h;
CREATE TABLE uk_h9 AS SELECT hex9, adr FROM uk_bounds x, h9_encode(x.lons, x.lats);
CREATE TABLE uk_h9 AS SELECT hex_uuid, adr FROM uk_bounds x, h9_encode(x.lons, x.lats);
select * from uk_bounds x, h9_encode(x.lons, x.lats);
select h9.* from uk_bounds x, h9_encode(x.lons, x.lats) h9;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    
    # Returning dicts completely eliminates the "single record" bug
    return [{"hex9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
select h9.* from uk_bounds x, h9_encode(x.lons, x.lats) h9;
DROP TABLE IF EXISTS uk_h9;
CREATE TABLE uk_h9 AS  SELECT h9.hex9, h9.adr FROM uk_bounds x CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9;
CREATE TABLE uk_h9 AS SELECT h9.hex9, h9.adr FROM uk_bounds x CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9;
select *.h9 from uk_bounds x CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9
;
select *.h9 from uk_bounds x CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9;
select h9.* from uk_bounds x CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9;
\df h9_encode
WITH batched AS (
    SELECT 
        array_agg(lons) AS lon_array, 
        array_agg(lats) AS lat_array 
    FROM uk_bounds
)
-- 2. Pass the arrays to force Postgres to use the vectorized function
SELECT h9.hex9, h9.adr
FROM batched b
CROSS JOIN LATERAL h9_encode(b.lon_array, b.lat_array) AS h9;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [(str(u), int(a)) for u, a in zip(uuids, adrs)]
$$;
WITH batched AS (
    SELECT 
        array_agg(lons) AS lon_array, 
        array_agg(lats) AS lat_array 
    FROM uk_bounds
)
-- 2. Pass the arrays to force Postgres to use the vectorized function
SELECT h9.hex9, h9.adr
FROM batched b
CROSS JOIN LATERAL h9_encode(b.lon_array, b.lat_array) AS h9;
WITH b AS (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) SELECT h9.* from h9_encode(b.lon_array, b.lat_array) AS h9;
WITH (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) as b SELECT h9.* from h9_encode(b.lon_array, b.lat_array) AS h9;
from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) as b SELECT h9.* from h9_encode(b.lon_array, b.lat_array) AS h9;
SELECT h9.* from h9_encode(b.lon_array, b.lat_array) AS h9,(SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) as b; 
select b.* from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) as b;
select b.* from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds)  b;
SELECT h9.* from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b, h9_encode(b.lon_array, b.lat_array) h9;
SELECT h9.* from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b, h9_encode(b.lon_array, b.lat_array) h9;
select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo from h9.* in (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select from h9.* in (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select from h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select from h9.*  (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
\dt
select * from uk_bounds;
select * from foo;
insert into foo select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return {'h9':(str(u), 'adr':int(a)) for u, a in zip(uuids, adrs)}
$$;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [{"h9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
insert into foo select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
drop table foo;
select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(h9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [{"h9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
drop function h9_encode;
\df
\df h9_*
drop function h9_decodehex9s uuid[], adrs smallint[]
\df h9_*
drop function h9_decode hex9s uuid[], adrs smallint[]
\df h9_*
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(h9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [{"h9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(h9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [{"h9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
select h9.h9, h9.adr from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
\df h9_*
drop table h9_encode lons double precision[], lats double precision[];
drop function h9_encode lons double precision[], lats double precision[];
drop function h9_encode;
drop function h9_encode "lons double precision[], lats double precision[]";
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    return [{"hex9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
select h9.hex9, h9.adr from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
select h9.* from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
create table foo as select h9.hex9, h9.adr from (select h9_encode(b.lons, b.lats) from (SELECT array_agg(lons) as lons,array_agg(lats) as lats FROM uk_bounds) b) h9;
CREATE TABLE uk_h9 AS 
SELECT 
    h.hex9, 
    h.adr 
FROM 
    uk_bounds x, 
    h9_encode(x.lons, x.lats) h;
DROP TABLE IF EXISTS uk_h9;
CREATE TABLE uk_h9 AS 
SELECT 
    h.hex9, 
    h.adr 
FROM 
    uk_bounds x, 
    h9_encode(x.lons, x.lats) AS h(hex9, adr); -- This forces Postgres to register the columns!
CREATE OR REPLACE FUNCTION h9_encode(
    lons double precision[],
    lats double precision[]
) RETURNS TABLE(hex9 uuid, adr smallint)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_encode as _enc
    uuids, adrs = _enc(lats, lons)
    
    # Returning dicts completely eliminates the "single record" bug
    return [{"hex9": str(u), "adr": int(a)} for u, a in zip(uuids, adrs)]
$$;
CREATE TABLE uk_h9 AS 
SELECT 
    h.hex9, 
    h.adr 
FROM 
    uk_bounds x, 
    h9_encode(x.lons, x.lats) AS h(hex9, adr); -- This forces Postgres to register the columns!
CREATE TABLE uk_h9 AS 
SELECT 
    h9.hex9, 
    h9.adr 
FROM uk_bounds x
CROSS JOIN LATERAL h9_encode(x.lons, x.lats) AS h9;
CREATE TABLE uk_h9 AS 
SELECT 
    h.hex9, 
    h.adr 
FROM 
    uk_bounds x, 
    h9_encode(x.lons, x.lats) AS h(hex9, adr); -- This forces Postgres to register the columns!
DROP FUNCTION IF EXISTS h9_encode(double precision, double precision);
-- 1. Make sure the table doesn't already exist
DROP TABLE IF EXISTS uk_h9;
-- 2. Build it using the array aggregation
WITH batched AS (
    SELECT 
        array_agg(lons) AS lon_array, 
        array_agg(lats) AS lat_array 
    FROM uk_bounds
)
CREATE TABLE uk_h9 AS 
SELECT 
    h9.hex9, 
    h9.adr
FROM batched b
CROSS JOIN LATERAL h9_encode(b.lon_array, b.lat_array) AS h9;
-- 1. Make sure the table doesn't already exist
DROP TABLE IF EXISTS uk_h9;
-- 2. Create the table, with the CTE correctly placed inside!
CREATE TABLE uk_h9 AS 
WITH batched AS (
    SELECT 
        array_agg(lons) AS lon_array, 
        array_agg(lats) AS lat_array 
    FROM uk_bounds
)
SELECT 
    h9.hex9, 
    h9.adr
FROM batched b
CROSS JOIN LATERAL h9_encode(b.lon_array, b.lat_array) AS h9;
select * from uk_h9;
DROP TABLE IF EXISTS uk_h9;
DROP TABLE IF EXISTS uk_h9; CREATE TABLE uk_h9 AS SELECT h9.hex9, h9.adr from (SELECT array_agg(lons) AS lon, array_agg(lats) AS lat FROM uk_bounds) b, CROSS JOIN LATERAL h9_encode(b.lon, b.lat) h9;
DROP TABLE IF EXISTS uk_h9; CREATE TABLE uk_h9 AS SELECT h9.hex9, h9.adr from (SELECT array_agg(lons) AS lon, array_agg(lats) AS lat FROM uk_bounds) b, h9_encode(b.lon, b.lat) h9;
select * from uk_h9;
SELECT * FROM h9_encode(array_agg(lons), array_agg(lats)) from uk_bounds;
SELECT h9.* FROM uk_bounds.a, h9_encode(array_agg(a.lons), array_agg(a.lats)) h9;
\st
\dt
SELECT h9.* FROM uk_bounds a, h9_encode(array_agg(a.lons), array_agg(a.lats)) h9;
SELECT h9.* FROM  h9_encode(array_agg(uk_bounds.lons), array_agg(uk_bounds.lats)) h9;
SELECT h9.* FROM (select lons, lats from uk_bounds) a, h9_encode(array_agg(a.lons), array_agg(a.lats)) h9;
select h9_encode(array_agg(a.lons), array_agg(a.lats)) from uk_bounds a;
select h.hex9, h.adr from (h9_encode(array_agg(a.lons), array_agg(a.lats)) from uk_bounds a) h;
select h.hex9, h.adr from (select h9_encode(array_agg(a.lons), array_agg(a.lats)) from uk_bounds a) h;
select h.* from (select h9_encode(array_agg(a.lons), array_agg(a.lats)) from uk_bounds a) h;
select h9.hex9, h9.adr from (select array_agg(lons) lon, array_agg(lats) lat from uk_bounds) b, h9_encode(b.lon, b.lat) h9;
SELECT h9_bin(hex9, 15) AS bin, COUNT(*) AS n from uk_h9 group by bin order by n desc;
SELECT h9_bin(hex9, 5) AS bin, COUNT(*) AS n from uk_h9 group by bin order by n desc;
SELECT h9_bin(hex9, 3) AS bin, COUNT(*) AS n from uk_h9 group by bin order by n desc;
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (bin_str, f'SRID=4326;{wkt}')
        for bin_str, wkt in rows
    ]
$$;
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$;
SELECT bin, hex_geom FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
SELECT bin, hex_geom FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
SELECT bin, hex_geom FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(b.hex9, bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (bin_str, f'SRID=4326;{wkt}')
        for bin_str, wkt in rows
    ]
$$;
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (bin_str, f'SRID=4326;{wkt}')
        for bin_str, wkt in rows
    ]
$$;
DROP FUNCTION h9_bbox_hexagons(double precision,double precision,double precision,double precision,integer;
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.uuid_address import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (bin_str, f'SRID=4326;{wkt}')
        for bin_str, wkt in rows
    ]
$$;
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.hex9, b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$;
)
;
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.hex9, b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$;
DROP FUNCTION h9_fill_polygon(geometry,integer);
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.hex9, b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$;
SELECT bin, hex_geom FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
SELECT hex9, bin, hex_geom FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
SELECT *  FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.grid import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (hex9_str, bin_str, f'SRID=4326;{wkt}')
        for hex9_str, bin_str, wkt in rows
    ]
$$;
 DROP FUNCTION h9_bbox_hexagons(double precision,double precision,double precision,double precision,integer);
CREATE OR REPLACE FUNCTION h9_bbox_hexagons(
    min_lon double precision,
    min_lat double precision,
    max_lon double precision,
    max_lat double precision,
    layer   integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpython3u
AS $$
    from hhg9.h9.grid import h9_postgis_hexagons
    rows = h9_postgis_hexagons(min_lon, min_lat, max_lon, max_lat, layer)
    return [
        (hex9_str, bin_str, f'SRID=4326;{wkt}')
        for hex9_str, bin_str, wkt in rows
    ]
$$;
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.hex9, b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$
SELECT *  FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
;
;
SELECT *  FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
CREATE OR REPLACE FUNCTION h9_fill_polygon(
    geom  geometry,
    layer integer
) RETURNS TABLE(hex9 uuid, bin uuid, hex_geom geometry)
LANGUAGE plpgsql
AS $$
DECLARE
    env geometry;
BEGIN
    env := ST_Envelope(geom);
    RETURN QUERY
        SELECT b.hex9, b.bin, b.hex_geom
        FROM h9_bbox_hexagons(
            ST_XMin(env), ST_YMin(env),
            ST_XMax(env), ST_YMax(env),
            layer
        ) AS b
        WHERE ST_Intersects(b.hex_geom, geom);
END;
$$;
SELECT *  FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
SELECT *  FROM h9_fill_polygon(ST_GeomFromText('POLYGON((-1.87 51.16, -1.78 51.16, -1.78 51.20, -1.87 51.20, -1.87 51.16))', 4326), 8);
\df
\df h9_*
SELECT oid::regprocedure AS target_functions FROM pg_proc WHERE proname LIKE 'h9_%' AND pronamespace = 'public'::regnamespace;
DO $$
DECLARE func_rec record;
BEGIN
    FOR func_rec IN SELECT oid::regprocedure AS func_sig FROM pg_proc WHERE proname LIKE 'h9_%' AND pronamespace = 'public'::regnamespace
    LOOP EXECUTE 'DROP FUNCTION IF EXISTS ' || func_rec.func_sig || ' CASCADE;'; END LOOP;
END; $$;
;
SELECT oid::regprocedure AS target_functions FROM pg_proc WHERE proname LIKE 'h9_%' AND pronamespace = 'public'::regnamespace;
SELECT oid::regprocedure AS target_functions FROM pg_proc WHERE proname LIKE 'h9_%' AND pronamespace = 'public'::regnamespace;
SELECT oid::regprocedure AS target_functions FROM pg_proc WHERE proname LIKE 'h9_%' AND pronamespace = 'public'::regnamespace;
select * from ukpoly;
select geom from ukpoly where fid=1;
SELECT bin, hex_geom from (select geom from ukpoly where fid=1) p, h9_fill_polygon(p.geom,6);
SELECT bin, hex_geom from (select geom from ukpoly where fid=1) p, h9_fill_polygon(p.geom,6);
SELECT hex9, bin, hex_geom from (select geom from ukpoly where fid=1) p, h9_fill_polygon(p.geom,6);
\st
\dt
drop table uk_h9;
\dt
CREATE TABLE hex9_uk AS SELECT h.hex9, h.bin, h.hex_geom, 5 AS level FROM  (SELECT geom FROM ukpoly WHERE fid = 1) p, h9_fill_polygon(p.geom, 5) h;
\st
\gt
\dt
\dt
CREATE TABLE hex9_uk AS SELECT h.hex9, h.bin, h.hex_geom, 5 AS level FROM  (SELECT geom FROM ukpoly WHERE fid = 1) p, h9_fill_polygon(p.geom, 5) h;
CREATE TABLE hex9_uk AS SELECT h.hex9, h.bin, h.hex_geom, 5 AS level FROM  (SELECT geom FROM ukpoly WHERE fid = 1) p, h9_fill_polygon(p.geom, 5) h;
ALTER TABLE hex9_uk ADD PRIMARY KEY (hex9); 
CREATE INDEX hex9_uk_bin_idx ON hex9_uk (bin);
CREATE INDEX hex9_uk_level_idx ON hex9_uk (level);
CREATE INDEX hex9_uk_geom_idx ON hex9_uk USING GIST (hex_geom);
INSERT INTO hex9_uk (hex9, bin, hex_geom, level) SELECT h.hex9, h.bin, h.hex_geom, 6 AS level FROM (SELECT geom FROM ukpoly WHERE fid = 1) p, h9_fill_polygon(p.geom, 6) h;
show primary keys from hex9_uk;
\dt
\dk hex9_uk
\?
\d INSERT INTO hex9_uk (hex9, bin, hex_geom, level) SELECT h.hex9, h.bin, h.hex_geom, 6 AS level FROM (SELECT geom FROM ukpoly WHERE fid = 1) p, h9_fill_polygon(p.geom, 6) h;
\d hex9_uk
alter table hex9_uk drop primary key;
alter table hex9_uk drop key 'hex9';
alter table hex9_uk drop key hex9;
CREATE TABLE target_areas (
    id bigserial PRIMARY KEY,
    name char(64) not null,
    source_geom GEOMETRY(Polygon, 4326)
);
drop table target_areas;
CREATE TABLE hex9_areas (
    id bigserial PRIMARY KEY,
    name char(64) not null,
    source_geom GEOMETRY(Polygon, 4326)
);
insert into hex9_areas select (null,'ukpoly',geom FROM ukpoly WHERE fid = 1);
drop table hex9_areas;
CREATE TABLE h9_areas (
    id bigserial PRIMARY KEY,
    name char(64) not null,
    poly GEOMETRY(Polygon, 4326)
);
CREATE INDEX h9_areas_geom_idx ON h9_areas USING GIST (poly);
'ukpoly',geom FROM ukpoly WHERE fid = 1;
insert into h9_areas (name, poly) select "ukpoly",geom FROM ukpoly WHERE fid = 1;
select "ukpoly" as name,geom as poly FROM ukpoly WHERE fid = 1;
alter table hex9_areas change name name varchar(64) not null;
drop table hex9_areas;
drop table h9_areas;
CREATE TABLE h9_areas (
    id bigserial PRIMARY KEY,
    name varchar(64) not null,
    poly GEOMETRY(Polygon, 4326)
);
CREATE INDEX h9_areas_geom_idx ON h9_areas USING GIST (poly);
insert into h9_areas (name, poly) select "ukpoly",geom FROM ukpoly WHERE fid = 1;
select * from h9_areas;
insert into h9_areas set name="uk_extra";
insert into h9_areas values name="uk_extra";
insert into h9_areas (name) values "uk_extra";
insert into h9_areas (name) values ("uk_extra");
select * from h9_areas;
select "ukpoly", geom FROM ukpoly WHERE fid = 1;
insert into h9_areas (name) values ('uk_extra');
select * from h9_areas;
update h9_areas a, ukpoly p set a.poly=p.geom where p.fid=1 and a.id=2;
UPDATE h9_areas
SET poly = p.geom
FROM ukpoly p
WHERE h9_areas.id = 2 
  AND p.fid = 1;
select * from h9_areas;
CREATE TABLE h9_pt (
    id UUID PRIMARY KEY,
    bin8 UUID GENERATED ALWAYS AS (h9_bin(id, 8)) STORED,
    bin7 UUID GENERATED ALWAYS AS (h9_bin(id, 7)) STORED
);
drop table h9_pt;
CREATE TABLE h9_pt (
    id UUID PRIMARY KEY,
);
CREATE TABLE h9_pt (id UUID PRIMARY KEY);
CREATE TABLE h9_bin (
    pt UUID REFERENCES h9_pt(id) ON DELETE CASCADE,
    level INTEGER,
    bin UUID GENERATED ALWAYS AS (h9_bin(pt, level)) STORED,
    poly GEOMETRY(Polygon, 4326),
    PRIMARY KEY (bin)
);
drop table h9_bin;
CREATE TABLE h9_hex (
    pt UUID REFERENCES h9_pt(id) ON DELETE CASCADE,
    level INTEGER,
    bin UUID GENERATED ALWAYS AS (h9_bin(pt, level)) STORED,
    poly GEOMETRY(Polygon, 4326),
    PRIMARY KEY (bin)
);
CREATE INDEX h9_hex_level_idx ON h9_hex (level);
CREATE INDEX h9_hex_par_idx ON h9_hex (pt);
CREATE INDEX h9_hex_poly_idx ON h9_hex USING GIST (poly);
\gt
\dt
\dt ukpoly
\d ukpoly
\d ghg
select * from ghg;
drop table ghg;
\dt
\d uk_bounds;
drop table uk_bounds;
\dt
\d hex9_uk;
insert into h9_pt select hex9 from hex9_uk;
select * from hp_pt;
select * from h9_pt limit 5;
\d h9_hex
\d hex9_uk
insert into h9_hex (pt,level,poly) select hex9, hex_geom, level from hex9_uk;
insert into h9_hex set pt=u.hex9, poly=u.hex_geom, level=u.level from hex9_uk u;
insert into h9_hex (pt, level, poly) select hex9, level,  hex_geom from hex9_uk u;
select * from h9_hex;
select * from h9_hex;
select * from hex8_uk limit 4;
select * from hex9_uk limit 4;
insert into h9_hex select id,5 from h9_pt;
drop table h9_pt;
drop table h9_hex;
select * from h9_pt limit 2;
select h9_bin(uuid('43210003-5583-0424-4028-833014245165'), 4);
\df
\df h9_*
drop table h9_hex;
\df h9_*
ls
\df h9_*
\df
\df h9_*
  CREATE TABLE h9_poly_pts (                                                                                                                           
      area_id  integer REFERENCES h9_areas(id) ON DELETE CASCADE,                                                                                      
      level    integer,                                                                                                                                
      pt       uuid    REFERENCES h9_pt(id)    ON DELETE CASCADE,                                                                                      
      PRIMARY KEY (area_id, level, pt)                                                                                                                 
  );                                                                                                                                                   
  CREATE TABLE h9_poly_pts (                                                                                                                           
      area_id  integer REFERENCES h9_areas(id) ON DELETE CASCADE,                                                                                      
      level    integer,                                                                                                                                
      pt       uuid    REFERENCES h9_pt(id)    ON DELETE CASCADE,                                                                                      
      PRIMARY KEY (area_id, level, pt)                                                                                                                 
  );                                                                                                                                                  
\dt
CREATE OR REPLACE FUNCTION h9_populate(area_id integer, level integer)
RETURNS integer
LANGUAGE plpgsql AS $$
DECLARE n integer;
BEGIN
    WITH fill AS (
        SELECT hex9, bin, hex_geom
        FROM h9_areas a, h9_fill_polygon(a.geom, level)
        WHERE a.id = area_id
    ),
    ins_pt AS (
        INSERT INTO h9_pt (id)
        SELECT hex9 FROM fill
        ON CONFLICT DO NOTHING
    ),
    ins_hex AS (
        INSERT INTO h9_hex (pt, level, bin, poly)
        SELECT hex9, level, bin, hex_geom FROM fill
        ON CONFLICT DO NOTHING
    )
    INSERT INTO h9_poly_pts (area_id, level, pt)
    SELECT area_id, level, hex9 FROM fill
    ON CONFLICT DO NOTHING;

    GET DIAGNOSTICS n = ROW_COUNT;
    RETURN n;
END;
$$;
\d h9_poly_pts
\dt hex9_uk
drop table hex9_uk;
\dt
\dt h9_hex
\dt h9_hex
select * from h9_hex limit 1;
select * from h9_areas;
insert into h9_poly_pts select 2,level,pt from h9_hex;
  CREATE TABLE uk_pop (                                     
      hex9  uuid,      
      pop   float8,
      bin5  uuid, bin6 uuid, bin7 uuid, bin8 uuid, bin9 uuid                                                                                           
  );                                                        
\st
\dt
\d h9_poly_pts
create view h9_uk_L6 as SELECT h.bin, h.poly, SUM(p.pop) AS total_pop FROM uk_pop p JOIN h9_hex h ON h.bin = p.bin6 AND h.level = 6 GROUP BY h.bin, h.poly;
create view h9_uk_L7 as SELECT h.bin, h.poly, SUM(p.pop) AS total_pop FROM uk_pop p JOIN h9_hex h ON h.bin = p.bin7 AND h.level = 7 GROUP BY h.bin, h.poly;
create view h9_uk_L8 as SELECT h.bin, h.poly, SUM(p.pop) AS total_pop FROM uk_pop p JOIN h9_hex h ON h.bin = p.bin8 AND h.level = 8 GROUP BY h.bin, h.poly;
create view h9_uk_L9 as SELECT h.bin, h.poly, SUM(p.pop) AS total_pop FROM uk_pop p JOIN h9_hex h ON h.bin = p.bin9 AND h.level = 9 GROUP BY h.bin, h.poly;
\d h9_hex
\d uk_pop
create view h9_uk_L5 as SELECT h.bin, h.poly, SUM(p.pop) AS total_pop FROM uk_pop p JOIN h9_hex h ON h.bin = p.bin5 AND h.level = 5 GROUP BY h.bin, h.poly;
select count * from h9_uk_l8;
select count(*) from h9_uk_l8;
select count(*) from h9_uk_l7;
select count(*) from h9_uk_l6;
select count(*) from h9_uk_l5;
select count(*) from h9_uk_l9;
echo "0.19518869772516198 0.15855042215588602 -0.646260880118952" | cs2cs +proj=h9_boct +to +proj=longlat +datum=WGS84 --3d
select count(*) from h9_uk_l9;
INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text)
VALUES (
    999001, 
    'custom', 
    999001, 
    'PROJCS["B_Oct_Projection", GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433]], PROJECTION["H9_BOCT"], UNIT["meter",1]]', 
    '+proj=h9_boct +ellps=WGS84 +units=m +no_defs'
);
SELECT postgis_full_version();
SELECT ST_AsText(ST_Transform(
    ST_SetSRID(ST_MakePoint(0.304024114345498, -0.289333770877596), 910009),                                                                            
    4326));
SELECT ST_AsText(ST_Transform(                                                                                                                        
    ST_Transform(ST_SetSRID(ST_MakePoint(0, 51.5), 4326), 910009),
    4326));
SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_MakePoint(0, 51.5), 4326), 910009));
SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_MakePoint(0, 51.5, 0), 4326), 910009));
 SELECT ST_AsText(ST_Transform(                                                                                                                        
    ST_Transform(ST_SetSRID(ST_MakePoint(0, 51.5, 0), 4326), 910009),                                                                                   
    4326));
CREATE EXTENSION postgis_hex9
\dx postgis_hex9
 SELECT * FROM pg_available_extensions WHERE name = 'postgis_hex9';
 SELECT * FROM pg_available_extensions WHERE name = 'postgis_hex9';
 SELECT * FROM pg_available_extensions WHERE name = 'postgis_hex9';\dx
SELECT * FROM pg_available_extensions WHERE name = 'postgis_hex9'
;
SELECT * FROM pg_available_extensions WHERE name = 'postgis_hex9'
;
CREATE EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
DROP FUNCTION h9_decode(uuid,smallint);
CREATE EXTENSION postgis_hex9;
DROP FUNCTION h9_bin(uuid,integer);
CREATE EXTENSION postgis_hex9;
SELECT h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326));
WITH pts(name, geom) AS (
    VALUES
      ('London',    ST_SetSRID(ST_MakePoint(-0.1276,   51.5074), 4326)),
      ('Tokyo',     ST_SetSRID(ST_MakePoint(139.6917,  35.6895), 4326)),
      ('Sydney',    ST_SetSRID(ST_MakePoint(151.2093, -33.8688), 4326)),
      ('New York',  ST_SetSRID(ST_MakePoint(-74.0060,  40.7128), 4326)),
      ('Cape Town', ST_SetSRID(ST_MakePoint( 18.4241, -33.9249), 4326)),
      ('Reykjavik', ST_SetSRID(ST_MakePoint(-21.8174,  64.1466), 4326)),
      ('Null Island',ST_SetSRID(ST_MakePoint(  0.0,     0.0),    4326))
  ),
  encoded AS (
    SELECT name, geom, (h9_encode_address(geom)).hex9, (h9_encode_address(geom)).adr
    FROM pts
  ),                                                                                                                                                                              
  decoded AS (
    SELECT name, geom, hex9, h9_decode(hex9, adr) AS back
    FROM encoded
  )
  SELECT
    name,
    hex9,                                                                                                                                                                         
    round(ST_Distance(
      ST_Transform(geom, 3857),
      ST_Transform(back, 3857)
    )::numeric, 3) AS err_m
  FROM decoded
  ORDER BY name;                                                                                                                                                                  
WITH pts(name, geom) AS (
    VALUES
      ('London',    ST_SetSRID(ST_MakePoint(-0.1276,   51.5074), 4326)),
      ('Tokyo',     ST_SetSRID(ST_MakePoint(139.6917,  35.6895), 4326)),
      ('Sydney',    ST_SetSRID(ST_MakePoint(151.2093, -33.8688), 4326)),
      ('New York',  ST_SetSRID(ST_MakePoint(-74.0060,  40.7128), 4326)),
      ('Cape Town', ST_SetSRID(ST_MakePoint( 18.4241, -33.9249), 4326)),
      ('Reykjavik', ST_SetSRID(ST_MakePoint(-21.8174,  64.1466), 4326)),
      ('Null Island',ST_SetSRID(ST_MakePoint(  0.0,     0.0),    4326))
  ),
  encoded AS (
    SELECT name, geom, (h9_encode_address(geom)).hex9, (h9_encode_address(geom)).adr
    FROM pts
  ),                                                                                                                                                                              
  decoded AS (
    SELECT name, geom, hex9, h9_decode(hex9, adr) AS back
    FROM encoded
  )
  SELECT
    name,
    hex9,                                                                                                                                                                         
    round(ST_Distance(
      ST_Transform(geom, 3857),
      ST_Transform(back, 3857)
    )::numeric, 6) AS err_cm
  FROM decoded
  ORDER BY name;                                                                                                                                                                  
  WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts
  ),
  dec AS (
    SELECT name, lon, lat, geom,
           (addr).hex9, (addr).adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
  )
  SELECT
    name,
    (addr).hex9,                                                                                                                                                                  
    (addr).adr,
    round((ST_Distance(
      ST_Transform(geom,  3857),
      ST_Transform(back, 3857)
    ) * 1e6)::numeric, 2) AS err_um
  FROM dec
  ORDER BY err_um DESC;
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (                                                                                                                                                                        
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts                                                                                                                                                                          
  ),
  dec AS (
    SELECT name, lon, lat, geom,                                                                                                                                                  
           (addr).hex9 AS hex9,
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
  )                                                                                                                                                                               
  SELECT
    name,
    hex9,                                                                                                                                                                         
    adr,                                                                                                                                                                              
    round((ST_Distance(
      ST_Transform(geom,  3857),
      ST_Transform(back, 3857)
    ) * 1e6)::numeric, 2) AS err_um
  FROM dec
  ORDER BY err_um DESC;
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
           h9_encode_key(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS a_key
    FROM pts
  ),
  dec AS (
    SELECT name, lon, lat, geom, a_key,
           (addr).hex9 AS hex9,
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
  )
  SELECT
    name,
    a_key,
    hex9,
    adr,
    round((ST_Distance(
      ST_Transform(geom,  3857),
      ST_Transform(back, 3857)
    ) * 1e6)::numeric, 2) AS err_um
  FROM dec
  ORDER BY err_um DESC;
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
           h9_encode(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS a_key
    FROM pts
  ),
  dec AS (
    SELECT name, lon, lat, geom, a_key,
           (addr).hex9 AS hex9,
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
  )
  SELECT
    name,
    a_key,
    hex9,
    adr,
    round((ST_Distance(
      ST_Transform(geom,  3857),
      ST_Transform(back, 3857)
    ) * 1e6)::numeric, 2) AS err_um
  FROM dec
  ORDER BY err_um DESC;
SELECT * FROM h9_encode(-1.8262, 51.1789);
SELECT * FROM h9_encode(-1.8262, 51.1789);
\dt
\dt uk_pop
\d uk_pop
\d h9_pt;
select hex9 from uk_pop limit 12;
select right(hex9,2) from uk_pop limit 12;
select right(string(hex9),2) from uk_pop limit 12;
SELECT RIGHT('XYZ', 1);
select RIGHT(hex9,2) from uk_pop limit 12;
select RIGHT(cast hex9::char,2) from uk_pop limit 12;
select RIGHT(cast(hex9,'char'),2) from uk_pop limit 12;
select RIGHT(cast(hex9),2) from uk_pop limit 12;
select RIGHT(cast(hex9::char),2) from uk_pop limit 12;
select RIGHT(cast(hex9,char),2) from uk_pop limit 12;
select hex9 & 0xff from uk_pop limit 12;
select bitand(hex9, 0xff) from uk_pop limit 12;
select and(hex9, 0xff) from uk_pop limit 12;
select hex9 from uk_pop limit 12;
select h9_decode(hex9) from uk_pop limit 12;
select h9_decode(hex9,22) from uk_pop limit 12;
select h9_decode(43524220-6254-8177-2303-080325770245,22) ;
SELECT * FROM h9_encode(-1.8262, 51.1789);
SELECT h9_encode_address(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326));
select h9_decode((47823277-8313-5405-0863-808404756029,26));
select h9_decode(47823277-8313-5405-0863-808404756029,26);
\sf h9_decode;
\sf h9_decode*;
\s h9_decode;
\s
SELECT h9_encode_address(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326));
\sf h9
\sf h9*
\sf h9;
\df h9
\df h9*
drop function h9_bbox_hexagons, h9_bin, h9_cell,h9_decode, h9_encode, h9_encode_address,h9_encode_adr;
\df h9*
drop function h9_bin(uuid[], integer);
drop function h9_bin(uuid, integer);
drop extension postgis_hex9;
\df h9*
drop function h9_encode(double, double);
drop function h9_encode(double precision, double precision);
drop function h9_encode(double precision[], double precision[]);
\df h9*
drop function h9_populate(geometry, integer);
drop function h9_bbox_hexagons, h9_decode, h9_encode_adr, h9_fill_polygon, h9_in_bin, h9_populate;
\df h9*
create extension postgis_hex9;
\df h9*
SELECT h9_encode_address(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326));
select h9_decode(47823277-8313-5405-0863-808404756029,26);
select h9_decode('47823277-8313-5405-0863-808404756029',26);
select h9_decode(uuid('47823277-8313-5405-0863-808404756029'),26);
select h9_decode(uuid('47823277-8313-5405-0863-808404756029'),smallint(26));
select h9_decode(uuid('47823277-8313-5405-0863-808404756029'),cast 26 as smallint);
select h9_decode(uuid('47823277-8313-5405-0863-808404756029'),26);
select h9_bin(uuid('47823277-8313-5405-0863-808404756029'),26);
select h9_bin(uuid('47823277-8313-5405-0863-808404756029'),3);
drop extension postgis_hex9;
create extension postgis_hex9;
drop extension postgis_hex9;
create extension postgis_hex9;
drop extension postgis_hex9;
create extension postgis_hex9;
\df
\df h9*
drop extension postgis_hex9;
create extension postgis_hex9;
\df h9*
drop extension postgis_hex9;
\df h9*
drop extension postgis_hex9;
\df h9*
create extension postgis_hex9;
\df h9*
\df h9*
drop extension postgis_hex9;
create extension postgis_hex9;
\df h9*
 WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (                                                                                                                                                                        
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts                                                                                                                                                                          
  ),
  dec AS (
    SELECT name, lon, lat, geom,                                                                                                                                                  
           (addr).hex9 AS hex9,
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
    FROM pts                                                                                                                                                                      
  ),
  dec AS (
    SELECT name, lon, lat, geom,                                                                                                                                                  
           (addr).hex9 AS hex9,                                                                                                                                                       
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
  ) 
  SELECT
    name,
    hex9,                                                                                                                                                                         
    adr,
    round((ST_Distance(
      ST_Transform(geom,  3857),
      ST_Transform(back, 3857)
    ) * 1e6)::numeric, 2) AS err_um
  FROM dec
  ORDER BY err_um DESC;
create temporary table tmps WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  );
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
create temporary table pts;
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (                                                                                                                                                                        
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts                                                                                                                                                                          
  );
WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (                                                                                                                                                                        
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts                                                                                                                                                                          
  ) select * from enc;
)
)
select 'foo';
select 'fo';
select pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  );
select 'fo';
select 'fo' as dd;
select ('London',           -0.1276,    51.5074) as name,lon,lat;
select ('London',           -0.1276,    51.5074) as ('name','lon','lat');
select ('London', -0.1276,  51.5074) as ('name','lon','lat');
select ('London', -0.1276,  51.5074);
select 'London', -0.1276,  51.5074 as name,lon,lat;
select 'London', -0.1276,  51.5074 as 'name','lon','lat';
create temporary table places select       ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644);
create temporary table places as select (      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  );
 WITH pts(name, lon, lat) AS (
    VALUES
      ('London',           -0.1276,    51.5074),
      ('Tokyo',           139.6917,    35.6895),                                                                                                                                  
      ('Sydney',          151.2093,   -33.8688),
      ('New York',        -74.0060,    40.7128),
      ('Cape Town',        18.4241,   -33.9249),                                                                                                                                  
      ('Reykjavik',       -21.8174,    64.1466),
      ('Null Island',       0.0,        0.0),
      ('Antimeridian N',  180.0,       45.0),                                                                                                                                     
      ('Antimeridian S',  180.0,      -45.0),
      ('North Pole',        0.0,       90.0),
      ('South Pole',        0.0,      -90.0),                                                                                                                                     
      ('Oct boundary 1',   90.0,       0.0),
      ('Oct boundary 2',  -90.0,       0.0),
      ('Oct boundary 3',    0.0,       45.0),                                                                                                                                     
      ('Diagonals',        45.0,       35.2644)
  ),
  enc AS (                                                                                                                                                                        
    SELECT name, lon, lat,
           ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom,
           h9_encode_address(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) AS addr
    FROM pts                                                                                                                                                                          
  ),
  dec AS (
    SELECT name, lon, lat, geom,                                                                                                                                                  
           (addr).hex9 AS hex9,
           (addr).adr  AS adr,
           h9_decode((addr).hex9, (addr).adr) AS back
    FROM enc
    FROM pts                                                                                                                                                                      
  ) select * from dec;
select pts;
drop extension postgis_hex9;
create extension postgis_hex9;
  -- Round-trip test
  SELECT h9_decode(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)));
  -- Label tests (London, layer 8)
  SELECT h9_label    (h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)), 8), 8);
  SELECT h9_label_key(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)), 8);
  -- Cell geometry
  SELECT ST_AsText(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)), 8), 8));
\st
/st
?
\?
\d
select * from h9_hex limit 12;
select * from h9_hex limit 12;
select * from h9_pt limit 12;
select h9_decode(h9_pts) limit 12;
select h9_decode(h9_pt) limit 12;
select * from h9_pt limit 12;
select h9_decode(id) from h9_pt limit 12;
select ST_Y(h9_decode(id)) from h9_pt limit 12;
select ST_X(h9_decode(id)) from h9_pt limit 12;
select ST_XY(h9_decode(id)) from h9_pt limit 12;
create table h9rv select h9_decode(id) as gxy from h9_pt
select * from gxy limit 2;
select ST_XY(h9_decode(id)) from h9_pt limit 12;
create table h9rv select h9_decode(id) as gxy from h9_pt;
create table h9rv as select h9_decode(id) as gxy from h9_pt;
select * from h9rv limit 12;
create table lonlat as select STX(gxy) as lon, STY(gxy) as lat from h9rv;
create table lonlat as select ST_X(gxy) as lon, ST_Y(gxy) as lat from h9rv;
select * from lonlat limit 12;
select h9_encode(lon, lat) from lonlat limit 12;
select h9_encode(gxy) from lonlat limit 12;
select h9_encode(gxy) from h9rv  limit 12;
select h9_layer(gxy, 5) from h9rv limit 12;
\sf h9_*
\sf h9*
\s h9*
\f
\?
\dx
drop extension plpython3u
\df h9*
select h9_bin(gxy, 5) from h9rv limit 12;
select h9_bin(gxy, 5) from h9rv limit 12;
select h9_bin(h9_encode(gxy), 5) from h9rv limit 12;
select h9_bin(h9_label(gxy), 5) from h9rv limit 12;
select h9_bin(h9_encode(gxy), 5) from h9rv limit 12;
select h9_label(h9_encode(gxy), 5) from h9rv limit 12;
select h9_label_key(h9_encode(gxy), 5) from h9rv limit 12;
select count(*) from h9rv group by h9_label_key(h9_encode(gxy), 5);
select h9_label_key(h9_encode(gxy), 5), count(*) as freq from h9rv group by h9_label_key(h9_encode(gxy), 5) order by freq desc limit 20;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
  -- London L8 bbox — expect ~30–50 cells
  SELECT count(*), ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  -- Circle clip — cells should be limited to those inside the 5km circle
  SELECT count(*)
  FROM h9_grid(8,
    ST_Buffer(
      ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,
      5000)::geometry);
  -- Cap enforcement
  SELECT count(*) FROM h9_grid(14, ST_MakeEnvelope(-10, 49, 2, 61, 4326));
  -- UUID consistency: every returned UUID equals h9_bin(h9_encode(centroid), 8)
  SELECT bool_and(hex9 = h9_bin(h9_encode(ST_Centroid(geom)), 8))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
SELECT count(*), ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
DROP EXTENSION postgis_hex9;
\df h9*;
CREATE EXTENSION
\df h9*;
\df h9*
select h9_bin(h9_label(gxy), 5) from h9rv limit 12;
select h9_encode(gxy) from h9rv limit 12;
select h9_encode(gxy) from h9rv limit 12;
CREATE EXTENSION postgis_hex9;
select h9_encode(gxy) from h9rv limit 12;
SELECT count(*), ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
DROP EXTENSION postgis_hex9;
CREATE EXTENSION postgis_hex9;
SELECT count(*), ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
\q
DROP EXTENSION postgis_hex9;
 CREATE EXTENSION postgis_hex9;
DROP EXTENSION postgis_hex9;
\df h9*
 CREATE EXTENSION postgis_hex9;
 CREATE EXTENSION postgis_hex9;
\df h9*
SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326)); 
\q
\df h9*
SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*) FROM h9_grid(3, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*)
  FROM h9_grid(8,
    ST_Buffer(
      ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,
      5000)::geometry);
SELECT count(*) FROM h9_grid(14, ST_MakeEnvelope(-10, 49, 2, 61, 4326));
SELECT count(DISTINCT h9_bin(hex9, 8)) = count(*)
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*)
  FROM h9_grid(8,
    ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
SELECT count(*), ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
create table dims as select * FROM h9_grid(8,ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
create table dim2 as SELECT count(*)
  FROM h9_grid(8,
    ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
create table dim2 as SELECT *  FROM h9_grid(8,  ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
drop table dim2
create table dim2 as SELECT *  FROM h9_grid(8,  ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
drop table dim2;
create table dim2 as SELECT *  FROM h9_grid(8,  ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
drop table dim2
create table dim1 as ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
create table dim1 as ST_AsText(ST_Extent(geom))
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
create table dim1 as select ST_AsText(ST_Extent(geom))  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
drop table dim1;
\dt
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
SELECT count(*) FROM h9_grid(8, ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*) as total, count(DISTINCT hex9) as unique_uuids FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
create table dim1 as select * FROM h9_grid(8, ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
select * from dim1 limit 2;
select count(*) from dim1;
select ST_MakePoint(-0.1276, 51.5074), 4326);
select ST_MakePoint(-0.1276, 51.5074);
select ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000);
select ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography);
select 'foo';
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
SELECT count(*) FROM h9_grid(8,ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT h9_label(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)), 8), 8);
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9;
SELECT h9_version();
  -- Was 183 (3×61), should now be ~61
  SELECT count(*) FROM h9_grid(8,
    ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
  -- Was 741 (3×247), should now be ~247
  SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  -- Duplicate check — should be equal
  SELECT count(*) as total, count(DISTINCT hex9) as unique_uuids
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  -- Label still correct
  SELECT h9_label(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)), 8), 8);
  -- expect: 478232778
SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT count(*), count(DISTINCT hex9) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  DROP EXTENSION postgis_hex9;
  CREATE EXTENSION postgis_hex9;
  SELECT h9_version();                                                                                                            
  SELECT count(*), count(DISTINCT hex9)
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  SELECT count(*)
  FROM h9_grid(8, ST_Buffer(
      ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000
  )::geometry);
  DROP EXTENSION postgis_hex9;
  SELECT h9_version();                                                                                                           
  CREATE EXTENSION postgis_hex9;
  SELECT h9_version();                                                                                                           
  SELECT count(*), count(DISTINCT hex9)
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
  SELECT count(*)
  FROM h9_grid(8, ST_Buffer(
      ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000
  )::geometry);
select h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
CREATE MATERIALIZED VIEW london_hex8_rect AS
  SELECT hex9, geom
  FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));                                                                  
  CREATE INDEX ON london_hex8_rect USING GIST(geom);
CREATE MATERIALIZED VIEW london_hex8_circ AS SELECT hex9, geom FROM h9_grid(8, ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry);
CREATE INDEX ON london_hex8_circ USING hex9;
CREATE INDEX ON london_hex8_circ USING GIST(geom);
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
drop london_hex8_rect;
drop view london_hex8_rect;
drop materialized view london_hex8_rect;
drop materialized view london_hex8_circ;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
CREATE MATERIALIZED VIEW london_hex8_circ AS SELECT hex9, geom FROM h9_grid(8, ST_Buffer(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography, 5000)::geometry); CREATE INDEX ON london_hex8_circ USING GIST(geom);
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
drop materialized view london_hex8_circ;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
select h9_decode('47823518-2fff-ffff-ffff-fffffffffff1' as uuid);
select h9_decode('47823518-2fff-ffff-ffff-fffffffffff1');
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT h9_cell('47823518-2fff-ffff-ffff-fffffffffff1'::uuid, 8)::text,
         h9_cell('47823518-4fff-ffff-ffff-fffffffffff5'::uuid, 8)::text,
         h9_cell('47823518-6fff-ffff-ffff-fffffffffff5'::uuid, 8)::text,
         h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8)::text;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  WITH grid AS (
      SELECT hex9, geom FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326))                                                                                                        
  )
  SELECT
      count(*)                             AS total,
      count(DISTINCT ST_AsEWKT(geom))      AS distinct_geoms,
      count(*) FILTER (WHERE NOT ST_Equals(geom, h9_cell(hex9, 8))) AS cell_mismatch;
create table fx as SELECT hex9, geom FROM h9_grid(8, ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326));
SELECT count(*) as total, count(DISTINCT ST_AsEWKT(geom)) AS distinct_geoms, count(*) FILTER (WHERE NOT ST_Equals(geom, h9_cell(hex9, 8))) AS cell_mismatch from fx;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT count(*) as total, count(DISTINCT ST_AsEWKT(geom)) AS distinct_geoms, count(*) FILTER (WHERE NOT ST_Equals(geom, h9_cell(hex9, 8))) AS cell_mismatch from fx;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*), COUNT(DISTINCT hex9)
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326));
  -- Are the two specific missing cells now returned?
  SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326))
  WHERE hex9::text IN (
    '47823518-8fff-ffff-ffff-fffffffffff3',
    '47823512-0fff-ffff-ffff-fffffffffff1'
  );
  SELECT COUNT(*), COUNT(DISTINCT hex9)
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326));
  SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326))
  WHERE hex9::text IN (
    '47823518-8fff-ffff-ffff-fffffffffff3',
    '47823512-0fff-ffff-ffff-fffffffffff1'
  );
  SELECT ST_AsText(ST_Centroid(h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8)));
  SELECT ST_Intersects(
    h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8),
    ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326)
  );                                                                                                                                                                                     
  SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.160, 51.503, -0.152, 51.506, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326))
  WHERE hex9::text IN (
    '47823518-8fff-ffff-ffff-fffffffffff3',
    '47823512-0fff-ffff-ffff-fffffffffff1'
  );
  SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326))
  WHERE hex9::text IN (
    '47823518-8fff-ffff-ffff-fffffffffff3',
    '47823512-0fff-ffff-ffff-fffffffffff1'
  );
  SELECT COUNT(*), COUNT(DISTINCT hex9)
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326));
SELECT hex9::text
  FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.53, 4326))
  WHERE hex9::text IN (
    '47823518-8fff-ffff-ffff-fffffffffff3',
    '47823512-0fff-ffff-ffff-fffffffffff1'
  );
  SELECT
    hex9::text,
    count(*) as hits,                                                                                                                                                                    
    round(min(lon)::numeric, 5) as lon_start,                                                                                                                                           
    round(max(lon)::numeric, 5) as lon_end
  FROM (
    SELECT
      -0.22 + n * 0.002 as lon,
      h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)), 8) as hex9
    FROM generate_series(0, 40) n
  ) pts                                                                                                                                                                                 
  GROUP BY hex9
  ORDER BY lon_start;
  SELECT
    hex9::text,
    count(*) as hits,                                                                                                                                                                    
    round(min(lon)::numeric, 5) as lon_start,                                                                                                                                           
    round(max(lon)::numeric, 5) as lon_end,
    h9_bin(h9_pt), 8) as hex9_l8,
    h9_pt
  FROM (
    SELECT
      -0.22 + n * 0.002 as lon,
      h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
    FROM generate_series(0, 40) n
  ) pts                                                                                                                                                                                 
  GROUP BY h9_pt
  ORDER BY lon_start;
    SELECT
    hex9::text,
    count(*) as hits,                                                                                                                                                                    
    round(min(lon)::numeric, 5) as lon_start,                                                                                                                                           
    round(max(lon)::numeric, 5) as lon_end,
    h9_bin(h9_pt, 8) as hex9_l8,
    h9_pt
  FROM (
    SELECT
      -0.22 + n * 0.002 as lon,
      h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
    FROM generate_series(0, 40) n
  ) pts                                                                                                                                                                                 
  GROUP BY h9_pt
  ORDER BY lon_start;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT
  hex9::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(h9_pt, 8) as hex9_l8, h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT
  hex9::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8, pts.h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  h9_pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  h9_pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  pts.h9_pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  hex9_l8::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  hex9_l8::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  h9_pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(pts.h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  h9_pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_bin(h9_pt, 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326)) as h9_pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  hh9_encode(pt) as h9_pt 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  hh9_encode(pt) as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_encode(pt) as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  pt::text,
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_encode(pt) as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8, h9_pt, pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_encode(pt) as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8, h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_encode(pt) as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end,
  h9_encode(pt)::text as h9_pt, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326))::text as h9_pt,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 40) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326))::text as h9_pt,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 200) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lat)::numeric, 5) as lat_start,
  round(min(lon)::numeric, 5) as lon_start, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326))::text as h9_pt,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.002, 51.49), 4326) as pt
  FROM generate_series(0, 200) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT hex9, geom FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.4899999, -0.14, 51.4899999, 4326))
;
SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.4899999, -0.14, 51.4899999, 4326));
SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.4899999, -0.14, 51.4899999, 4326));
SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.490001, 4326));
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326))::text as h9_pt,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY hex9_l8
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_encode(ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326))::text as h9_pt,
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_encode(pt)::text as h9_pt,
  h9_bin(h9_encode(pt), 8) as hex9_l8
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_encode(pt)::text as h9_pt,
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.0002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  #h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  //h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
  SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  /*h9_encode(pt)::text as h9_pt*/
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00002, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  /*h9_encode(pt)::text as h9_pt*/
FROM (
  SELECT
    -0.22 + n * 0.002 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  /*h9_encode(pt)::text as h9_pt*/
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
 
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.490001, 4326))
  ORDER BY hex9;
  SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.490001, 4326));
SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.490001, 4326));
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  /*h9_encode(pt)::text as h9_pt*/
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_pt
  /*h9_encode(pt)::text as h9_pt*/
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start;
SELECT hex9 FROM h9_grid(8, ST_MakeEnvelope(-0.22, 51.49, -0.14, 51.490001, 4326));
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  /*h9_bin(h9_encode(pt), 8) as h9_pt*/
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
GROUP BY h9_pt
ORDER BY lon_start limit 12;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_l8
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
ORDER BY lon_start limit 9;
SELECT
  count(*) as hits,
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_l8,
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
ORDER BY lon_start limit 9;
SELECT
  round(min(lon)::numeric, 5) as lon_start,
  round(max(lon)::numeric, 5) as lon_end, 
  h9_bin(h9_encode(pt), 8) as h9_l8,
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
ORDER BY lon_start limit 9;
SELECT
  lon::numeric as lon_start,
  h9_bin(h9_encode(pt), 8) as h9_l8,
  h9_encode(pt)::text as h9_pt
FROM (
  SELECT
    -0.22 + n * 0.00008 as lon,
    ST_SetSRID(ST_MakePoint(-0.22 + n * 0.00008, 51.49), 4326) as pt
  FROM generate_series(0, 1000) n
) pts
ORDER BY lon_start limit 9;
 SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid));
select h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid);
\df h9_cell
select h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8);
 SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
SELECT ST_Y(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)) AS cell1_lat;
SELECT h9_bin(h9_encode(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)), 8) AS recoded;
SELECT h9_bin(h9_encode(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)), 7) AS recoded;
SELECT h9_bin(h9_encode(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)), 8) AS recoded;
SELECT h9_encode(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)) AS recoded;
SELECT h9_encode(h9_decode('47823514-2010-1010-1010-101010107059'::uuid)) AS recoded;
SELECT h9_bin(h9_encode(h9_decode('47823514-2010-1010-1010-101010107059'::uuid)),22) AS recoded;
SELECT h9_decode(h9_bin(h9_encode(h9_decode('47823514-2010-1010-1010-101010107059'::uuid)),22)) AS recoded;
SELECT h9_decode(h9_bin(h9_encode(h9_decode('47823514-2010-1010-1010-101010107059'::uuid)),22))::text AS recoded;
SELECT h9_encode(h9_decode(h9_bin(h9_encode(h9_decode('47823514-2010-1010-1010-101010107059'::uuid)),22))) AS recoded;
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
select h9_encode(ST_SetSRID(ST_MakePoint(-0.163356, 51.496572), 4326));
 ST_Y(h9_decode('47823218-5280-1806-7062-58562724803b'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5280-1806-7062-58562724803b'::uuid)) AS cell1_lat;
select h9_bin('47823218-5280-1806-7062-58562724803b'::uuid, 8);
select  ST_Y(h9_decode('47823218-5fff-ffff-ffff-fffffffffff3'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5fff-ffff-ffff-ffffffffff3b'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5000-0000-0000-00000000003b'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5000-0000-0000-000000000030'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5000-0000-0000-000000000003'::uuid)) AS cell1_lat;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
select  ST_Y(h9_decode('47823218-5000-0000-0000-000000000003'::uuid)) AS cell1_lat;
select  ST_Y(h9_decode('47823218-5fff-ffff-ffff-fffffffffff3'::uuid)) AS cell1_lat;
 SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid));
 SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
select h9_encode(ST_SetSRID(ST_MakePoint(-0.280998, 51.490384), 4326));
select h9_bin('47823814-6222-0665-8663-13412131661d'::uuid, 8);
select h9_label('47823814-6222-0665-8663-13412131661d'::uuid, 8);
select h9_label_key('47823814-6222-0665-8663-13412131661d'::uuid, 8);
select h9_encode(ST_SetSRID(ST_MakePoint(-0.281034, 51.476798), 4326));
select h9_encode(ST_SetSRID(ST_MakePoint(-0.257893, 51.469581), 4326));
select h9_encode(ST_SetSRID(ST_MakePoint(-0.257956, 51.483545), 4326));
select h9_label('47823814-4222-2725-6686-782088114103'::uuid, 8);
select h9_label('47823815-8227-8386-6781-835517783773'::uuid, 8);
select h9_label('47823814-0222-7663-0603-334083164845'::uuid, 8);
select h9_bin('47823814-4222-2725-6686-782088114103'::uuid, 8);
select h9_bin('47823815-8227-8386-6781-835517783773'::uuid, 8);
select h9_bin('47823814-0222-7663-0603-334083164845'::uuid, 8);
select h9_bin('47823814-6222-0665-8663-13412131661d'::uuid, 8);
select h9_label_key('47823814-6222-0665-8663-13412131661d'::uuid, 8);
select h9_bin('47823814-6222-0665-8663-13412131661d'::uuid, 8) union
select h9_label_key('47823814-6222-0665-8663-13412131661d'::uuid, 8);
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT h9_label_key('47823814-6222-0665-8663-13412131661d'::uuid, 8);
 SELECT ST_Y(h9_decode('47823518-8fff-ffff-ffff-fffffffffff3'::uuid)) AS cell1_lat;
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9 FROM h9_grid(8, h9_cell('47823518-8fff-ffff-ffff-fffffffffff3'::uuid, 8));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT * FROM h9_grid(6, ST_MakeEnvelope(-0.17, 51.45, -0.10, 51.46, 4326));
SELECT hex9 FROM h9_grid(6, ST_MakeEnvelope(-0.17, 51.45, -0.10, 51.46, 4326));
select h9_decode('4782322f-ffff-ffff-ffff-fffffffffffd':uuid)
;
select h9_decode('4782322f-ffff-ffff-ffff-fffffffffffd'::uuid);
SELECT ST_SRID(h9_cell(h9_bin('0101000020E61000000A21B7533FEDB1BFFA7A80332AAE4940',8)) AS cell_srid;
)
;
SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,h9_decode(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)))::geography) < 0.0000001 AS roundtrip_sub_1m;
SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,h9_decode(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)))::geography) < 0.000000001 AS roundtrip_sub_1m;
SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,h9_decode(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)))::geography) < 0.0000000001 AS roundtrip_sub_1m;
SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)::geography,h9_decode(h9_encode(ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326)))::geography) AS roundtrip_sub_1m;
SELECT * FROM h9_grid(6, ST_MakeEnvelope(-0.17, 51.45, -0.10, 51.46, 4326));
select h9_grid(6,'0103000020E610000001000000070000000621BAFCD6A7C216FAA32EFBCBB04940BD524BDCBFA3C21673C9CE7789AB49405CE60E6F51E9B1BF101378AEE7A84940DE3026D9A3EDC1BF3652945D87AB4940FBFCA9A493F1C1BFF1F718E1C9B049405EE6225C2FF1B1BF28474AB56CB349400621BAFCD6A7C216FAA32EFBCBB04940');
SELECT * FROM h9_grid(1, ST_MakeEnvelope(-0.179.9, -89.9, 179.9, 89.9, 4326));
SELECT * FROM h9_grid(1, ST_MakeEnvelope(-179.9, -89.9, 179.9, 89.9, 4326));
SELECT hex9, ST_AsText(ST_Centroid(geom)) as geom_ctr, geom FROM h9_grid(1, ST_MakeEnvelope(-75, -75, 75, 75, 4326));
SELECT hex9, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-75, -75, 75, 75, 4326));
SELECT hex9, ST_Area(geom) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-75, -75, 75, 75, 4326));
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-75, -75, 75, 75, 4326));
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-90, -90, 90, 90, 4326));
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-179, -90, 179, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(-179, -90, 179, 90, 4326));
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT hex9, ST_Area(geom::geography) as area, ST_AsText(ST_Centroid(geom)) as geom_ctr FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
  SELECT hex9, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
  SELECT hex9, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
  SELECT count(*) FROM h9_grid(8, ST_MakeEnvelope(-0.15, 51.45, -0.10, 51.46, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
  SELECT hex9, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326));
SELECT hex9, ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326));
SELECT hex9, ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by st_astext;
SELECT min(hex9), ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by st_astext;
SELECT STRING_AGG(hex9), ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by st_astext;
SELECT STRING_AGG(hex9::text), ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by st_astext;
SELECT STRING_AGG(hex9::text), ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326));
SELECT hex9::text, ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326));
SELECT STRING_AGG(hex9::text), ST_AsText(ST_Centroid(geom))
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by st_astext;
SELECT array_agg(hex9) as h9s, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by ctr;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT array_agg(hex9) as h9s, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by ctr;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT array_agg(hex9) as h9s, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by ctr;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT array_agg(hex9) as h9s, ST_AsText(ST_Centroid(geom)) as ctr
  FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) group by ctr;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded,
         h9_bin(h9_encode(ST_MakePoint(45, 50)), 1) AS binned_l1;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded,
         h9_bin(h9_encode(ST_MakePoint(45, 50)), 28) AS binned_l1;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded,
         h9_bin(h9_encode(ST_MakePoint(45, 50)), 29) AS binned_l1;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded,
         h9_bin(h9_encode(ST_MakePoint(45, 50)), 29) AS binned_l1;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded, h9_bin(h9_encode(ST_MakePoint(45, 50)), 29) AS binned_l1, h9_decode(h9_bin(h9_encode(ST_MakePoint(45, 50)), 29)) as bin_decoded;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded, h9_bin(h9_encode(ST_MakePoint(45, 50)), 29) AS binned_l1, h9_encode(h9_decode(h9_bin(h9_encode(ST_MakePoint(45, 50)), 29))) as bin_decoded;
SELECT h9_encode(ST_MakePoint(45, 50)) AS encoded, h9_bin(h9_encode(ST_MakePoint(45, 50)), 8) AS binned_l1, h9_encode(h9_decode(h9_bin(h9_encode(ST_MakePoint(45, 50)), 8))) as bin_decoded;
SELECT hex9, ST_AsText(ST_Centroid(geom)) as geom_ctr, geom FROM h9_grid(1, ST_MakeEnvelope(0, -90, 90, 90, 4326));
SELECT h9_bin(h9_encode(ST_MakePoint(45, 50)), 8) = h9_bin(h9_encode(h9_decode(h9_bin(h9_encode(ST_MakePoint(45, 50)), 8))), 8) AS stable;
SELECT count(*), count(DISTINCT hex9) FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326));
SELECT array_agg(hex9), ST_AsText(ST_Centroid(geom)) FROM h9_grid(1, ST_MakeEnvelope(0, 0, 90, 90, 4326)) GROUP BY ST_Centroid(geom);
SELECT h9_bin(h9_encode(ST_MakePoint(45.94, 33.37)), 1) AS expected_uuid;
WITH expected AS (
    SELECT h9_bin(h9_encode(ST_MakePoint(45.94, 33.37)), 1) AS u
  )
  SELECT g.hex9, e.u, g.hex9 = e.u AS match
  FROM expected e
  LEFT JOIN h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326)) g
    ON g.hex9 = e.u;
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
SELECT count(*) FROM h9_grid(1, ST_MakeEnvelope(-180, -90, 180, 90, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT h9_bin(h9_encode(ST_MakePoint(0.00895, 51.47516)), 11);
SELECT ST_AsText(ST_Centroid(h9_cell('43486836-4723-ffff-ffff-fffffffffff0'::uuid, 11)));
 Test 3 — polygon filter working?
  SELECT COUNT(*) FROM h9_grid(8, ST_MakeEnvelope(-0.01, 51.47, 0.01, 51.49, 4326));
  SELECT COUNT(*) FROM h9_grid(8, ST_Buffer(ST_MakePoint(0, 51.48)::geography, 500)::geometry);
   SELECT COUNT(*) FROM h9_grid(8, ST_MakeEnvelope(-0.01, 51.47, 0.01, 51.49, 4326));
  SELECT COUNT(*) FROM h9_grid(8, ST_Buffer(ST_MakePoint(0, 51.48)::geography, 500)::geometry);
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT ST_AsText(ST_Centroid(h9_cell('43486836-4723-ffff-ffff-fffffffffff0'::uuid, 11)));
SELECT * FROM h9_grid(11, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,
     -0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,
     -0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,
     -0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,
     -0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,
     -0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,
     -0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,
     -0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284,
      0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151,
      0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855,
      0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043,
      0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177,
      0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937,
      0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180,
      0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037,
      0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750,
      0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266,
      0.002237 51.481588
  )'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT ST_AsText(ST_Centroid(h9_cell('43486836-4723-ffff-ffff-fffffffffff0'::uuid, 11)));
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(0.002237 51.481588,...,0.002237 51.481588)'::geometry, 4326));
SELECT * FROM h9_grid(11, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,
     -0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,
     -0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,
     -0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,
     -0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,
     -0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,
     -0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,
     -0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284,
      0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151,
      0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855,
      0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043,
      0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177,
      0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937,
      0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180,
      0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037,
      0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750,
      0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266,
      0.002237 51.481588
  )'::geometry, 4326));
SELECT * FROM h9_grid(11, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0)'));
SELECT * FROM h9_grid(11, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0)');
);
create table gp11 as SELECT * FROM h9_grid(11, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,
     -0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,
     -0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,
     -0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,
     -0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,
     -0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,
     -0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,
     -0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284,
      0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151,
      0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855,
      0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043,
      0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177,
      0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937,
      0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180,
      0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037,
      0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750,
      0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266,
      0.002237 51.481588
  )'::geometry, 4326));
create table gp12 as SELECT * FROM h9_grid(12, ST_Polygon('LINESTRING(
      0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
      0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
     -0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,
     -0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,
     -0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,
     -0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,
     -0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,
     -0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,
     -0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,
     -0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,
     -0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,
     -0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,
     -0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,
     -0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,
     -0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,
     -0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,
     -0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,
     -0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,
     -0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,
     -0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,
     -0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,
     -0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284,
      0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151,
      0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855,
      0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043,
      0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177,
      0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937,
      0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180,
      0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037,
      0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750,
      0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266,
      0.002237 51.481588
  )'::geometry, 4326));
select count(*) from gp11;
\st gp11;
\s gp11;
select * from gp11 limit 2;
select count(*) from gp11 group by hex9;
select sum(count(*)) from gp11 group by hex9;
select count(*) from gp11 group by hex9;
select count(x) from select count(*) as x from gp11 group by hex9;
select count(x) from (select count(*) as x from gp11 group by hex9);
select count(x) from (select count(*) as x from gp11 group by geom);
sELECT h9_version();
SELECT ST_AsText(ST_Centroid(h9_cell('43486836-4723-ffff-ffff-fffffffffff0'::uuid, 11)));
sELECT h9_version();
select * from gp11 where hex9='43486833-3622-ffff-ffff-fffffffffff0';
SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(-0.009, 51.472, 0.010, 51.482, 4326));
SELECT COUNT(*), COUNT(DISTINCT hex9) FROM gp11;
SELECT max(c), min(c), round(avg(c),2) FROM (SELECT COUNT(*) c FROM gp11 GROUP BY geom) t;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT pg_reload_conf(); 
 SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,
    -0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,
    -0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,
    -0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,
    -0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,
    -0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,
    -0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,
    -0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,
     0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,
     0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,
     0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,
     0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,
     0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,
     0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,
     0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,
     0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,
     0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,
     0.002237 51.481588)'::geometry, 4326));
SELECT postgis_hex9_version()
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT postgis_hex9_version()
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT postgis_hex9_version()
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT postgis_hex9_version()
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT postgis_hex9_version()
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
 SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.005
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,
    -0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,
    -0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,
    -0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,
    -0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,
    -0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,
    -0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,
    -0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,
     0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,
     0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,
     0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,
     0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,
     0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,
     0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,
     0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,
     0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,
     0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,
     0.002237 51.481588)'::geometry, 4326));
'
)
)
;
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,
    -0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,
    -0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,
    -0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,
    -0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,
    -0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,
    -0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,
    -0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,
     0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,
     0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,
     0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,
     0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,
     0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,
     0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,
     0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,
     0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,
     0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,
     0.002237 51.481588)'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.0050

'));
drop table gp11; create table gp11 as SELECT * FROM h9_grid(12, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
  SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(
    0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,
    0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,
    -0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,
    -0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,
    -0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,
    -0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,
    -0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,
    -0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,
    -0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,
    -0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,
    -0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,
    -0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,
    -0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,
    -0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,
    -0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,
    -0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,
    -0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,
    -0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,
    -0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,
    -0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,
    -0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,
    -0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,
     0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,
     0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,
     0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,
     0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,
     0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,
     0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,
     0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,
     0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,
     0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,
     0.002237 51.481588)'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,-0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,-0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,-0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,-0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,-0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,-0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,-0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,-0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,-0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,-0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,-0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,-0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,-0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,-0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,-0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,-0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,-0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,-0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,-0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,0.002237 51.481588)'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,-0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,-0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,-0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,-0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,-0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,-0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,-0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,-0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,-0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,-0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,-0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,-0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,-0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,-0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,-0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,-0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,-0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,-0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,-0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,0.002237 51.481588)'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*) FROM h9_grid(11, ST_Polygon('LINESTRING(0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083,0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848, -0.001111 51.481653, -0.001934 51.481571,-0.002234 51.481530, -0.002221 51.481461, -0.002221 51.481401,-0.002253 51.481321, -0.002297 51.481270, -0.002338 51.481237,-0.002486 51.481164, -0.005877 51.480008, -0.005879 51.479916,-0.005951 51.479891, -0.006985 51.479551, -0.007034 51.479547,-0.007097 51.479560, -0.007134 51.479590, -0.007335 51.479494,-0.007442 51.479397, -0.007495 51.479285, -0.007527 51.479139,-0.007514 51.478899, -0.007494 51.478852, -0.007446 51.478788,-0.007245 51.478565, -0.007046 51.478424, -0.006676 51.478220,-0.006386 51.478039, -0.006260 51.477978, -0.006136 51.477870,-0.006029 51.477757, -0.006005 51.477729, -0.005880 51.477485,-0.005815 51.477322, -0.005750 51.477014, -0.005731 51.476990,-0.005437 51.476731, -0.005022 51.476427, -0.004507 51.476194,-0.003959 51.475790, -0.003773 51.475625, -0.003027 51.474917,-0.002994 51.474912, -0.002969 51.474894, -0.002916 51.474891,-0.002888 51.474874, -0.002460 51.474469, -0.002455 51.474461,-0.002462 51.474440, -0.002488 51.474420, -0.001428 51.473424,-0.001416 51.473429, -0.001357 51.473408, -0.001351 51.473396,-0.001361 51.473379, -0.001353 51.473372, -0.001367 51.473366,-0.000298 51.472357, -0.000266 51.472370, -0.000178 51.472284,0.000050 51.472207,  0.000024 51.472178,  0.000105 51.472151,0.000137 51.472109,  0.003658 51.473143,  0.005997 51.473855,0.007575 51.474360,  0.008834 51.474778,  0.009674 51.475043,0.009726 51.475070,  0.009770 51.475115,  0.009800 51.475177,0.009805 51.475238,  0.009792 51.475282,  0.006880 51.477937,0.006836 51.477963,  0.006623 51.478158,  0.006612 51.478180,0.005183 51.479505,  0.004899 51.479728,  0.004296 51.480037,0.003571 51.480459,  0.003289 51.480750,  0.003245 51.480750,0.003182 51.480774,  0.002722 51.481139,  0.002566 51.481266,0.002237 51.481588)'::geometry, 4326));
 SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT h9_version();
  SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
SELECT h9_version();
SELECT h9_version();
SELECT h9_version();
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT h9_version();
  SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
VACUUM (VERBOSE, ANALYZE);
\st
\?
\dt
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
SELECT pg_cancel_backend(21613);
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
CREATE MATERIALIZED VIEW gp08 AS SELECT hex9, geom FROM h9_grid(8, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp09 AS SELECT hex9, geom FROM h9_grid(9, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp10 AS SELECT hex9, geom FROM h9_grid(10, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
drop view gp09; drop view gp10;
drop materialized view gp09; drop materialized view gp10;
DROP EXTENSION postgis_hex9; CREATE EXTENSION postgis_hex9; SELECT h9_version();
drop materialized view gp09; drop materialized view gp08;
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
CREATE MATERIALIZED VIEW gp08 AS SELECT hex9, geom FROM h9_grid(8, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp09 AS SELECT hex9, geom FROM h9_grid(9, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
CREATE MATERIALIZED VIEW gp09 AS SELECT hex9, geom FROM h9_grid(9, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp10 AS SELECT hex9, geom FROM h9_grid(10, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp11 AS SELECT hex9, geom FROM h9_grid(11, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
drop table gp11;
CREATE MATERIALIZED VIEW gp11 AS SELECT hex9, geom FROM h9_grid(11, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
CREATE MATERIALIZED VIEW gp12 AS SELECT hex9, geom FROM h9_grid(12, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
drop table gp12;
CREATE MATERIALIZED VIEW gp12 AS SELECT hex9, geom FROM h9_grid(12, ST_Polygon('LINESTRING( 0.002237 51.481588, 0.001826 51.481983, 0.001692 51.482083, 0.001410 51.482271, 0.001084 51.482221, 0.000648 51.482130,-0.000455 51.481848,-0.001111 51.481653,-0.001934 51.481571,-0.002234 51.481530,-0.002221 51.481461,-0.002221 51.481401,-0.002253 51.481321,-0.002297 51.481270,-0.002338 51.481237,-0.002486 51.481164,-0.005877 51.480008,-0.005879 51.479916,-0.005951 51.479891,-0.006985 51.479551,-0.007034 51.479547,-0.007097 51.479560,-0.007134 51.479590,-0.007335 51.479494,-0.007442 51.479397,-0.007495 51.479285,-0.007527 51.479139,-0.007514 51.478899,-0.007494 51.478852,-0.007446 51.478788,-0.007245 51.478565,-0.007046 51.478424,-0.006676 51.478220,-0.006386 51.478039,-0.006260 51.477978,-0.006136 51.477870,-0.006029 51.477757,-0.006005 51.477729,-0.005880 51.477485,-0.005815 51.477322,-0.005750 51.477014,-0.005731 51.476990,-0.005437 51.476731,-0.005022 51.476427,-0.004507 51.476194,-0.003959 51.475790,-0.003773 51.475625,-0.003027 51.474917,-0.002994 51.474912,-0.002969 51.474894,-0.002916 51.474891,-0.002888 51.474874,-0.002460 51.474469,-0.002455 51.474461,-0.002462 51.474440,-0.002488 51.474420,-0.001428 51.473424,-0.001416 51.473429,-0.001357 51.473408,-0.001351 51.473396,-0.001361 51.473379,-0.001353 51.473372,-0.001367 51.473366,-0.000298 51.472357,-0.000266 51.472370,-0.000178 51.472284, 0.000050 51.472207, 0.000024 51.472178, 0.000105 51.472151, 0.000137 51.472109, 0.003658 51.473143, 0.005997 51.473855, 0.007575 51.474360, 0.008834 51.474778, 0.009674 51.475043, 0.009726 51.475070, 0.009770 51.475115, 0.009800 51.475177, 0.009805 51.475238, 0.009792 51.475282, 0.006880 51.477937, 0.006836 51.477963, 0.006623 51.478158, 0.006612 51.478180, 0.005183 51.479505, 0.004899 51.479728, 0.004296 51.480037, 0.003571 51.480459, 0.003289 51.480750, 0.003245 51.480750, 0.003182 51.480774, 0.002722 51.481139, 0.002566 51.481266, 0.002237 51.481588  )'::geometry, 4326));
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
create table gpm AS (
    SELECT hex9, geom
    FROM h9_grid(22, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326))
)
;
create table gpm AS (
    SELECT hex9, geom
    FROM h9_grid(21, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326))
)
;
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
create table gpm AS (
    SELECT hex9, geom
    FROM h9_grid(21, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326))
);
select ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326);
select ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326);
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
create table gpm02 AS (SELECT hex9, geom FROM h9_grid(2, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326)));
drop table gpm02;
SELECT count(*) FROM h9_grid(5, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326));
SELECT count(*) FROM h9_grid(15, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326));
SELECT count(*) FROM h9_grid(18, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326));
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SELECT * from h9_grid(13, (select * from gwp));
SELECT count(hex9) from h9_grid(13, (select * from gwp));
SELECT count(hex9) from h9_grid(13, (select * from gwp)) group by hex9;
SELECT count(hex9) from h9_grid(13, (select * from gwp));
select count(*) from h9_grid(13, (select * from gwp));
select count(*) from h9_grid(13, (select * from gwp)) group by hex9;
select count(distinct hex9) from h9_grid(13, (select * from gwp));
select count(*) from h9_grid(13, (select * from gwp)) group by hex9;
select count(distinct hex9) from h9_grid(13, (select * from gwp));
select count(*) from h9_grid(13, (select * from gwp));
create table gp13 as select * from h9_grid(13, (select * from gwp));
create table gpm AS (
    SELECT hex9, geom
    FROM h9_grid(21, ST_MakeEnvelope(-0.00000015, 51.4799999, 0.00000015, 51.4800001, 4326))
)
;
create table gp22 AS (SELECT hex9, geom FROM h9_grid(22, ST_MakeEnvelope(-0.000000015, 51.47999999, 0.000000015, 51.48000001, 4326)));
create table gp23 AS (SELECT hex9, geom FROM h9_grid(23, ST_MakeEnvelope(-0.000000015, 51.47999999, 0.000000015, 51.48000001, 4326)));
create table gp23 AS (SELECT hex9, geom FROM h9_grid(23, ST_MakeEnvelope(-0.0000000015, 51.479999999, 0.0000000015, 51.480000001, 4326)));
create table gp24 AS (SELECT hex9, geom FROM h9_grid(24, ST_MakeEnvelope(-0.0000000015, 51.479999999, 0.0000000015, 51.480000001, 4326)));
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
create table gp24 AS (SELECT hex9, geom FROM h9_grid(24, ST_MakeEnvelope(-0.0000000015, 51.479999999, 0.0000000015, 51.480000001, 4326)));
create table gp25 AS (SELECT hex9, geom FROM h9_grid(25, ST_MakeEnvelope(-0.0000000015, 51.479999999, 0.0000000015, 51.480000001, 4326)));
create table gp26 AS (SELECT hex9, geom FROM h9_grid(26, ST_MakeEnvelope(-0.0000000015, 51.479999999, 0.0000000015, 51.480000001, 4326)));
create table gp27 AS (SELECT hex9, geom FROM h9_grid(27, ST_MakeEnvelope(-0.00000000015, 51.4799999999, 0.00000000015, 51.4800000001, 4326)));
create table gp28 AS (SELECT hex9, geom FROM h9_grid(28, ST_MakeEnvelope(-0.00000000015, 51.4799999999, 0.00000000015, 51.4800000001, 4326)));
create table gp29 AS (SELECT hex9, geom FROM h9_grid(29, ST_MakeEnvelope(-0.00000000015, 51.4799999999, 0.00000000015, 51.4800000001, 4326)));
drop table gp24; 
drop table gp25;
drop table gp26;
drop table gp27;
drop table gp28;
drop table gp29;
DROP EXTENSION postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
\st
\?
\dt
select * from h9_areas;
ST_Polygon('LINESTRING( -2.6238109, 49.4726628,
-2.6129685, 49.4802828,
-2.5937349, 49.4801344,
-2.5913794, 49.4886627,
-2.5817014, 49.4914189,
-2.5756943, 49.4961258,
-2.5577207, 49.5012657,
-2.5451345, 49.5099962,
-2.5291156, 49.5131536,
-2.505032,  49.5102897,
-2.4929778, 49.5025042,
-2.5007966, 49.4825612,
-2.5067083, 49.4754988,
-2.5268362, 49.4687651,
-2.5215214, 49.4536999,
-2.526374,  49.4440629,
-2.5321198, 49.4352719,
-2.5279256, 49.4276855,
-2.5246489, 49.4195864,
-2.5422119, 49.4160053,
-2.5486342, 49.4212063,
-2.5576511, 49.4100779,
-2.5745219, 49.4192342,
-2.5815321, 49.4186485,
-2.5876922, 49.4154937,
-2.6179687, 49.4163464,
-2.6444443, 49.4237640,
-2.6585995, 49.4205243,
-2.6703814, 49.4236751,
-2.6768103, 49.4316199,
-2.6758919, 49.4384883,
-2.6590848, 49.4363383,
-2.6521219, 49.4464678,
-2.6555928, 49.4524284,
-2.6779246, 49.4602612,
-2.6772214, 49.4641590,
-2.6639458, 49.4646935,
-2.6536072, 49.4600357,
-2.6397425, 49.4634596,
-2.6389558, 49.4671856,
-2.6362022, 49.4705060,
-2.6238109, 49.4726628)');
elect ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)', 4326);
select ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)', 4326);
  SELECT COUNT(*) FROM h9_grid(11, ST_MakeEnvelope(0.006, 51.473, 0.012, 51.478, 4326));
select ST_MakePolygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)', 4326);
select ST_MakePolygon(ST_GeomFromText('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)', 4326));
SELECT * from ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );
select True;
select True as t;
SELECT  ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326));
SELECT  ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326);
select ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)::geometry, 4326);
'
)'

'
select True as t;
select ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)::geometry, 4326);
'
)'

'
select True as t;
'
'
select ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)::geometry, 4326);
'
)'

'
select True as t;
select True as t
;
select ST_Polygon('LINESTRING( -2.6238109, 49.4726628,-2.6129685, 49.4802828,-2.5937349, 49.4801344,-2.5913794, 49.4886627,-2.5817014, 49.4914189,-2.5756943, 49.4961258,-2.5577207, 49.5012657,-2.5451345, 49.5099962,-2.5291156, 49.5131536,-2.505032,  49.5102897,-2.4929778, 49.5025042,-2.5007966, 49.4825612,-2.5067083, 49.4754988,-2.5268362, 49.4687651,-2.5215214, 49.4536999,-2.526374,  49.4440629,-2.5321198, 49.4352719,-2.5279256, 49.4276855,-2.5246489, 49.4195864,-2.5422119, 49.4160053,-2.5486342, 49.4212063,-2.5576511, 49.4100779,-2.5745219, 49.4192342,-2.5815321, 49.4186485,-2.5876922, 49.4154937,-2.6179687, 49.4163464,-2.6444443, 49.4237640,-2.6585995, 49.4205243,-2.6703814, 49.4236751,-2.6768103, 49.4316199,-2.6758919, 49.4384883,-2.6590848, 49.4363383,-2.6521219, 49.4464678,-2.6555928, 49.4524284,-2.6779246, 49.4602612,-2.6772214, 49.4641590,-2.6639458, 49.4646935,-2.6536072, 49.4600357,-2.6397425, 49.4634596,-2.6389558, 49.4671856,-2.6362022, 49.4705060,-2.6238109, 49.4726628)'::geometry, 4326);
SELECT ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326);
SELECT ST_Polygon('LINESTRING( -2.6238109  49.4726628,-2.6129685  49.4802828,-2.5937349 49.4801344,-2.5913794 49.4886627,-2.5817014 49.4914189,-2.5756943 49.4961258,-2.5577207 49.5012657,-2.5451345 49.5099962,-2.5291156 49.5131536,-2.505032  49.5102897,-2.4929778 49.5025042,-2.5007966 49.4825612,-2.5067083 49.4754988,-2.5268362 49.4687651,-2.5215214 49.4536999,-2.526374  49.4440629,-2.5321198 49.4352719,-2.5279256 49.4276855,-2.5246489 49.4195864,-2.5422119 49.4160053,-2.5486342 49.4212063,-2.5576511 49.4100779,-2.5745219 49.4192342,-2.5815321 49.4186485,-2.5876922 49.4154937,-2.6179687 49.4163464,-2.6444443 49.4237640,-2.6585995 49.4205243,-2.6703814 49.4236751,-2.6768103 49.4316199,-2.6758919 49.4384883,-2.6590848 49.4363383,-2.6521219 49.4464678,-2.6555928 49.4524284,-2.6779246 49.4602612,-2.6772214 49.4641590,-2.6639458 49.4646935,-2.6536072 49.4600357,-2.6397425 49.4634596,-2.6389558 49.4671856,-2.6362022 49.4705060,-2.6238109 49.4726628)'::geometry 4326);
SELECT ST_Polygon('LINESTRING( -2.6238109  49.4726628,-2.6129685  49.4802828,-2.5937349 49.4801344,-2.5913794 49.4886627,-2.5817014 49.4914189,-2.5756943 49.4961258,-2.5577207 49.5012657,-2.5451345 49.5099962,-2.5291156 49.5131536,-2.505032  49.5102897,-2.4929778 49.5025042,-2.5007966 49.4825612,-2.5067083 49.4754988,-2.5268362 49.4687651,-2.5215214 49.4536999,-2.526374  49.4440629,-2.5321198 49.4352719,-2.5279256 49.4276855,-2.5246489 49.4195864,-2.5422119 49.4160053,-2.5486342 49.4212063,-2.5576511 49.4100779,-2.5745219 49.4192342,-2.5815321 49.4186485,-2.5876922 49.4154937,-2.6179687 49.4163464,-2.6444443 49.4237640,-2.6585995 49.4205243,-2.6703814 49.4236751,-2.6768103 49.4316199,-2.6758919 49.4384883,-2.6590848 49.4363383,-2.6521219 49.4464678,-2.6555928 49.4524284,-2.6779246 49.4602612,-2.6772214 49.4641590,-2.6639458 49.4646935,-2.6536072 49.4600357,-2.6397425 49.4634596,-2.6389558 49.4671856,-2.6362022 49.4705060,-2.6238109 49.4726628)'::geometry, 4326);
\dt
select * from gpm;
ls
\dt
select * from gwp;
select * from gwp;
;s
ls
;
\dt
desc h9_areas;
\dt h9_areas;
\df h9_areas;
\?
\st h9_areas;
\d h9_Areas;
insert into h9_areas (name, poly) select 'greenwich park', * from gwp;
select * from h9_areas;
insert into h9_areas (name, poly) select 'Guernsey Island', ST_Polygon('LINESTRING( -2.6238109  49.4726628,-2.6129685  49.4802828,-2.5937349 49.4801344,-2.5913794 49.4886627,-2.5817014 49.4914189,-2.5756943 49.4961258,-2.5577207 49.5012657,-2.5451345 49.5099962,-2.5291156 49.5131536,-2.505032  49.5102897,-2.4929778 49.5025042,-2.5007966 49.4825612,-2.5067083 49.4754988,-2.5268362 49.4687651,-2.5215214 49.4536999,-2.526374  49.4440629,-2.5321198 49.4352719,-2.5279256 49.4276855,-2.5246489 49.4195864,-2.5422119 49.4160053,-2.5486342 49.4212063,-2.5576511 49.4100779,-2.5745219 49.4192342,-2.5815321 49.4186485,-2.5876922 49.4154937,-2.6179687 49.4163464,-2.6444443 49.4237640,-2.6585995 49.4205243,-2.6703814 49.4236751,-2.6768103 49.4316199,-2.6758919 49.4384883,-2.6590848 49.4363383,-2.6521219 49.4464678,-2.6555928 49.4524284,-2.6779246 49.4602612,-2.6772214 49.4641590,-2.6639458 49.4646935,-2.6536072 49.4600357,-2.6397425 49.4634596,-2.6389558 49.4671856,-2.6362022 49.4705060,-2.6238109 49.4726628)'::geometry, 4326);
select * from h9_areas;
select id, name from h9_areas;
delete from h0_areas where id=2;
delete from h9_areas where id=2;
drop table h9_uk_l5;
drop view h9_uk_l5;
\sv h9_uk_l6
\st h9_hex;
\vt h9_hex
\sv h9_hex
\d h9_hex
delete from h9_hex;
\d h9_pt
dekete frin h9_pt;
delete frin h9_pt;
delete from h9_pt;
drop table h9_uk_l6;
drop view h9_uk_l6;
\?
select * from h9_areas;
CREATE TABLE h9_grid (
    bin uuid NOT NULL,
    src integer NOT NULL,
    level integer NOT NULL,
    poly geometry(Polygon,4326),
);
ALTER TABLE ONLY h9_grid ADD CONSTRAINT h9_grid_pkey PRIMARY KEY (bin);
ALTER TABLE ONLY h9_grid ADD CONSTRAINT h9_grid_fkey FOREIGN KEY (src) REFERENCES h9_areas(id) ON DELETE CASCADE;
CREATE INDEX h9_grid_idx ON h9_grid USING gist (poly);
CREATE TABLE h9_grid (
    bin uuid NOT NULL,
    src integer NOT NULL,
    level integer NOT NULL,
    poly geometry(Polygon,4326)
);
ALTER TABLE ONLY h9_grid ADD CONSTRAINT h9_grid_pkey PRIMARY KEY (bin);
ALTER TABLE ONLY h9_grid ADD CONSTRAINT h9_grid_fkey FOREIGN KEY (src) REFERENCES h9_areas(id) ON DELETE CASCADE;
CREATE INDEX h9_grid_idx ON h9_grid USING gist (poly);
select id, name from h9_areas;
\d h9_areas;
select hex_9, geom from h9_grid(3,select poly from h9_areas where id=9);
select hex_9, geom from h9_grid(select poly from h9_areas where id=9);
select hex_9, geom from h9_grid(3, (select poly from h9_areas where id=9));
select * from h9_grid(3, (select poly from h9_areas where id=9));
select * from h9_grid(5, (select poly from h9_areas where id=9));
select * from h9_grid(7, (select poly from h9_areas where id=9));
select * from h9_grid(4, (select poly from h9_areas where id=5));
select * from h9_grid(5, (select poly from h9_areas where id=5));
select * from h9_grid(6, (select poly from h9_areas where id=5));
select hex9, geom from h9_grid(6, (select poly from h9_areas where id=5));
select hex9, 5, 6, geom from h9_grid(6, (select poly from h9_areas where id=5));
select hex9, 5 as src, 7 as level, geom from h9_grid(7, (select poly from h9_areas where id=5));
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 7 as level, geom from h9_grid(7, (select poly from h9_areas where id=5));
select * from h9_grid;
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 8 as level, geom from h9_grid(8, (select poly from h9_areas where id=5));
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 9 as level, geom from h9_grid(9, (select poly from h9_areas where id=5));
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 10 as level, geom from h9_grid(10, (select poly from h9_areas where id=5));
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 11 as level, geom from h9_grid(11, (select poly from h9_areas where id=5));
insert into h9_grid (bin, src, level, poly) select hex9, 5 as src, 12 as level, geom from h9_grid(12, (select poly from h9_areas where id=5));
create table ggy20 select 2.6 as lon, .63 as lat, 3.11 as pop;
create table ggy20 as select 2.6 as lon, .63 as lat, 3.11 as pop;
\d ggy20;
insert into ggy20 (lon,lat,pop) select (-2.17722222207920524,49.7324999999826147,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1774999998569835,49.7322222222048325,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17722222207920524,49.7322222222048325,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1774999998569835,49.7319444444270573,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17777777763476132,49.7316666666492821,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1774999998569835,49.7316666666492821,2.32410788536071777);
insert into ggy20 (lon,lat,pop) select (-2.17722222207920524,49.7316666666492821,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17694444430142742,49.7316666666492821,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1774999998569835,49.7313888888714999,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16638888874586311,49.7313888888714999,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.18527777763476738,49.7302777777603922,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17861111096809523,49.7302777777603922,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1641666665236392,49.7299999999826099,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.18222222207920957,49.7297222222048347,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16944444430142136,49.7297222222048347,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16916666652364309,49.7297222222048347,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17722222207920524,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17694444430142742,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17055555541253353,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17027777763475527,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16444444430141747,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1641666665236392,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16361111096808312,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1633333331903053,49.7294444444270525,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17722222207920524,49.7291666666492773,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.17083333319031135,49.7291666666492773,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16611111096808528,49.7291666666492773,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16444444430141747,49.7291666666492773,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.1641666665236392,49.7291666666492773,1.16205394268035889);
insert into ggy20 (lon,lat,pop) select (-2.16055555541252531,49.7291666666492773,1.16205394268035889);
insert into ggy20 (-2.1774999998569835,49.7288888888715022,1.16205394268035889);
insert into ggy20 (-2.17722222207920524,49.7288888888715022,1.16205394268035889);
insert into ggy20 (-2.17111111096808962,49.7288888888715022,1.16205394268035889);
insert into ggy20 (-2.17083333319031135,49.7288888888715022,1.16205394268035889);
insert into ggy20 (-2.17055555541253353,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.1774999998569835,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.17722222207920524,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.17111111096808962,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.17083333319031135,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.17055555541253353,49.7288888888715022,1.16205394268035889);
select * from ggy20;
delete from ggy2- where lat=0.63;
delete from ggy20 where lat=0.63;
select * from ggy20;
insert into ggy20 values (-2.17027777763475527,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.1633333331903053,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.16305555541252748,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.16277777763474921,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.16055555541252531,49.7288888888715022,1.16205394268035889);
insert into ggy20 values (-2.1774999998569835,49.7286111110937199,2.32410788536071777);
insert into ggy20 values (-2.17722222207920524,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.17694444430142742,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.1766666665236496,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.17638888874587133,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.17055555541253353,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.17027777763475527,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.16305555541252748,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.16027777763474749,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.15999999985696922,49.7286111110937199,1.16205394268035889);
insert into ggy20 values (-2.17722222207920524,49.7283333333159447,1.16205394268035889);
insert into ggy20 values (-2.17083333319031135,49.7283333333159447,1.16205394268035889);
insert into ggy20 values (-2.1633333331903053,49.7283333333159447,1.16205394268035889);
insert into ggy20 values (-2.1677777776347531,49.7280555555381625,1.16205394268035889);
insert into ggy20 values (-2.16694444430141964,49.7280555555381625,1.16205394268035889);
insert into ggy20 values (-2.1633333331903053,49.7280555555381625,2.32410788536071777);
insert into ggy20 values (-2.16305555541252748,49.7280555555381625,1.16205394268035889);
insert into ggy20 values (-2.15888888874585749,49.7280555555381625,1.16205394268035889);
insert into ggy20 values (-2.18861111096810346,49.7277777777603873,1.16205394268035889);
insert into ggy20 values (-2.15888888874585749,49.7277777777603873,1.16205394268035889);
insert into ggy20 values (-2.17194444430142353,49.7274999999826122,1.16205394268035889);
insert into ggy20 values (-2.17138888874586744,49.7274999999826122,1.16205394268035889);
insert into ggy20 values (-2.16999999985697745,49.7274999999826122,1.16205394268035889);
insert into ggy20 values (-2.16277777763474921,49.7274999999826122,1.16205394268035889);
insert into ggy20 values (-2.18416666652365521,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.18361111096809957,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.1833333331903213,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.17194444430142353,49.7272222222048299,2.32410788536071777);
insert into ggy20 values (-2.17138888874586744,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.17111111096808962,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.17027777763475527,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.1619444443014153,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.16166666652363748,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.16138888874585922,49.7272222222048299,1.16205394268035889);
insert into ggy20 values (-2.18638888874587956,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.18611111096810129,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.18444444430143347,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.18416666652365521,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.1833333331903213,49.7269444444270547,2.32410788536071777);
insert into ggy20 values (-2.18305555541254348,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.17138888874586744,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.17083333319031135,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.16583333319030746,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.16138888874585922,49.7269444444270547,1.16205394268035889);
insert into ggy20 values (-2.16083333319030313,49.7269444444270547,1.16205394268035889);
select count(*) from ggy20;
ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);
ALTER TABLE ggy20  ADD COLUMN geom geometry(Point, 4326);
UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
UPDATE ggy20 SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
UPDATE ggy20 SET geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);
ALTER TABLE ggy20  ADD COLUMN h9 uuid;
\d ggy20;
select h9_encode(geom),lat,lon from ggy20 limit 20;
update ggy20 set h9=h9_encode(heom);
update ggy20 set h9=h9_encode(geom);
\st
\dt
\dt h9_grid;
\d h9_grid
\d ggy20
ALTER TABLE ggy20 ADD COLUMN bin9 uuid GENERATED ALWAYS AS (h9_bin(h9, 9)) STORED; CREATE INDEX ON ggy20 (bin9);
ALTER TABLE ggy20 ADD COLUMN bin10 uuid GENERATED ALWAYS AS (h9_bin(h9, 10)) STORED; CREATE INDEX ON ggy20 (bin10);
ALTER TABLE ggy20 ADD COLUMN bin11 uuid GENERATED ALWAYS AS (h9_bin(h9, 11)) STORED; CREATE INDEX ON ggy20 (bin11);
ALTER TABLE ggy20 ADD COLUMN bin12 uuid GENERATED ALWAYS AS (h9_bin(h9, 12)) STORED; CREATE INDEX ON ggy20 (bin12);
ALTER TABLE ggy20 ADD COLUMN bin8 uuid GENERATED ALWAYS AS (h9_bin(h9, 8)) STORED; CREATE INDEX ON ggy20 (bin8);
CREATE INDEX ON ggy20 USING GIST (geom);
DO $$
BEGIN
  FOR i IN 8..12 LOOP
    EXECUTE format(
      'ALTER TABLE btn20 ADD COLUMN bin%s uuid GENERATED ALWAYS AS (h9_bin(h9, %s)) STORED',
      lpad(i::text, 2, '0'), i
    );
    EXECUTE format(
      'CREATE INDEX ON btn20 (bin%s)',
      lpad(i::text, 2, '0')
    );
  END LOOP;
END;
$$;
\d bt
\dt
\d h9_areas;
SELECT MAX(ST_NumGeometries(geom)) FROM bt;
select id,name from h9_Areas;
insert into h9_areas select 6, 'Bhutan', ST_GeometryN(geom, 1) from bt;
select id,name from h9_Areas;
drop table bt;
insert into h9_grid (bin, src, level, poly) select hex9, 6 as src, 8 as level, geom from h9_grid(8, (select poly from h9_areas where id=6));
insert into h9_grid (bin, src, level, poly) select hex9, 6 as src, 9 as level, geom from h9_grid(9, (select poly from h9_areas where id=6));
insert into h9_grid (bin, src, level, poly) select hex9, 6 as src, 7 as level, geom from h9_grid(7, (select poly from h9_areas where id=6));
ALTER TABLE btn20 ADD COLUMN bin07 uuid GENERATED ALWAYS AS (h9_bin(h9, 7)) STORED; CREATE INDEX ON btn20 (bin07);
CREATE OR REPLACE VIEW h9_btn_l7 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_hex h ON h.bin = p.bin7 AND h.level = 7 GROUP BY h.bin, h.poly
CREATE OR REPLACE VIEW h9_btn_l8 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_hex h ON h.bin = p.bin8 AND h.level = 8 GROUP BY h.bin, h.poly
;
;
CREATE OR REPLACE VIEW h9_btn_l7 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_hex h ON h.bin = p.bin7 AND h.level = 7 GROUP BY h.bin, h.poly;
CREATE OR REPLACE VIEW h9_btn_l7 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_hex h ON h.bin = p.bin07 AND h.level = 7 GROUP BY h.bin, h.poly;
CREATE OR REPLACE VIEW h9_btn_l8 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_hex h ON h.bin = p.bin08 AND h.level = 8 GROUP BY h.bin, h.poly;
select count(*) from h9_btn_l7;
CREATE OR REPLACE VIEW h9_btn_l8 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_grid h ON h.bin = p.bin08 AND h.level = 8 GROUP BY h.bin, h.poly;
select count(*) from h9_btn_l8;
CREATE OR REPLACE VIEW h9_btn_l7 AS SELECT h.bin, h.poly, sum(p.btn_general_2020) AS total_pop FROM btn20 p JOIN h9_grid h ON h.bin = p.bin07 AND h.level = 7 GROUP BY h.bin, h.poly;
select count(*) from h9_btn_l7;
\d h9_grid;
\d h9_area
\d
\d h9_areas
select id,name from h9_areas;
select id,name from h9_areas;
select id,name from h9_areas;
delete from h9_grid;
insert into h9_grid (bin, src, level, poly) select hex9, 2 as src, 5 as level, geom from h9_grid(5, (select poly from h9_areas where id=2));
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version(); 
insert into h9_grid (bin, src, level, poly) select hex9, 2 as src, 5 as level, geom from h9_grid(5, (select poly from h9_areas where id=2));
delete from h9_grid;
insert into h9_grid (bin, src, level, poly) select hex9, 2 as src, 5 as level, geom from h9_grid(5, (select poly from h9_areas where id=2));
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT h9_version();           -- confirm fresh load
  SHOW hex9.encoder;              -- expect 'nn'
  
  -- Same point both ways:
  SET hex9.encoder = 'nn';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(0.009313674395060489, 51.474933833691), 4326));
  
  SET hex9.encoder = 'containment';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(0.009313674395060489, 51.474933833691), 4326));
  
      SET hex9.encoder = 'nn';
      SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326)) AS nn;
      SET hex9.encoder = 'containment';
      SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326)) AS containment;
  SET hex9.use_warp = off;
  SET hex9.encoder = 'nn';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  
  SET hex9.encoder = 'containment';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SET hex9.use_warp = on;
  
  SET hex9.encoder = 'nn';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  
  SET hex9.encoder = 'containment';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SET hex9.use_warp = on;
  SELECT h9_diag(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
print(w.undo(np.array([[0.20786011,-0.26302651]]), mo=1))
;
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SET hex9.use_warp = on;
  SET hex9.encoder = 'containment';
  
  -- The diagnostic should now show the descent input matching Python ex0062's BARY:
  SELECT h9_diag(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  -- Expect: BRAW=(0.20786…, -0.26302…) descent=(0.21542944…, -0.26807383…)
  
  -- And the encode should match Python's UUID:
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  -- Expect: 43472104-7601-2378-… (matching Python through L11; tail divergence at L12+ is _recover territory)
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SET hex9.use_warp = on;
SET hex9.encoder = 'containment';
SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
SET hex9.encoder = 'nn';
SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
SET hex9.encoder = 'containment';
SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
SET hex9.use_warp = on;
  -- (1) Both encoders should now match Python through L13+ for interior points
  SET hex9.encoder = 'nn';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  
  SET hex9.encoder = 'containment';
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
  -- Both expected: 43472104-7601-2368-... (matching Python through L13)
  
  -- (2) Round-trip sanity: decode-then-encode should return same UUID
  WITH p AS (SELECT ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326) AS g)
  SELECT h9_encode(p.g) AS encoded,
         h9_encode(h9_decode(h9_encode(p.g))) AS roundtrip,
         h9_encode(p.g) = h9_encode(h9_decode(h9_encode(p.g))) AS match
  FROM p;
\st
;
\dt
\d h9_grid
alter table h9_grid change poly geom(Polygon,4326);
alter table h9_grid rename poly to geom;
\d h9_grid
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  WITH p AS (SELECT ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326) AS g)
  SELECT h9_encode(p.g) AS encoded,
         h9_encode(h9_decode(h9_encode(p.g))) AS roundtrip,
         h9_encode(p.g) = h9_encode(h9_decode(h9_encode(p.g))) AS match
  FROM p;
  SELECT h9_encode(ST_SetSRID(ST_MakePoint(13.405313674395048970, 52.520933833691204), 4326));
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT ST_AsText(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(13.4, 52.5), 4326)), 8), 8)) =
         ST_AsText(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(13.4, 52.5), 4326)), 8), 8, 0));
  SELECT ST_NPoints(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(13.4, 52.5), 4326)), 2), 2, 0)),  -- 7
         ST_NPoints(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(13.4, 52.5), 4326)), 2), 2, 1)),  -- 19
         ST_NPoints(h9_cell(h9_bin(h9_encode(ST_SetSRID(ST_MakePoint(13.4, 52.5), 4326)), 2), 2, 3));  -- 163
delete from h9_grid;
insert into h9_grid (bin, src, level, poly) select hex9, 2 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 3);
insert into h9_grid (bin, src, level, poly) select hex9, 2 as src, 5 as level, geom from h9_grid((select geom from h9_areas where id=2), 1, 3);
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 5 as level, geom from h9_grid((select geom from h9_areas where id=2), 1, 3);
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 3);
\d h9_areas
select poly from h9_areas where id=2
;
select * from select poly from h9_areas where id=2 limit 1 \h
select * poly from h9_areas where id=2 limit 1 \g
select * poly from h9_areas where id=2 limit 1;
select * from h9_areas where id=2 limit 1;
select * from h9_grid((select poly from h9_areas where id=2), 1, 2) limit 1;
select * from h9_grid(1, (select poly from h9_areas where id=2), 2) limit 1;
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 3);
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 6);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 6);
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1);
select hex9 from h9_grid((select poly from h9_areas where id=2), 1);
select hex9 from h9_grid((select geom from h9_areas where id=2), 1);
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
select hex9 from h9_grid((select geom from h9_areas where id=2), 1);
select hex9 from h9_grid((select poly from h9_areas where id=2), 1, 1);
select * from h9_grid((select poly from h9_areas where id=2), 1, 1);
SELECT count(*), count(DISTINCT geom) FROM h9_grid((select poly from h9_areas where id=2), 2, 1);
  SELECT count(*), count(DISTINCT hex9) FROM h9_grid((select poly from h9_areas where id=2), 2, 0);
  SELECT count(*), count(DISTINCT hex9) FROM h9_grid((select poly from h9_areas where id=2), 2, 1);
  SELECT count(*),
         count(DISTINCT geom)               AS uniq_grid_legacy,
         count(DISTINCT h9_cell(hex9, 2))   AS uniq_h9_cell_d0,
         count(DISTINCT h9_cell(hex9, 2, 1)) AS uniq_h9_cell_d1
  FROM h9_grid((select poly from h9_areas where id=2), 2, 0);
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT hex9, ST_AsText(centroid) AS c, ST_NPoints(geom) AS np
  FROM h9_grid(ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326), 8) LIMIT 5;
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
  SELECT hex9, ST_AsText(centroid) AS c, ST_NPoints(geom) AS np
  FROM h9_grid(ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326), 8) LIMIT 5;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 4);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 4);
SELECT count(*), count(DISTINCT geom) FROM h9_grid((select poly from h9_areas where id=2), 2, 1);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 4);
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 4);
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 1 as level, geom from h9_grid((select poly from h9_areas where id=2), 1, 4);
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id=2), 2, 5);
DROP EXTENSION if exists postgis_hex9 cascade; CREATE EXTENSION postgis_hex9; SELECT h9_version();
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id=2), 2, 5);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id=2), 2, 5);
delete from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 2 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id=2), 2, 5);
SELECT h9_version();     
update btn20 set h9=h9_encode(geom);
select h9 from btn20 limit 20;
select count(*) from h9_grid;
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id, 6), 2, 5);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 2 as level, geom from h9_grid((select poly from h9_areas where id=6), 2, 5);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 3 as level, geom from h9_grid((select poly from h9_areas where id=6), 3, 5);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 4 as level, geom from h9_grid((select poly from h9_areas where id=6), 4, 5);
delete from h9_grid where src=6;
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 5 as level, geom from h9_grid((select poly from h9_areas where id=6), 5);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 6 as level, geom from h9_grid((select poly from h9_areas where id=6), 6);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 7 as level, geom from h9_grid((select poly from h9_areas where id=6), 7);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 8 as level, geom from h9_grid((select poly from h9_areas where id=6), 8);
insert into h9_grid (bin, src, level, geom) select hex9, 6 as src, 9 as level, geom from h9_grid((select poly from h9_areas where id=6), 9);
\dt 
\dt btn20
\d btn20
ALTER TABLE btn20 ADD COLUMN l6 uuid GENERATED ALWAYS AS (h9_bin(h9, 6)) STORED;
ALTER TABLE btn20 ADD COLUMN l7 uuid GENERATED ALWAYS AS (h9_bin(h9, 7)) STORED;
ALTER TABLE btn20 ADD COLUMN l8 uuid GENERATED ALWAYS AS (h9_bin(h9, 8)) STORED;
ALTER TABLE btn20 ADD COLUMN l9 uuid GENERATED ALWAYS AS (h9_bin(h9, 9)) STORED;
ALTER TABLE btn20 ADD COLUMN l10 uuid GENERATED ALWAYS AS (h9_bin(h9, 10)) STORED;
\d h9_grid
ALTER TABLE btn20 ADD COLUMN l11 uuid GENERATED ALWAYS AS (h9_bin(h9, 11)) STORED;
\dt
\d h9_areas
select id,name from h9_areas
;
select max(layer) from h9_grid where src=6;
\d h9_grid
select max(level) from h9_grid where src=6;
insert into h9_grid (bin, src, level, geom) select hex9, 4 as src, 12 as level, geom from h9_grid((select poly from h9_areas where id=4), 12);
insert into h9_grid (bin, src, level, geom) select hex9, 4 as src, 13 as level, geom from h9_grid((select poly from h9_areas where id=4), 13);
SELECT ST_AsText( ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );
insert into h9_areas (1eas select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.12 0.1,  89.1 -0.1,  89.12 -0.1)'::geometry, 4326);
);
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.12 0.1,  89.1 -0.1,  89.12 -0.1)'::geometry, 4326));
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.12 0.1,  89.1 -0.1,  89.12 -0.1, 89.1 0.1)'::geometry, 4326));
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.12 0.1,  89.12 -0.1,  89.1 -0.1, 89.1 0.1)'::geometry, 4326));
delete from h9_areas where id=7;
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.12 0.1,  89.12 -0.1,  89.1 -0.1, 89.1 0.1)'::geometry, 4326));
delete from h9_areas where id=7;
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(89.1 0.1,  89.11 0.1,  89.11 -0.1,  89.1 -0.1, 89.1 0.1)'::geometry, 4326));
delete from h9_areas where id=7;
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(0.1 89.1, 0.1 89.11,-0.1 89.11,-0.1 89.1,0.1 89.1)'::geometry, 4326));
delete from h9_areas where id=7;
insert into h9_areas (select 7, '89_1', ST_Polygon('LINESTRING(0.005 89.0999, 0.005 89.1001, -0.005 89.1001, -0.005 89.0999, 0.005 89.0999)'::geometry, 4326));
insert into h9_grid (bin, src, level, geom) select hex9, 7 as src, 13 as level, geom from h9_grid((select poly from h9_areas where id=7), 13);
insert into h9_grid (bin, src, level, geom) select hex9, 7 as src, 14 as level, geom from h9_grid((select poly from h9_areas where id=7), 14);
insert into h9_grid (bin, src, level, geom) select hex9, 7 as src, 15 as level, geom from h9_grid((select poly from h9_areas where id=7), 15);
insert into h9_grid (bin, src, level, geom) select hex9, 7 as src, 16 as level, geom from h9_grid((select poly from h9_areas where id=7), 16);
insert into h9_grid (bin, src, level, geom) select hex9, 7 as src, 17 as level, geom from h9_grid((select poly from h9_areas where id=7), 17);
\s h9_grid
