Skip to content

optim corrections

Jump to bottom
Peter edited this page Dec 27, 2021 · 7 revisions

Ver por exemplo http://osm.codes/_sql/rpc/resolver_geo_uri?geouri=11,12 as implementações a seguir (WORKING/osm-grids/preparo-jur.sql) garantem que as coordenadas serão resolvidas em qualquer lugar

on co_ba

(não precisava, bastava usar jtags!)

psql postgres://postgres@localhost/co_ba  -c "create table lixo_divipola_geom AS SELECT -osm_id as osm_id, 170 as jurisd_base_id, (tags->>'divipola')::int jurisd_local_id, substr(tags->>'divipola',1,2)::int as cod, tags->>'is_in:state' as state_name, way as geom FROM jplanet_osm_polygon where tags?'is_in:state' AND tags?'divipola'"
# SELECT 1034
pg_dump -t lixo_divipola_geom postgres://postgres@localhost/co_ba | psql  postgres://postgres@localhost/dl03t_main
# ...
psql postgres://postgres@localhost/co_ba  -c "DROP table lixo_divipola_geom"

psql postgres://postgres@localhost/co_ba  -c "create table lixo_divipola_state AS SELECT DISTINCT tags->>'is_in:state' as name, substr(tags->>'divipola',1,2)::int as cod FROM jplanet_osm_polygon where tags?'is_in:state' AND tags?'divipola'"
pg_dump -t lixo_divipola_state postgres://postgres@localhost/co_ba | psql  postgres://postgres@localhost/dl03t_main
psql postgres://postgres@localhost/co_ba  -c "DROP table lixo_divipola_state"

on DL03

ALTER TABLE osm_city           ADD COLUMN isolevel int default -1 NOT NULL;
ALTER TABLE optim.jurisdiction ADD COLUMN isolevel int default -1 NOT NULL;
UPDATE osm_city           SET isolevel = cardinality(regexp_split_to_array(isolabel_ext,'\-')) WHERE isolabel_ext>'';
UPDATE optim.jurisdiction SET isolevel = cardinality(regexp_split_to_array(isolabel_ext,'\-'));

--select count(*)  -- teste
--from osm_city c INNER JOIN lixo_divipola_geom d ON d.osm_id=c.osm_id AND st_equals(d.geom,c.geom);

UPDATE osm_city
  SET jurisd_local_id=d.jurisd_local_id
FROM lixo_divipola_geom d 
WHERE d.jurisd_base_id=170 AND osm_city.jurisd_base_id=170  AND  osm_city.osm_id=d.osm_id AND d.jurisd_local_id>0 AND osm_city.jurisd_local_id IS NULL;

UPDATE optim.jurisdiction
   SET jurisd_local_id=d.cod
FROM lixo_divipola_state d
where admin_level=4 AND jurisd_base_id=170 AND d.name=jurisdiction.name;

-- UPDATE optim.jurisdiction SET WHERE? osm_id=1387968

Corrigindo Bogotá

OLD:

osm_id  | jurisd_base_id | jurisd_local_id |  name  | parent_abbrev | abbrev | wikidata_id | lexlabel | isolabel_ext | ddd | info | admin_level | parent_id | name_en | isolevel 
--------+----------------+-----------------+--------+---------------+--------+-------------+----------+--------------+-----+------+-------------+-----------+---------+----------
7426387 |            170 |              11 | Bogotá | CO            | DC     |        2841 | dc       | CO-DC        |   1 |      |           4 |    120027 | Bogota  |        2

DELETE FROM optim.jurisdiction WHERE osm_id=7426387;  -- é o OSM_ID de área urbana

UPDATE osm_city
  SET jurisd_local_id=11001, abbrev='CO-DC', lexlabel='bogota' --isolabel_ext='CO-DC-Bogota'
WHERE osm_id=1387968;

O osm_id correto é de https://www.openstreetmap.org/relation/1387968 que vai migrar automaticamente com as cidades de OSM_city.

isolabel_ext dos municípios de Colombia

CREATE view vw_tmp_lixo_divipola_geom AS
 WITH state_abbrev AS ( 
  select osm_id, isolabel_ext as isoprefix, name, jurisd_local_id, substr(isolabel_ext,4) as abbrev 
  from optim.jurisdiction j 
  WHERE admin_level=4 AND jurisd_base_id=170
 ) 
 SELECT d.*, s.abbrev AS parent_abbrev, s.isoprefix
 FROM state_abbrev s INNER JOIN lixo_divipola_geom d
   ON  d.cod=s.jurisd_local_id
;

UPDATE osm_city
  SET  isolevel = 3,  parent_abbrev = d.parent_abbrev,
       isolabel_ext = d.isoprefix ||'-'|| unaccent(translate( regexp_replace(name,' de l[ao] | del | de | [lL]a |\([^\)]+\)','','g'), ' ', ''))
FROM vw_tmp_lixo_divipola_geom d 
WHERE osm_city.jurisd_base_id=170  AND  osm_city.isolabel_ext is null
      AND osm_city.osm_id=d.osm_id AND d.jurisd_local_id>0;

-- correcoes:
update  osm_city set jurisd_local_id=13873 where osm_id=11319054;
delete from osm_city WHERE osm_id=1554330; -- Área en litigio
delete from osm_city  where osm_id=11374887; -- duplicado no OSM!
delete from osm_city where osm_id=11322770;  -- cod divipola no errado no OSM
delete from osm_city where osm_id=11313104;  -- cod divipola no errado no OSM

-- Diversos erros no OSM:
select jurisd_base_id, jurisd_local_id, count(*), array_agg(osm_id) ids FROM osm_city group by 1,2 having count(*)>1;
delete from osm_city where osm_id IN (4060664,2463270,2460349,4060699,4062807,10688531,11909704,10298142,11895071,4061117,4046923,3947350,11889093,11893926,11909823,1554702,11893921,1343043,1305052,4079108,4047009,7229628,7229667,-62089775,4047301,4046960,4047593,4047308,10688524,4047171,4047422,9175595,4079109,1473606,4060568,4046886,5794061,4079004,10688130,11889894,11888630,10688527,11896741,8347238,10687625,10688525,10688529,4052037,120027,10688530,11895070,1314687,11891528,11323562,2721167,2460383,4060575,7296967,10688528,4046719,10688523,10688131,11893635,4840466,11890103);
------


-----
INSERT INTO optim.jurisdiction 
       (osm_id, jurisd_base_id, jurisd_local_id, name, parent_abbrev, wikidata_id,  isolabel_ext,  isolevel, info, lexlabel)
  SELECT osm_id, jurisd_base_id, jurisd_local_id, name, parent_abbrev,
         substring(jtags->>'wikidata',2)::bigint,  isolabel_ext,  isolevel,
         jtags - 'name' - 'divipola' - 'wikidata' - 'clcfile' - 'is_in:country',
         lower(unaccent(translate( trim(regexp_replace(name,' de l[ao] | del | de | [lL]a |\([^\)]+\)',' ','g')), ' ', '.')))
  FROM osm_city
  WHERE jurisd_base_id=170 AND isolevel = 3
;

Correções em countries

ALTER TABLE countries
  ADD COLUMN osm_id bigint DEFAULT -1 NOT NULL,
  ADD COLUMN jurisd_base_id int DEFAULT -1 NOT NULL
;
UPDATE countries
   SET osm_id=j.osm_id,
       jurisd_base_id=j.jurisd_base_id
FROM optim.jurisdiction j 
WHERE j.admin_level=2 AND j.jurisd_base_id=countries.iso_n3::int
;
alter table osm_city add column ingeohash text;
update osm_city set inGeohash=st_geohash(geom); -- '' quando vazio
alter table osm_city add column ghs2_intersects text[];
alter table osm_city add column ghs1_intersects text[];
update osm_city set ghs1_intersects=??
INSERT INTO osm_city
  (osm_id,jurisd_base_id,jurisd_local_id,name,parent_abbrev,abbrev,wikidata_id,lexlabel,isolabel_ext,ddd,info,jtags,isolevel,geom)
  SELECT c.osm_id,j.jurisd_base_id,j.jurisd_local_id,j.name,j.parent_abbrev,
         j.abbrev,j.wikidata_id,j.lexlabel,j.isolabel_ext,j.ddd,j.info,
         NULL, -- jtags,
         1,    -- isolevel
         c.geom
  FROM countries c INNER jOIN optim.jurisdiction j
    ON j.admin_level=2 AND j.osm_id=c.osm_id
;

Os níveis administrativos OSM (admin_level) são definidos em Tag:boundary=administrative#National. Uma altenativa para baixar todos os países em alta resolução OSM seria usando BigQuery.

Pendências imediatas

  • recuperar OSM_id das countries
  • revisar uso do osm_city para que tenha view restrita às cidades e indexação adequada.
  • montar os polígonos de geohash-decisão por grade topológica ou grade direta: ideal que se confira em um só vetor e não em vários complementares.

O algoritmo de verificação da jurisdição passa primeiro pela regex de geohashes com 3 ou mais dígitos. A maioria deles conterá decisão entre 2 ou mais polígonos.

SELECT COUNT(*) FROM (SELECT DISTINCT unnest(ghs2_intersects) ghs2 FROM osm_city) t
; -- 501 rows
SELECT ghs2,  array_agg(isolabel_ext) ghs2
FROM (
  SELECT osm_id, isolabel_ext, unnest(ghs2_intersects) ghs2 FROM osm_city
) t group  by 1 having count(*)=1
; -- 238 rows

Dos 501 geohashes de 2 dígitos, 238 retornam de imediato o seu osm_id. Nos 263 restantes pode-se ainda excluir alguns dos 3870 que são interiores a algo com 3 dígitos ou mais.

Ver decisões de indexação conforme http://blog.cleverelephant.ca/2021/05/indexes-and-queries.html

Pendências futuras

Geohash binário terá que ser implementado em C++ mediante pequena alteração nas funções PostGIS preexistentes... Isso tem custo. As opções de visualização e recorte do Geohash através de base16h e base32nvu também requerem implementação C++.

Countries: é preciso resgatar o osm_id de cada geometria, mesmo quando usando naturalearthdata.com. Para a indexação PostGIS ideal usar raster com pixels Geohash.

Clone this wiki locally