User:Krauss/QualidadeBR-multipolygons
Jump to navigation
Jump to search
Tabela inicial com polígonos com mais de 100 itens:
| osm_id | ilhas |
|---|---|
| 8025281 | 306 PPkrauss e Nelson confirmam que é ruim |
| 2217368 | 209 Nelson sugere melhorar |
| 4816374 | 208 ... |
| 2217370 | 134 |
| 7714329 | 130 |
| 7998638 | 126 |
| 5298213 | 122 |
| 2634648 | 118 |
| 8279693 | 114 |
| 8051745 | 113 |
| 3849796 | 108 |
| 2697338 | 103 |
SELECT osm_id, count(*) n FROM planet_osm_polygon GROUP BY 1 HAVING COUNT(*)>1
Para fins de prospecção de bugs, o primeiro passo é:
select count(*) from planet_osm_polygon
where not(ST_IsValid(way)) and osm_id IN (
select osm_id from planet_osm_polygon group by 1 having count(*)>1
);
Per area
CREATE or replace FUNCTION array_distinct_sort (
ANYARRAY,
p_no_null boolean DEFAULT true
) RETURNS ANYARRAY AS $f$
SELECT CASE WHEN array_length(x,1) IS NULL THEN NULL ELSE x END -- same as x='{}'::anyarray
FROM (
SELECT ARRAY(
SELECT DISTINCT x
FROM unnest($1) t(x)
WHERE CASE
WHEN p_no_null THEN x IS NOT NULL
ELSE true
END
ORDER BY 1
)
) t(x)
$f$ language SQL strict IMMUTABLE;
-- usar bags contando frequencia das áreas.
SELECT -osm_id osm_id, count(*) n
,array_to_string(array_distinct_sort(array_agg(round(st_area(way,true)/1000000.0))),', ') area_list
-- ,tags->'wikidata'
FROM planet_osm_polygon
GROUP BY 1 having count(*)>49
order by 2 desc,1;
PS: uso de array_distinct_sort() limita performance. Usar bags.
osm_id | n | area_list ---------+-----+-------------------------------------------------------------------------- 7357153 | 500 | 0, 18 8085729 | 325 | 0, 1, 2 8025281 | 306 | 0, 1, 2 2217368 | 209 | 0, 1, 3, 6, 179, 1539 4816374 | 208 | 0, 1, 3, 6, 179, 7724 2217370 | 134 | 0, 1, 6, 179, 622 7714329 | 130 | 0, 1 7998638 | 126 | 0, 1, 2 5298213 | 122 | 0, 2, 3 2634648 | 118 | 0, 1, 2, 3, 6, 9, 30, 56, 147, 465 8279693 | 114 | 0 8051745 | 113 | 0, 2, 3 3849796 | 108 | 0 2697338 | 103 | 0, 1, 5, 36, 1177 5298434 | 97 | 0, 1 8327507 | 90 | 0 8312111 | 85 | 0 8307324 | 84 | 0 8330250 | 82 | 0, 2 2220779 | 81 | 0, 2, 3, 10, 10595 4821368 | 80 | 0, 1, 2, 3, 5, 6, 15, 22, 23, 26, 27, 34, 54, 109615 4830223 | 80 | 0, 1, 2, 3, 5, 6, 15, 22, 23, 26, 27, 34, 54, 5474 8342850 | 78 | 0, 1 4830230 | 77 | 0, 1, 2, 3, 4, 7, 8, 9, 10, 13, 14, 16, 17, 29, 77075 1277591 | 75 | 0, 1, 3, 917 8307221 | 73 | 0, 1, 69 8279694 | 71 | 0, 1, 2, 3 5130759 | 70 | 0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 17, 23, 24, 27, 34, 41, 42, 44, 78, 86120 8325368 | 70 | 0, 1, 2 5130764 | 69 | 0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 17, 23, 24, 27, 34, 41, 42, 44, 78, 21088 7559709 | 67 | 0 7580120 | 67 | 0 2536567 | 65 | 0, 1, 2, 11, 14 4830212 | 65 | 0, 1, 2, 3, 4, 7, 8, 9, 13, 16, 17, 29, 8506 4807856 | 64 | 0, 1, 2, 7, 8, 337, 9313 2815659 | 62 | 0, 1, 2, 4, 12 5130387 | 61 | 0, 1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 18, 19, 20, 21, 25, 32, 55, 98, 52195 8006381 | 61 | 0, 1 8353127 | 60 | 0, 1, 5, 13 3970238 | 59 | 0, 1, 2 5130769 | 58 | 0, 1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 18, 19, 20, 21, 25, 32, 55, 98, 9060 3769219 | 55 | 0, 1, 6, 8, 49, 235, 478 8332889 | 54 | 0 4813236 | 52 | 0, 1, 2, 7, 8, 337, 1578 6353196 | 52 | 0 2719082 | 51 | 0 7609396 | 51 | 0, 1, 14 8382450 | 51 | 0, 1 8332833 | 50 | 0, 1 8342849 | 50 | 0, 1 8382846 | 50 | 0