Brazil/PostGIS-testes

From OpenStreetMap Wiki
Jump to navigation Jump to search

{{GcdrBox}}

... adaptado para PostgreSQL 9.X da implementação de buscas do .... , e das funções TIGER-PostGIS ...


SQL schema e tipos

Lembrando que no postgreSQL a toda tabela está associado um tipo. 

CREATE SCHEMA geoname; -- esquema que encapsula funções e tabelas do Geocoder e do URN-Resolver

CREATE TABLE geoname.urn_response (
   urn  varchar(255), -- urn canônica encontrada
   urn_search varchar(255), -- urn que se buscava (sugeita a similar_text com urn)
   matchtype integer, -- 1=exato, 2=like words, 3=like metaphone, 
  ...
);

SQL funções principais

CREATE FUNCTION  goename.geocode (
    -- v1.0.0
    --  Geocodifica endereços baseados em nome de logradouro, conforme contexto (default município usuário da API).
    --  Se a string address for uma URN, salta a normalização da string, e assume o contexto da URN caso seja fornecido.
    --
    -- Example(s): 
    -- SELECT goename.geocode('rua Rafael A.S. Vidal 1515');
    -- SELECT goename.geocode('urn:geoname::via:rua.rafael.a.s.vidal:num:1515');
    -- SELECT goename.geocode('São Carlos SP, rua R. Vidal 1515',2,432);
    -- SELECT goename.geocode('av. Getulio Vargas, 700 - Ibaté',2);
    --
    address       IN varchar,               -- endereço completo com número predial no final.
    max_results   IN integer default  10,   -- máximo de resultados no retorno.
    context_id    IN integer default  NULL  -- id da entidade de contexto (id da cidade).
    ) RETURNS setof record AS $BODY$
	DECLARE
		...
	BEGIN
		IF substring(address from 1 for 12) = 'urn:geoname:' THEN
			... trata URN, que nem precisa ser de endereço ...
			... ver "especializações de goename.geocode" tais como geocode_code() e goename.geocode_address()...
		ELSE
			... trata string endereço iniciando por goename.Normalize_Address(address)...
		END IF;
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION  goename.output_geometry(
    -- v1.0.0
    -- Converte formato de saida da geometria conforme convenções de tipo adotadas.
    -- Gera exceção de erro se asType for desconhecido ou conversão não-permitida.
    --
    -- Example(s): 
    -- SELECT goename.output_geometry(the_geom,'geom'); -- identidade
    -- SELECT goename.output_geometry(the_geom,'geom.point'); -- obtém centroide se não for ponto
    -- SELECT goename.output_geometry(the_geom,'wkt.point'); -- converte para WKT com st_asText()
    --
    the_geom geometry, -- geometria para ser retornada
    asType varchar DEFAULT 'geom', -- formato 'geom.point', 'wkt.poly', 'wkb.point', 'geojson', ect.
    pt_radius_buffer float DEFAULT 5
    ) RETURNS setof record AS $BODY$
	DECLARE
		gtype varchar; -- ...
		part varchar[]; -- 0=format, 1=gtype.
		retgeom geometry;
	BEGIN
		part = string_to_array(lower(asType),'.'); -- ou pior regexp_split_to_array(x,E'\\.');
		gtype:=ST_GeometryType(the_geom);
		retgeom := the_geom;
		CASE part[2]
		  WHEN 'poly' THEN
			IF gtype='point' AND pt_radius_buffer>0 THEN
				retgeom := st_buffer(the_geom,pt_radius_buffer);
			ELSE
				RAISE EXCEPTION 'ponto nao pode ter output de poligono'
			END IF;
		  WHEN 'point' THEN
			IF gtype!='point' THEN 
				retgeom := st_centroid(the_geom);
			END IF;
		END CASE;
		CASE part[1]
		  WHEN 'wkt' THEN
			RETURN ST_AsText(retgeom);
		  WHEN 'wkb' THEN
			RETURN ST_AsBinary(retgeom);
 		  WHEN 'geojson' THEN
			RETURN ST_AsGeoJSON(retgeom);
		  DEFAULT:
		  	RETURN retgeom;
		END CASE;
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION  goename.Normalize_Address(
    -- v1.0.0
-- fachada para Normalize_to_URN 
    -- Normaliza a string (UTF8) de endereço conforme regras contextuais, retornando-a em formato de URN, 
    -- junto com os alertas de erro de formatação, e a URN final em formato Metaphone (que traduz o UTF8 de 
    -- forma adequada quando necessário).
    --
    -- Example(s): 
    -- SELECT goename.Normalize_Address('rua Rafael A.S.Vidal 1515');
    -- SELECT goename.Normalize_Address('São Carlos SP','city',20);
    --
    address    IN varchar,                   -- endereço completo com número predial no final.
    typeopt    IN varchar default  'street', -- tipo de endereço.
    context_id IN integer default  NULL      -- id da entidade de contexto (id da cidade).
    ) RETURNS setof record AS $BODY$
	DECLARE
		...
	BEGIN
		...
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION  goename.Resolver(
    -- v1.0.0
    -- Devolve (o ID ou string) da URN canônica "casada" com a URN fornecida, 
    -- e alertas de erro e confiabilidade da resolução obtida.
    --
    -- Example(s): 
    -- SELECT goename.Resolver('::via:rua.rafael.a.s.vidal:num:1515');
    -- SELECT goename.Resolver('::via:rua.eca.de.queiros:1515','::r.ess.d.krs',2);
    -- SELECT goename.Resolver('br;rs;restinga.seca','br;rs;rtng.sc');
    -- SELECT goename.Resolver('br;rs');
    -- SELECT goename.Resolver('br;sp;sao.carlos',NULL,1,NULL,'geom.poly');
    -- SELECT goename.Resolver('::ref:teatro.municipal');
    -- SELECT goename.Resolver('::ref:condominio.damha.ii:num:rua.das.flores,500,apto.20');
    --
    urn         IN varchar,               -- endereço completo com número predial no final.
    metaphone   IN varchar default NULL,  -- tipo de endereço.
    max_results IN integer default 10,    -- máximo de resultados no retorno.
    context_id  IN integer default NULL   -- id da entidade de contexto (id da cidade).
    retype      IN integer default 'geom.point' -- formato do retorno.
    ) RETURNS setof record AS $BODY$
	DECLARE
		...
	BEGIN
		...
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION goename.interpolate_from_via(
    -- v1.0.0
    -- Obtém ponto sob a via mediante interpolação do numpredial.
    -- Requer tabela geoname.via padronizada (dados e orientação padrão).
    -- NOTA: não resolve casos como Bauru, onde é usado o padrão "quadra-numero".
    --
    -- Example(s): 
    -- SELECT goename.interpolate_from_via(123,1515);
    --
    via_id      IN integer,              -- ID da via
    numpredial  IN integer default 0,    -- número predial (zero é o inicio).
    retnull     IN boolean default false -- flag para retornar NULL em caso de numpredial fora do intervalo.
    ) RETURNS geometry AS $BODY$
	DECLARE
		rvia record; -- via_geom geometry; via_nmax integer; via_nmin integer;
		part float;
		p integer;
	BEGIN
		SELECT RECORD[the_geom,nmax,nmin] INTO rvia FROM geoname.via WHERE id=via_id;
		IF numpredial<rvia.nmin THEN 
			IF retnull THEN RETURN NULL ELSE p:=rvia.nmin; END IF;
		ELSEIF numpredial>rvia.nmax THEN
			IF retnull THEN RETURN NULL ELSE p:=rvia.nmax; END IF;
		ELSE
			p:=numpredial;
		END IF;
		part := (p - rvia.nmin)::float / trunc(rvia.nmax - rvia.nmin, 1)::float;
		RETURN line_interpolate_point(via_geom, part);
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION goename.exactPoint_from_via(
    -- v1.0.0
    -- Obtém ponto da "porta principal do lote" se o endereço exato existir na base de dados.
    -- Requer tabela geoname.lote padronizada (com atributo pointporta nao-nulo).
    -- NOTA: não resolve casos como Bauru, onde é usado o padrão "quadra-numero".
    --
    -- Example(s): 
    -- SELECT goename.exactpoint_from_via(123,1515);
    --
    p_via_id      IN integer,         -- ID da via
    numpredial  IN integer default 0  -- número predial (zero é o inicio).  
    ) RETURNS geometry AS $BODY$
	DECLARE
		ret_pointporta geometry;
		via_nmax integer;
		via_nmin integer;
		part float;
	BEGIN
		SELECT pointporta INTO ret_pointporta FROM geoname.lote WHERE via_id=p_via_id AND num=numpredial;
		IF FOUND THEN
			RETURN ret_pointporta;
		ELSE
			RETURN NULL;
		ENDIF;
	END;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION goename.point_from_via(
    -- v1.0.0
    -- Obtém ponto da "porta principal do lote" se o endereço exato existir na base de dados.
    -- Requer tabela geoname.lote padronizada (com atributo pointporta nao-nulo).
    -- NOTA: não resolve casos como Bauru, onde é usado o padrão "quadra-numero".
    --
    -- Example(s): 
    -- SELECT goename.point_from_via(123,1515);
    --
    p_via_id    IN integer,            -- ID da via
    numpredial  IN integer default 0,  -- número predial (zero é o inicio).
    exato       OUT boolean            -- var externa para saber se foi exato ou interpolado.
    ) RETURNS geometry AS $BODY$
	DECLARE
             pminmax integer[];
             p integer;
             ret_geom geometry;
	BEGIN
             SELECT the_geom INTO ret_geom FROM goename.exactpoint_from_via(p_via_id,numpredial);
             IF FOUND THEN
		exato = true;
		RETURN ret_geom;
             ELSE
		exato = false;
		RETURN goename.interpolate_from_via(p_via_id,numpredial);
	     END IF;
	END;
$BODY$ LANGUAGE plpgsql;

SQL funções complementares

Sobrecargas e conversões

Sobrecardas nas funções principais e conversões complementares, para reuso das funções principais. 

CREATE FUNCTION  goename.Resolver(
    -- v1.0.0
    -- Sobrecarga para goename.Resolver(), para estabelecer contexto por geometria dada.
    --
    -- Example(s): 
    -- SELECT goename.Resolver('cidade:sao.carlos;sp;br',NULL,1,the_geom,'geom.polygon');
    --
    urn         IN varchar,               -- endereço completo com número predial no final.
    metaphone   IN varchar default NULL,  -- tipo de endereço.
    max_results IN integer default 10,    -- máximo de resultados no retorno.
    p_geom    IN geometry                 -- polígono de contextualizacao
    retype      IN integer default 'geom.point' -- formato do retorno.
    afetascore  IN boolean default false  -- indica se p_geom deve afetar score ou não.
    filtrar     IN boolean default false  -- indica se p_geom deve filtrar resultados ou não.
    ) RETURNS setof record AS $BODY$
	DECLARE
		context_id integer;
		the_area float;
		ret setof record;
	BEGIN
		SELECT ST_Area(p_geom) INTO the_area;
		SELECT id INTO context_id FROM goename.contexts WHERE area>=the_area AND the_geom && p_geom LIMIT 1;
		IF FOUND THEN
			ret = goename.Resolver(urn,metaphone,max_results,context_id,retype);
			IF (ret.n>1 AND filtrar) -- mais de um resultado
				-- falta considerar flag afetascore e tratamento de escore
				RAISE EXCEPTION '... EM CONSTRUCAO ...';
			ELSE
				RETURN ret;
			ENDIF;

		ELSE
			RAISE EXCEPTION 'sem contexto definido para a geometria dada';
		ENDIF;
	END;
$BODY$ LANGUAGE plpgsql;

Especializações de goename.geocode

Drivers para geocodificação direta, resolução direta de nomes. 

CREATE FUNCTION  goename.geocode_code (
    -- v1.0.0
    -- Geocodifica código conforme padrão requerido. Resolve CDC, Inscrição Imobiliária, etc.
    -- Quando outAsType='auto', assumo default dado conforme codtype.
    -- Códigos não requerem processamento por dicionário ou Metaphone, nem intermediação por URN-resolver.
    -- Códigos devem ser ou exatos ou expressões regulares (/p_cod/) ou expressões contendo '%'.
    -- 
    -- Example(s): 
    -- SELECT goename.geocode_code('12008001001','im',123); -- Inscrição Imobiliária
    -- SELECT goename.geocode_code('/^12.+001/','im',123); -- iniciando por 12 e teminando por 001
    -- SELECT goename.geocode_code('12%001','im',123); -- iniciando por 12 e teminando por 001
    -- SELECT goename.geocode_code('10-111','sqf',123); -- Setor-Quadra Fiscal
    -- SELECT goename.geocode_code('123','cdc',123); -- Código do Consumidor (CdC) do SAAE
    -- SELECT goename.geocode_code('04569-010','cep',200); -- CEP (Correios)
    --
    p_cod       IN varchar,               -- código alfanumérico.
    codtype     IN varchar,               -- descritor de tipo ('cdc', 'im', etc.).
    max_results IN integer DEFAULT  10,   -- máximo de resultados no retorno.
    context_id  IN integer DEFAULT  NULL  -- id da entidade de contexto (id da cidade).
    outAsType   IN varchar DEFAULT 'geom' -- formato de retorno conforme goename.output_geometry()
    ) RETURNS setof record AS $BODY$ 
	DECLARE
		isExpr integer := false;
		isLike boolean := false;
		retgeom geometry := NULL;
		ret setof record;
	BEGIN
		IF substring(p_cod from 1 for 1)='/' THEN
			isRegex:=true;
			p_cod := trim(p_cod,'/');
		ELSEIF position('%' in p_cod)>0 THEN
			isLike:=true;
		END IF;

		CASE codtype

		   WHEN 'im' THEN  -- geocode/im DRIVER
		        IF outAsType='auto' THEN outAsType:='geom.poli'; END IF;
		        -- remove '.', '-', etc. do código? inteiro?
		        IF isLike THEN
			   SELECT as(the_geom) INTO retgeom 
			   FROM goename.code_lookup_im WHERE code LIKE p_cod 
			   ORDER BY code
			   LIMIT max_results;
		        ELSEIF isExpr THEN 
			   SELECT as(the_geom) INTO retgeom 
			   FROM goename.code_lookup_im WHERE code ~* p_cod 
			   ORDER BY code
			   LIMIT max_results;
		        ELSE 
			   SELECT as(the_geom) INTO retgeom 
			   FROM goename.code_lookup_im WHERE code=p_cod;
		        END IF;

		   WHEN 'cdc' THEN   -- geocode/cdc DRIVER
		        IF outAsType='auto' THEN outAsType:='geom.point'; END IF;
		        IF isLike THEN
			   SELECT as(the_geom) INTO retgeom 
			   FROM public.pontos_cdc WHERE cdc::varchar LIKE p_cod 
			   ORDER BY cdc
			   LIMIT max_results;
		        ELSEIF isExpr THEN 
			   SELECT as(the_geom) INTO retgeom 
			   FROM public.pontos_cdc WHERE cdc::varchar ~* p_cod 
			   ORDER BY cdc
			   LIMIT max_results;
		        ELSE 
			   SELECT as(the_geom) INTO retgeom 
			   FROM public.pontos_cdc WHERE cdc=p_cod::integer;
		        END IF;

		   WHEN '...' THEN
		        ...

		   ELSE 
		        RAISE EXCEPTION 'codtype desconhecido: %',codtype;

		END CASE;
		ret.geom = goename.output_geometry(retgeom,outAsType);
		ret.etc : = ...
		RETURN ret;
$BODY$ LANGUAGE plpgsql;


CREATE FUNCTION  goename.geocode_name (
    -- v1.0.0
    -- Geocodifica nome de cidade, de bairro, de distrito ou de "landmark", dados por URN.
    -- Requerem processamento com metaphone e dicionário.
    -- Faz uso do URN-Resolver.
) RETURNS setof record AS $BODY$ ... $BODY$ LANGUAGE plpgsql;


CREATE FUNCTION  goename.geocode_address (
    -- v1.0.0
    -- Geocodifica endereço dado por URN e parâmetros opcionais (CEP, Bairro, etc.)
) RETURNS setof record AS $BODY$ ... $BODY$ LANGUAGE plpgsql;

Úteis

CREATE FUNCTION goename.interpolate_fraction(INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR)
-- This function converts string addresses to integers and passes them to
-- the other interpolate_ratio function.
RETURNS FLOAT AS $_$
BEGIN
  RETURN goename.interpolate_fraction($1, to_number($2,'999999'), to_number($3,'999999'), to_number($4,'999999'),to_number($5,'999999'), $6);
END
$_$ LANGUAGE plpgsql;


CREATE FUNCTION goename.interpolate_fraction(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) 
-- interpolate_from_address(local_address, from_address_l, to_address_l, from_address_r, to_address_r)
-- This function returns the fraction for line_interpolate_point().
-- corresponding to the given address.  If the given address is not within
-- the address range of the road, null is returned.
-- This function requires that the address be grouped, such that the second and
-- third arguments are from one side of the street, while the fourth and
-- fifth are from the other.
-- REF: function interpolate_from_address() of the PostGIS tiger_geocoder framework.
RETURNS FLOAT AS $_$
DECLARE
  given_address INTEGER;
  lmaxaddr INTEGER := -1;
  rmaxaddr INTEGER := -1;
  lminaddr INTEGER := -1;
  rminaddr INTEGER := -1;
  lfrgreater BOOLEAN;
  rfrgreater BOOLEAN;
  frgreater BOOLEAN;
  addrwidth INTEGER;
  part FLOAT;
BEGIN
  IF $1 IS NULL THEN
    RETURN NULL;
  ELSE
    given_address := $1;
  END IF;

  IF $2 IS NOT NULL THEN
    lfrgreater := TRUE;
    lmaxaddr := $2;
    lminaddr := $2;
  END IF;

  IF $3 IS NOT NULL THEN
    IF $3 > lmaxaddr OR lmaxaddr = -1 THEN
      lmaxaddr := $3;
      lfrgreater := FALSE;
    END IF;
    IF $3 < lminaddr OR lminaddr = -1 THEN
      lminaddr := $3;
    END IF;
  END IF;

  IF $4 IS NOT NULL THEN
    rmaxaddr := $4;
    rminaddr := $4;
    rfrgreater := TRUE;
  END IF;

  IF $5 IS NOT NULL THEN
    IF $5 > rmaxaddr OR rmaxaddr = -1 THEN
      rmaxaddr := $5;
      rfrgreater := FALSE;
    END IF;
    IF $5 < rminaddr OR rminaddr = -1 THEN
      rminaddr := $5;
    END IF;
  END IF;

  IF given_address >= lminaddr AND given_address <= lmaxaddr THEN
    IF (given_address % 2) = (lminaddr % 2)
        OR (given_address % 2) = (lmaxaddr % 2) THEN
      addrwidth := lmaxaddr - lminaddr;
      part := (given_address - lminaddr) / trunc(addrwidth, 1);
      frgreater := lfrgreater;
    END IF;
  END IF;

  IF given_address >= rminaddr AND given_address <= rmaxaddr THEN
    IF (given_address % 2) = (rminaddr % 2)
        OR (given_address % 2) = (rmaxaddr % 2) THEN
      addrwidth := rmaxaddr - rminaddr;
      part := (given_address - rminaddr)::float / trunc(rmaxaddr - rminaddr, 1)::float;
      frgreater := rfrgreater;
    END IF;
  END IF;

  IF frgreater THEN
    part := 1 - part;
  END IF;
  RETURN part;
END;
$_$ LANGUAGE plpgsql;

Dicionário

Ver e testar inicializações

...

SQL tabelas e views

...

Ver também

Retrieved from "https://wiki.openstreetmap.org/w/index.php?title=Brazil/PostGIS-testes&oldid=2668790"