WIKI de apuntes de Billy Rondón

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== Ubigeos Perú Manejo ======

Fuente de data:

  * [[https://github.com/CONCYTEC/ubigeo-peru]]


====== Crear una tabla de Ubigeos ======

Importo la tabla en csv con todos los campos varchar(200)

<code sql>

-- Para PostgreSQL
select * from equivalencia_ubigeos_oti_concytec_csv

</code>

-- el query de SQL

<code sql>
create table ubigeos_dep(
	
	cod_dep_inei CHAR(2) unique PRIMARY KEY,
	desc_dep_inei VARCHAR(13)

);
create UNIQUE index idx_ubigeos_dep_desc_dep_inei
	on ubigeos_dep using BTREE(desc_dep_inei)

insert into ubigeos_dep( cod_dep_inei, desc_dep_inei )
select cod_dep_inei, desc_dep_inei from equivalencia_ubigeos_oti_concytec_csv
where desc_dep_inei <> 'NA'
group by cod_dep_inei, desc_dep_inei
order by cod_dep_inei ASC

-----------

CREATE table ubigeos_prov(
	cod_prov_inei CHAR(4) unique primary KEY,
	cod_dep_inei CHAR(2),
	desc_prov_inei VARCHAR(25),
	constraint fk_ubigeos_prov_dep
		foreign KEY(cod_dep_inei)
			references ubigeos_dep(cod_dep_inei)
);
create index idx_ubigeos_prov_cod_dep_inei
	on ubigeos_prov using BTREE(cod_dep_inei);
create UNIQUE index idx_ubigeos_prov_desc_prov_inei
	on ubigeos_prov using BTREE(desc_prov_inei)

INSERT INTO ubigeos_prov(cod_prov_inei,cod_dep_inei,desc_prov_inei)
select 
	cod_prov_inei,
	cod_dep_inei,
	desc_prov_inei
from equivalencia_ubigeos_oti_concytec_csv
where desc_prov_inei <> 'NA'
group by cod_prov_inei,
		 cod_dep_inei,
		 desc_prov_inei
order by cod_prov_inei ASC

---------------


create table ubigeos_dist(
	cod_ubigeo_inei CHAR(6) unique primary key,
	cod_prov_inei CHAR(4),
	cod_dep_inei CHAR(2),
	desc_ubigeo_inei VARCHAR(36),
	constraint fk_dist_dep
		foreign key( cod_dep_inei )
			references ubigeos_dep( cod_dep_inei ),
	constraint fk_dist_prov
		foreign key ( cod_prov_inei )
			references ubigeos_prov( cod_prov_inei )
);
create index idx_ubigeos_dist_cod_dep
	on ubigeos_dist using BTREE(cod_dep_inei);
create index idx_ubigeos_dist_cod_prov
	on ubigeos_dist using BTREE(cod_prov_inei);
create index idx_ubigeos_dist_desc_ubigeo
	on ubigeos_dist using BTREE(desc_ubigeo_inei)

insert into ubigeos_dist(
	cod_ubigeo_inei,
	cod_prov_inei,
	cod_dep_inei,
	desc_ubigeo_inei
)
select 
	cod_ubigeo_inei,
	cod_prov_inei,
	cod_dep_inei, 
	desc_ubigeo_inei
from equivalencia_ubigeos_oti_concytec_csv
where desc_ubigeo_inei <> 'NA'
order by cod_ubigeo_inei ASC

-----

--drop view vst_ubigeos
create view vst_ubigeos as 
select 
	d.cod_ubigeo_inei as cod_ubigeo,
		d.desc_ubigeo_inei as desc_ubigeo,
	p.cod_prov_inei as cod_prov,
		p.desc_prov_inei as desc_prov,
	e.cod_dep_inei as cod_dep,
		e.desc_dep_inei as desc_dep
from ubigeos_dist as d
left join ubigeos_prov as p
	on p.cod_prov_inei  = d.cod_prov_inei 
left join ubigeos_dep as e
	on e.cod_dep_inei = d.cod_dep_inei
	

</code>
  * [[https://www.postgresqltutorial.com/postgresql-foreign-key/]]
  * [[https://www.w3resource.com/PostgreSQL/primary-key-constraint.php#:~:text=The%20PostgreSQL%20PRIMARY%20KEY%20is,NOT%20NULL%20and%20UNIQUE%20constraint.]]
==== sobre los indexados rtree btree =====

Index types : The optional USING clause can be used to specify the type of index to implement. PostgreSQL 7.1.x supports three types of indices including:
  - B-tree
  - R-tree
  - Hash

  * [[https://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c13329_001.htm]]

"Prior releases of PostgreSQL also had an R-tree index method. This method has been removed because it had no significant advantages over the GiST method. If USING rtree is specified, CREATE INDEX will interpret it as USING gist, to simplify conversion of old databases to GiST."
  * [[https://www.postgresql.org/docs/9.1/sql-createindex.html]]

-----------

  * Huayllo a Ihuayllo [[https://www2.congreso.gob.pe/Sicr/Prensa/heraldo.nsf/CNtitulares2/8588D837B2A075B505257ED0006FE355/?OpenDocument]]