gID inteligente para pontos de endereço - AddressForAll/DCRPec GitHub Wiki
Implementando o que foi solicitado em https://github.com/AddressForAll/DCRPec/issues/2
-- General funcions:
CREATE or replace FUNCTION b32ghs_to_vbit( p_val text, p_bits int DEFAULT NULL) RETURNS varbit AS $f$
DECLARE
ch char;
ret varbit := b'';
alphabet text := '0123456789bcdefghjkmnpqrstuvwxyz';
BEGIN
FOREACH ch IN ARRAY regexp_split_to_array(p_val,'') LOOP
ret := ret || (strpos(alphabet,ch)-1)::bit(5)::varbit;
END LOOP;
RETURN CASE WHEN p_bits IS NULL THEN ret ELSE substring(ret,1,p_bits) END;
END
$f$ LANGUAGE PLpgSQL IMMUTABLE;
COMMENT ON FUNCTION b32ghs_to_vbit(text,int)
IS 'Faster base32 Geohash, string to varbit, with optional truncation. Algorithm based on strpos.'
;
CREATE or replace FUNCTION vbit_to_int( b varbit, blen int DEFAULT NULL) RETURNS int AS $f$
-- slower SELECT ( (b'0'::bit(32) || b) << COALESCE(blen,length(b)) )::bit(32)::int
-- !loss information about varbit zeros and empty varbit
SELECT overlay( b'0'::bit(32) PLACING b FROM 33-COALESCE(blen,length(b)) )::int
-- same as ( substring(0::bit(32),bit_length(x)+1) || x )::bit(32)::int
$f$ LANGUAGE SQL IMMUTABLE;
CREATE or replace FUNCTION vbit_to_int(x varbit[]) returns int[] as $f$
SELECT array_agg( vbit_to_int(x_i) ORDER BY i) FROM unnest(x) WITH ORDINALITY t(x_i,i)
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION vbit_to_base(
p_val varbit, -- input
p_base text DEFAULT '32ghs'
) RETURNS text AS $f$
DECLARE
vlen int;
pos0 int;
ret text := '';
blk varbit;
blk_n int;
bits_per_digit int;
trtypes JSONb := '{
"4js":[0,1,2],"8js":[0,1,3],"16js":[0,1,4],
"32ghs":[1,4,5],"32hex":[1,1,5],"32nvu":[1,2,5],"32rfc":[1,3,5],
"64url":[2,8,6],"32js":[1,1,5]
}'::JSONb; -- var,pos,bits
base0 "char"[] := array[
'[0:15]={0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f}'::"char"[] --1. 4, 5 , 16 js
];
base1 "char"[] := array[
'[0:31]={0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v}'::"char"[] --1=32hex
,'[0:31]={0,1,2,3,4,5,6,7,8,9,B,C,D,F,G,H,J,K,L,M,N,P,Q,R,S,T,U,V,W,X,Y,Z}'::"char"[] --2=32nvu
,'[0:31]={A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,2,3,4,5,6,7}'::"char"[] --3=32rfc
,'[0:31]={0,1,2,3,4,5,6,7,8,9,b,c,d,e,f,g,h,j,k,m,n,p,q,r,s,t,u,v,w,x,y,z}'::"char"[] --4=32ghs
];
-- "64url": "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_"
tr_selected JSONb;
trbase "char"[];
BEGIN
vlen := bit_length(p_val);
tr_selected := trtypes->(p_base::text);-- [1=var,2=pos,3=bits]
IF p_val IS NULL OR tr_selected IS NULL OR vlen=0 THEN
RETURN NULL; -- or p_retnull;
END IF;
IF p_base='2' THEN
-- need to strip leading zeros
RETURN $1::text; --- direct bit string as string
END IF;
bits_per_digit := (tr_selected->>2)::int;
IF vlen % bits_per_digit != 0 THEN
RETURN NULL; -- trigging ERROR
END IF;
blk_n := vlen/bits_per_digit;
pos0 = (tr_selected->>1)::int;
-- trbase := CASE tr_selected->>0 WHEN '0' THEN base0[pos0] ELSE base1[pos0] END; -- NULL! pgBUG?
trbase := CASE tr_selected->>0 WHEN '0' THEN base0 ELSE base1 END;
--RAISE NOTICE 'HELLO: %; % % -- %',pos0,blk_n,trbase,trbase[pos0][1];
FOR counter IN 1..blk_n LOOP
blk := substring(p_val FROM 1 FOR bits_per_digit);
ret := ret || trbase[pos0][ vbit_to_int(blk,bits_per_digit) ]::text;
p_val := substring(p_val FROM bits_per_digit+1);
END LOOP;
vlen := bit_length(p_val);
-- IF p_val!=b'' THEN ERROR
RETURN ret;
END
$f$ LANGUAGE PLpgSQL IMMUTABLE;
COMMENT ON FUNCTION vbit_to_base
IS 'Converts varbit to compressed text, using standard numeric bases (4js, 32ghs, etc.).'
;
CREATE or replace FUNCTION geomPoint_to_gidPrefix(
p_ghs text,
is_composit boolean DEFAULT false
) RETURNS bigint
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT ( CASE WHEN is_composit THEN b'01' ELSE b'00' END
|| b32ghs_to_vbit(ghs6)
|| b'00000000000000000000000000000000'
)::bit(64)::bigint
FROM (SELECT substring(p_ghs,1,6)) t(ghs6);
END;
COMMENT ON FUNCTION geomPoint_to_gidPrefix(text,boolean)
IS 'Converts Geohash into a binary geohash of 30 bits, with fist bit in zero and second bit as type.'
;
CREATE or replace FUNCTION geomPoint_to_gidPrefix(
p_ghs text,
is_composit boolean DEFAULT false
) RETURNS bigint
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT ( CASE WHEN is_composit THEN b'01' ELSE b'00' END
|| b32ghs_to_vbit(substring(p_ghs,1,6))
|| b'00000000000000000000000000000000'
)::bit(64)::bigint
END;
COMMENT ON FUNCTION geomPoint_to_gidPrefix(text,boolean)
IS 'Converts Geohash into a binary geohash of 30 bits, with fist bit in zero and second bit as type.'
;
CREATE or replace FUNCTION geomPoint_to_gidPrefix(
p_pt geometry(Point,4326), -- depectar se ponto ou se precisa de ST_Centroid
is_composit boolean DEFAULT false
) RETURNS bigint
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT geomPoint_to_gidPrefix(ghs6,is_composit)
FROM (SELECT ST_GeoHash(p_pt,6)) t(ghs6); -- ou ST_PointOnSurface(p_geom)
END;
COMMENT ON FUNCTION geomPoint_to_gidPrefix(geometry(Point,4326),boolean)
IS 'Converts point into a binary geohash of 30 bits, with fist bit in zero.'
;
CREATE or replace FUNCTION gid_to_str(p_gid bigint) RETURNS text
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT vbit_to_base(substring(p_gid::bit(64),3,30))
||'-'||
(p_gid & 4294967295::bigint)::text;
END;
COMMENT ON FUNCTION gid_to_str(bigint)
IS 'Converts inteligent-gid into human-readable string.'
;
Em seguida a cada tabela de pontos de endereços, será necessário montar seu próprio Trigger... Vamos supor que sample_point2 seja uma tabela desse tipo.
DROP TABLE sample_point CASCADE
;
CREATE TABLE sample_point (
gid bigint DEFAULT -1 NOT NULL PRIMARY KEY, -- reserved value -1
city_name text NOT NULL,
--geom geometry(Point, 4326) NOT NULL,
ghs9 text NOT NULL,
UNIQUE(ghs9)
);
--- special functions:
CREATE or replace FUNCTION trg_sample_point_before_insert()
RETURNS trigger LANGUAGE plpgsql AS $f$
DECLARE
prefix bigint;
local_serial bigint;
BEGIN
prefix := geomPoint_to_gidPrefix(NEW.ghs9); -- or geom
local_serial := COALESCE((SELECT MAX(gid & 4294967295::bigint)+1 FROM sample_point WHERE (gid & 9223372032559808512) = prefix), 1::bigint);
-- depois aprimorar para o caso de lotes...
NEW.gid := prefix + local_serial;
RETURN NEW;
END;
$f$;
CREATE TRIGGER sample_point_before_insert
BEFORE INSERT ON sample_point
FOR EACH ROW
EXECUTE FUNCTION trg_sample_point_before_insert();
INSERT INTO sample_point (city_name,ghs9)
SELECT city_name, ST_GeoHash( str_geom::geometry(Point,4326), 9 )
FROM (
SELECT 'Sao Paulo', 'POINT(-46.63331 -23.55052)'
UNION ALL SELECT 'Rio Branco' , 'POINT(-67.810250 -9.97472)'
UNION ALL SELECT 'Recife' , 'POINT(-34.881301 -8.05428)'
UNION ALL SELECT 'Boa Vista' , 'POINT(-60.644531 2.900391)'
UNION ALL SELECT 'Porto Alegre' , 'POINT(-51.152344 29.97066)'
) t(city_name,str_geom);
----
INSERT INTO sample_point (city_name,ghs9)
SELECT city_name, ST_GeoHash( str_geom::geometry(Point,4326), 9 )
FROM (
SELECT 'Sao Paulo', 'POINT(-46.64331 -23.5552)'
UNION ALL SELECT 'Rio Branco' , 'POINT(-67.8112 -9.9741)'
UNION ALL SELECT 'Recife' , 'POINT(-34.882 -8.0544)'
) t(city_name,str_geom);
SELECT *, gid_to_str(gid) AS gid_humano FROM sample_point;