User:Krauss/QualidadeBR-multipolygons

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