Usuário:Peter/Lembretes SQL e docs
Tabelas da base corrente DL05s_main
. Documentação gerada por recursos do "psql" e tableconvert.com/markdown-to-mediawiki.
Ver também Usuário:Peter/Modelos endereço
Table optim.jurisdiction
Jurisdições nacionais e locais, sem geometrias (ver tabela complementar _geom).
Column | Type | Nullable | Description |
---|---|---|---|
osm_id | bigint | not null | Relation identifier in OpenStreetMap. |
jurisd_base_id | integer | not null | ISO3166-1-numeric COUNTRY ID (e.g. Brazil is 76) or negative for non-iso (ex. oceans). |
jurisd_local_id | integer | not null | Numeric official ID like IBGE_ID of BR jurisdiction. For example ACRE is 12 and its cities are {1200013, 1200054,etc}. |
parent_id | bigint | osm_id of top admin_level. | |
admin_level | smallint | not null | OSM convention for admin_level tag in country. |
name | text | not null | Name of jurisdiction |
parent_abbrev | text | not null | Abbreviation of parent name. |
abbrev | text | Name abbreviation. | |
wikidata_id | bigint | wikidata identifier without Q prefix. | |
lexlabel | text | not null | Cache from name; e.g. sao.paulo. |
isolabel_ext | text | not null | Cache from parent_abbrev (ISO) and name (camel case); e.g. BR-SP-SaoPaulo. |
ddd | integer | Direct distance dialing. | |
housenumber_system_type | text | Housenumber system. | |
lex_urn | text | Housenumber system law. | |
info | jsonb | Others information. | |
name_en | text | City name in english. | |
isolevel | integer | 1=country, 2=state, 3=mun | |
ne_country_id | integer | NaturalEarthData country gid. | |
int_country_id | integer | One byte identifier, adopted for internal use. Shorter than ISO and NaturalEarthData. |
Indexes: "jurisdiction_pkey" PRIMARY KEY, btree (osm_id) "jurisdiction_isolabel_ext_idx1" btree (isolabel_ext) "jurisdiction_isolabel_ext_key" UNIQUE CONSTRAINT, btree (isolabel_ext) "jurisdiction_jurisd_base_id_jurisd_local_id_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, jurisd_local_id) "jurisdiction_jurisd_base_id_parent_abbrev_abbrev_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, abbrev) "jurisdiction_jurisd_base_id_parent_abbrev_lexlabel_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, lexlabel) "jurisdiction_jurisd_base_id_parent_abbrev_name_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, name) "jurisdiction_wikidata_id_key" UNIQUE CONSTRAINT, btree (wikidata_id) Check constraints: "jurisdiction_admin_level_check" CHECK (admin_level > 0 AND admin_level < 100) "jurisdiction_name_check" CHECK (length(name) < 60) Foreign-key constraints: "jurisdiction_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES optim.jurisdiction(osm_id) Referenced by: TABLE "optim.donor" CONSTRAINT "donor_scope_osm_id_fkey" FOREIGN KEY (scope_osm_id) REFERENCES optim.jurisdiction(osm_id) TABLE "optim.jurisdiction" CONSTRAINT "jurisdiction_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES optim.jurisdiction(osm_id) Access method: heap
Queries
Queries que geram reports e sugestão de análise das views próprias.
-- R0a
select '* '||vw_name from optim.admin_views_dependency_summary
where ref_tab_name='optim.jurisdiction' order by 1
;
-- R0b
select schema_name, name, arguments_simplified , return_type
from doc_UDF_using_a_table('optim.jurisdiction') t order by 1,2,3
;
...
Reports
R0a. Views que fazem uso da tabela:
- api.jurisdiction: reproduz a tabela na API.
- api.stats_donated_packcomponent: fornece estatísticas de pacotes doados, organizada por jurisdição.
- optim.jurisdiction_lexlabel: reformata isolabel_ext como URN LEX.
- optim.vw01donorevidencecmd:
- optim.vw01full_donated_packtpl: pacotes doados em join completo.
- optim.vw01full_jurisdiction_geom: jurisdição completa, com geometria.
- optim.vw01int_country_id: (lixo) lista o ID interno de país.
- optim.vw02generate_list: gera JSON com árvore de países, com optim.vw01generate_list.
- optim.vw02publication: metadados para ato de publicação de um pacote.
- optim.vwjurisdiction_synonym: lista de-para de todos os sinônimos válidos de uma jurisdição (CEP, abreviação, URN LEX etc.).
- osmc.jurisdictions_select: JSON para interface de seletor de jurisdição.
- public.qwerty: (resgatar para optim) gerador de comando bash "curl" para criar CSVs de todas as jurisdições.
R0b. Funções que fazem uso da tabela:
schema_name | name | arguments_simplified | return_type |
---|---|---|---|
api | jurisdiction_autocomplete | {text} | jsonb |
api | osmcode_decode_postal | {text,text} | jsonb |
api | osmcode_encode | {text,integer} | jsonb |
api | osmcode_encode_sci | {text,integer} | jsonb |
optim | generate_synonym_csv | {text,text} | text |
optim | generate_synonym_ref_csv | {text} | text |
optim | insert_donor_pack | {text} | text |
optim | insert_jurisdpoint | {NULL} | text |
osmc | encode_short_code | {text,"bit varying",text,USER-DEFINED,text,integer,text} | record |
osmc | l0cover_upsert_co | {NULL} | text |
Table optim.jurisdiction_geom
Geometrias de #optim.jurisdiction. Geometrias das jurisdições nacionais e locais.
Column | Type | Nullable | Description |
---|---|---|---|
osm_id | bigint | not null | Relation identifier in OpenStreetMap. |
isolabel_ext | text | not null | ISO 3166-1 alpha-2 code and name (camel case); e.g. BR-SP-SaoPaulo. |
geom | geometry(Geometry,4326) | Geometry for osm_id identifier | |
geom_svg | geometry(Geometry,4326) | Simplified geometry version to use in svg interface. | |
kx_ghs1_intersects | text[] | ||
kx_ghs2_intersects | text[] |
Indexes: "jurisdiction_geom_pkey" PRIMARY KEY, btree (osm_id) "jurisdiction_geom_isolabel_ext_key" UNIQUE CONSTRAINT, btree (isolabel_ext) "optim_jurisdiction_geom_idx1" gist (geom) "optim_jurisdiction_geom_isolabel_ext_idx1" btree (isolabel_ext) "optim_jurisdiction_geom_svg_idx1" gist (geom_svg) Access method: heap
Table osmc.coverage
Coberturas nacionais e de suas jurisdições. As geometrias nacionais são expressas em LatLong, as locais conforme sua projeção oficial.
Ingestão: arquivos "coverage.csv" do git. Função ou make para construção??
Exemplos: BR_new/data/coverage.csv, CO_new/data/coverage.csv. As colunas "status" e "cover" são copiadas em "status" e "kx_prefix".
Column | Type | Default | Description |
---|---|---|---|
cbits | bit varying | Coverage cell (hierarchical) identifier. | |
isolabel_ext | text | Jurisdiction identifier. ISO 3166-2 code extended for subjurisdiction name (camel case); e.g. BR-SP-SaoPaulo. | |
cindex | text | First digit of the coverage cell, a prefix in base 32nvu. Used only case is_country=false. | |
bbox | integer[] | Coverage cell BBOX. | |
status | smallint | 0 | Coverage status. Convention: 0: generated, 1: revised, 2: homologated. |
is_country | boolean | false | True if it is a cell of national coverage. |
is_contained | boolean | false | True if it is a cell contained in the jurisdiction. Must validate. |
is_overlay | boolean | false | True if it is an overlay cell. |
kx_prefix | text | Coverage cell prefix in base 32nvu. | |
geom | geometry | Coverage cell geometry on country's projection (local SRID). | |
geom_srid4326 | geometry | Coverage cell geometry on WGS84 LatLong (4326 srid). Used only case is_country=true. |
Indexes: "osm_coverage_cbits10true_idx" btree ((cbits::bit(10))) WHERE is_country IS TRUE "osm_coverage_cbits15false_idx" btree ((cbits::bit(14)), isolabel_ext) WHERE is_country IS FALSE "osm_coverage_geom4326_idx1" gist (geom_srid4326) "osm_coverage_geom_idx1" gist (geom) "osm_coverage_isolabel_ext_false_idx" btree (isolabel_ext) WHERE is_country IS FALSE "osm_coverage_isolabel_ext_idx1" btree (isolabel_ext) "osm_coverage_isolabel_ext_true_idx" btree (isolabel_ext) WHERE is_country IS TRUE Check constraints: "coverage_status_check" CHECK (status = ANY (ARRAY[0, 1, 2]))
Queries
Queries que geram reports e sugestão de análise das views próprias.
-- R0a
select * from optim.admin_views_dependency_summary where ref_tab_name='osmc.coverage'
;
-- R0b
select schema_name, name, arguments_simplified , return_type
from doc_UDF_using_a_table('osmc.cover') t order by 1,2,3
;
-- R1
select distinct
substring(isolabel_ext,1,2) as country,
natcod.vbit_to_baseh(substring(cbits,1,8),16,true) as cbits_b16h,
is_country, status,
count(*) n, count(distinct isolabel_ext) n_jurisds
from osmc.coverage
group by 1,2,3,4
order by 1,2,3 desc,4;
-- R2a
select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
count(*) n, count(distinct isolabel_ext) n_jurisds, count(distinct cbits) n_cells,
min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
from (select *, round(length(cbits)/4)::int as l from osmc.coverage) t
group by 1,2,3 order by 1,2 desc,3;
-- R2b
select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
count(*) n, count(distinct isolabel_ext) n_jurisds,
count(distinct kx_prefix) n_cells, count(distinct kx_prefix||'.'||cindex::text) n_cells_idx,
count(distinct kx_prefix||'.'||isolabel_ext) n_cells_jur,
min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
from (select *, length(kx_prefix) as l from osmc.coverage) t
group by 1,2,3 order by 1,2 desc,3;
-- R3
select isolabel_ext, is_overlay,
array_agg( natcod.vbit_to_baseh(substring(cbits,11),16,true) order by cbits) cover
from osmc.coverage
where isolabel_ext like 'CO-ARA-%'
group by 1,2 order by 1,2;
-- R4. Testar contra qwerty2, que tem comjunto consistente. Aqui apenas CA e CO apresentam parte consistente.
select substr(isolabel_ext,1,2) as iso, len, round(avg(a),1) area_avg, max(a) area_max, count(*) n
from (
select isolabel_ext, length(cbits) len, round(st_area(geom)/10000.0) a, is_contained, is_overlay
from osmc.coverage where is_contained order by 2 desc,1
) t group by 1,2 order by 1,2
;
Reports
R0a. Views que fazem uso desta tabela:
- osmc.jurisdictions_select
- osmc.tmpvw10: temporária
- osmc.tmpvwpoeira: indica "sobras" dos polígonos que não foram aceitas como cobertura válida, por serem menores que 100 metros quadrados.
R0b. Funções que fazem uso desta tabela. (removidas as variantes "EC" e "UY")
schema_name | name | arguments_simplified | return_type |
---|---|---|---|
api | jurisdiction_coverage | {text,integer} | jsonb |
api | jurisdiction_geojson_from_isolabel | {text} | jsonb |
api | osmcode_decode_postal | {text,text} | jsonb |
api | osmcode_decode_postal_absolute | {text,text} | jsonb |
api | osmcode_decode_scientific_absolute | {text,text,integer} | jsonb |
api | osmcode_encode | {text,integer} | jsonb |
osmc | afa_log_to_hbig | {text,text} | int8 |
osmc | cellcontainsproperly | {text,text,integer,text,integer} | text |
osmc | check_coverage | {text,ARRAY,text,ARRAY,ARRAY,ARRAY,ARRAY,boolean,ARRAY} | record |
osmc | cover_child_geometries | {text,text,integer,text,text,USER-DEFINED} | record |
osmc | encode_point_brazil | {USER-DEFINED} | text |
osmc | encode_point_colombia | {USER-DEFINED} | text |
osmc | encode_postal_br | {USER-DEFINED,"double precision",integer,text} | jsonb |
osmc | encode_postal_cm | {USER-DEFINED,"double precision",integer,text} | jsonb |
osmc | encode_postal_co | {USER-DEFINED,"double precision",integer,text} | jsonb |
osmc | encode_scientific_br | {USER-DEFINED,"double precision",integer} | jsonb |
osmc | encode_scientific_cm | {USER-DEFINED,"double precision",integer} | jsonb |
osmc | encode_scientific_co | {USER-DEFINED,"double precision",integer} | jsonb |
osmc | encode_short_code | {text,"bit varying",text,USER-DEFINED,text,integer,text} | record |
osmc | generate_cover_csv | {text,text} | text |
osmc | l0_upsert | {text,smallint,integer,integer,integer,integer,integer,ARRAY,ARRAY} | text |
osmc | l0cover_upsert_br | {NULL} | text |
osmc | l0cover_upsert_co | {NULL} | text |
osmc | update_coverage_isolevel3 | {text,smallint,ARRAY,ARRAY} | text |
R1. Sumário do conteúdo corrente (final de 2023). Diferencia coberturas nacionais, expressa contagem de células e contagem de jurisdições.
country | cbits_b16h | is_country | status | n | n_jurisds |
---|---|---|---|---|---|
BR | 13 | t | 1 | 18 | 1 |
BR | 13 | f | 0 | 72726 | 5570 |
CM | 1e | t | 1 | 14 | 1 |
CM | 1e | f | 0 | 2738 | 360 |
CO | 2a | t | 1 | 16 | 1 |
CO | 2a | f | 0 | 15244 | 1115 |
CO | 2a | f | 2 | 22 | 1 |
EC | 36 | t | 1 | 22 | 1 |
UY | d6 | t | 1 | 19 | 1 |
UY | d6 | f | 0 | 1 | 1 |
R2a. Tamanho mais frequente (mediana "mdn") da cobertura. Comprimento l dado em quantidade de dígitos hexadecimais.
PS: ignorando EC e UY, que não estão configurados.
country | is_country | is_overlay | n | n_jurisds | l_min | l_max | l_avg | l_mdn |
---|---|---|---|---|---|---|---|---|
BR | t | f | 18 | 1 | 4 | 4 | 4.00 | 4 |
BR | f | f | 72565 | 5570 | 5 | 9 | 7.77 | 8 |
BR | f | t | 161 | 11 | 7 | 9 | 8.11 | 8 |
CM | t | f | 14 | 1 | 3 | 3 | 3.00 | 3 |
CM | f | f | 2730 | 360 | 3 | 7 | 5.48 | 5 |
CM | f | t | 8 | 1 | 7 | 7 | 7.00 | 7 |
CO | t | f | 16 | 1 | 3 | 3 | 3.00 | 3 |
CO | f | f | 14165 | 1116 | 4 | 8 | 5.82 | 6 |
CO | f | t | 1101 | 1091 | 6 | 9 | 7.39 | 8 |
R2b. Mesmo que R2a porém usando "kx_prefix" direto. Linhas is_country removidas pois não usam "kx_prefix".
country | is_ctry | is_ovly | n | n_jurisds | n_cells | n_cel_idx | n_cel_jur | l_min | l_max | l_avg | l_mdn |
---|---|---|---|---|---|---|---|---|---|---|---|
BR | f | f | 72565 | 5570 | 40933 | 69557 | 72565 | 2 | 5 | 3.77 | 4 |
BR | f | t | 161 | 11 | 161 | 161 | 161 | 3 | 5 | 4.11 | 4 |
CM | f | f | 2730 | 360 | 1411 | 2302 | 2626 | 2 | 5 | 3.50 | 3 |
CM | f | t | 8 | 1 | 4 | 4 | 4 | 5 | 5 | 5.00 | 5 |
CO | f | f | 14165 | 1116 | 7552 | 13530 | 14165 | 2 | 5 | 3.82 | 4 |
CO | f | t | 1101 | 1091 | 1100 | 1100 | 1101 | 4 | 6 | 4.70 | 5 |
R3. Para reproduzir a planilha CSV precisamos agrupar cbits e converte-los em base16h. Exemplo, municípios de "CO-ARA". As coberturas-overlay ainda não aparecem no git, e aqui apenas a célula de um ponto é utilizada.
isolabel_ext | is_overlay | n | cover |
---|---|---|---|
CO-ARA-Arauca | f | 14 | {cd4,cd5,cd6,cd7,e2a,e2e,e2f,e80,e81,e82,e83,e84,e85,e86} |
CO-ARA-Arauca | t | 1 | {cd7fG} |
CO-ARA-Arauquita | f | 9 | {c7e,c7f,cd0,cd1,cd2,cd3,cd4,cd5,cd6} |
CO-ARA-Arauquita | t | 1 | {cd3cQ} |
CO-ARA-CravoNorte | f | 13 | {e28,e2a,e2b,e2c,e2d,e2e,e2f,e38,e3a,e80,e81,e84,e85} |
CO-ARA-CravoNorte | t | 1 | {e2b5Q} |
CO-ARA-Fortul | f | 5 | {c6f,cc5,cd0,cd1,cd2} |
CO-ARA-Fortul | t | 1 | {cd0bG} |
CO-ARA-PuertoRondon | f | 9 | {c7d,c7e,c7f,cd1,cd4,cd5,e28,e2a,e80} |
CO-ARA-PuertoRondon | t | 1 | {c7e4Q} |
CO-ARA-Saravena | f | 4 | {cc5,cc7,cd0,cd2} |
CO-ARA-Saravena | t | 1 | {cd22Q} |
CO-ARA-Tame | f | 13 | {c6c,c6d,c6e,c6f,c78,c79,c7a,c7b,c7e,cc5,cd0,cd1,cd4} |
CO-ARA-Tame | t | 1 | {c7acQ} |
Outros lembretes
CREATE VIEW optim.admin_views_dependency AS -- https://stackoverflow.com/a/69473861/287948 select u.view_schema schema_name, u.view_name, u.table_schema referenced_table_schema, u.table_name referenced_table_name, v.view_definition from information_schema.view_table_usage u join information_schema.views v on u.view_schema = v.table_schema and u.view_name = v.table_name where u.table_schema not in ('information_schema', 'pg_catalog') order by u.view_schema, u.view_name ; CREATE VIEW optim.admin_views_dependency_summary AS select schema_name||'.'||view_name as vw_name, referenced_table_schema||'.'||referenced_table_name as ref_tab_name from optim.admin_views_dependency ; -- DROP VIEW optim.admin_udf_summary; CREATE VIEW optim.admin_udf_summary AS SELECT n.oid, quote_ident(n.nspname) as schema , quote_ident(p.proname) as function, pg_get_function_result(n.oid) as fres FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname not like 'pg%' ; CREATE or replace FUNCTION doc_UDF_using_a_table( p_table text, p_schema_name text DEFAULT NULL, p_name_like text DEFAULT '', p_name_notlike text DEFAULT '', p_oid oid DEFAULT NULL ) RETURNS TABLE ( id text, oid oid, schema_name text, name text, language text, --new definition_md5 text, -- version control arguments_simplified text[], arguments text, return_type text, prokind text, comment text ) AS $f$ SELECT doc_UDF_transparent_id(u.schema_name, u.name::text, s.arguments_simplified::text[]) AS id, u.oid, u.schema_name, u.name::text, u.language, md5(u.definition), s.arguments_simplified::text[] as arguments_simplified, u.arguments::text AS arguments, u.return_type, u.prokind, u.comment FROM doc_UDF_show_simplified_signature($2,$3,$4) s INNER JOIN doc_UDF_show($2,$3,$4,$5) u ON s.oid=u.oid::text WHERE u.definition ilike '%'|| p_table ||'%' $f$ LANGUAGE SQL IMMUTABLE;