smnorris / fwapg

PostgreSQL tools for working with British Columbia's Freshwater Atlas
https://smnorris.github.io/fwapg
MIT License
9 stars 5 forks source link

carto views for labelling multipart features #111

Open smnorris opened 2 years ago

smnorris commented 2 years ago

Unless I'm missing something in the QGIS labelling interface, it doesn't have the greatest polygon label de-duplicator, labelling island and lake groups can be awkward. Views holding the island/lake group name and the centroid of the geoms for features could help this.

smnorris commented 2 years ago

Something vaguely like this, but as a view and ensuring that it is easy to distinguish and separate the primary/secondary names.

select 
  row_number() over() as id,
  n_islands,
  gnis_name_1,
  geom::geometry(multipolygon, 3005)
from 
( 
  select 
     count(*) as n_islands, 
    gnis_name_1, 
    gnis_id_1, 
    st_multi(st_union(geom)) as geom
from whse_basemapping.fwa_islands_poly
group by gnis_name_1, gnis_id_1) as f;