2 538
edições
(add funcoes e seu relatorio) |
(correct descriptors) |
||
Linha 1: | Linha 1: | ||
Tabelas da base corrente <code>DL05s_main</code>. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki]. | Tabelas da base corrente <code>DL05s_main</code>. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki]. | ||
== Table <code> | == Table <code>optim.jurisdiction</code> == | ||
Jurisdições nacionais e locais, sem geometrias (ver tabela complementar _geom). | |||
{| class="wikitable" | |||
! 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. | |||
|} | |||
<small><pre> | |||
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 | |||
</pre></small> | |||
== Table <code>osmc.coverage</code> == | == Table <code>osmc.coverage</code> == | ||
Linha 9: | Linha 72: | ||
{| class="wikitable" | {| class="wikitable" | ||
! Column !! Type !! | ! Column !! Type !! Default !! Description | ||
|- | |- | ||
| cbits || bit varying || | | cbits || bit varying || || Coverage cell (hierarchical) identifier. | ||
|- | |- | ||
| isolabel_ext || text || | | isolabel_ext || text || || Jurisdiction identifier. ISO 3166-2 code extended for subjurisdiction name (camel case); e.g. BR-SP-SaoPaulo. | ||
|- | |- | ||
| cindex || text || | | cindex || text || || First digit of the coverage cell, a prefix in base 32nvu. Used only case is_country=false. | ||
|- | |- | ||
| bbox || integer[] || | | bbox || integer[] || || Coverage cell BBOX. | ||
|- | |- | ||
| status || smallint || | | status || smallint || 0 || Coverage status. Convention: 0: generated, 1: revised, 2: homologated. | ||
|- | |- | ||
| is_country || boolean || | | is_country || boolean || false || True if it is a cell of national coverage. | ||
|- | |- | ||
| is_contained || boolean || | | is_contained || boolean || false || True if it is a cell contained in the jurisdiction. Must validate. | ||
|- | |- | ||
| is_overlay || boolean || | | is_overlay || boolean || false || True if it is an overlay cell. | ||
|- | |- | ||
| kx_prefix || text || | | kx_prefix || text || || Coverage cell prefix in base 32nvu. | ||
|- | |- | ||
| geom || geometry || | | geom || geometry || || Coverage cell geometry on country's projection (local SRID). | ||
|- | |- | ||
| geom_srid4326 || geometry || | | geom_srid4326 || geometry || || Coverage cell geometry on WGS84 LatLong (4326 srid). Used only case is_country=true. | ||
|} | |} | ||
<pre> | |||
<small><pre> | |||
Indexes: | Indexes: | ||
"osm_coverage_cbits10true_idx" btree ((cbits::bit(10))) WHERE is_country IS TRUE | "osm_coverage_cbits10true_idx" btree ((cbits::bit(10))) WHERE is_country IS TRUE | ||
Linha 44: | Linha 108: | ||
Check constraints: | Check constraints: | ||
"coverage_status_check" CHECK (status = ANY (ARRAY[0, 1, 2])) | "coverage_status_check" CHECK (status = ANY (ARRAY[0, 1, 2])) | ||
</pre> | </pre></small> | ||
=== Queries === | === Queries === | ||
Queries que geram reports e sugestão de análise das views próprias. | Queries que geram reports e sugestão de análise das views próprias. |
edições