Welsh Authorities

Owned by Matt Walker
Last updated: 27 Oct, 2022 by Kim Stimpson
3 min read

Welsh Assembly Members (AMs)

Welsh authorities commonly also require the member(s) of the Welsh Assembly to be displayed as well as MPs and County Councillors. The process is similar to setting up MPs.

Regions

OS BoundaryLine does not appear to provide a layer for Welsh Assembly regions but it should be possible to create one by combining existing Westminster Constituency regions. The SQL to create the spatial table below demonstrates selecting and merging Westminster Constituency regions for The Isle of Anglesey.

Import Modern.gov RSS feed

Set up an RSS type Data Share Connection with the URL http://democracy.yourcouncil.gov.uk/mgRss.aspx?f=27 to import the RSS feed from Modern.gov.

Join Member information from RSS feed with geometry

Create a "Stored Procedure" Workflow Task. Select the wkf_create_table function from the Workflow filter and enter the information below.

NameFormat Modern.gov AMs (moderngov_regional_spatial)
schemanamepublic
tablenamemoderngov_regional_spatial
selectstatementSee below
selectstatement
SELECT geom.ogc_fid,
       geom.wkb_geometry,
       mg.*
FROM
  (SELECT ogc_fid,
          wkb_geometry,
          'Ynys Môn' AS "region"
   FROM boundaryline_westminster_constituencies
   WHERE "name" = 'Ynys Mon Co Const'
   UNION SELECT 1::int AS ogc_fid,
                st_union(wkb_geometry),
                'North Wales' AS "region"
   FROM boundaryline_westminster_constituencies
   WHERE "name" IN ('Aberconwy Co Const',
                    'Alyn and Deeside Co Const',
                    'Arfon Co Const',
                    'Clwyd South Co Const',
                    'Clwyd West Co Const',
                    'Delyn Co Const',
                    'Vale of Clwyd Co Const',
                    'Wrexham Co Const')) AS geom,
  (SELECT mg.*,
          '<div class="modern-dot-gov-councillor">
<p class="name">
<a href="' || "userpage" || '" alt="' || "name" || ' web page">' || "name" || '</a>
</p>
<p class="modern-dot-gov-councillor-portrait">
<a href="' || "userpage" || '" alt="' || "name" || ' web page"><img src="' || "photo" || '"></a>
</p>
<p class="party">' || "party" || '</p>
</div>' AS full_html
   FROM
     (SELECT split_part(title, ' - ', 1) AS name,
             split_part(title, ' - ', 2) AS region,
             description AS party,
             (regexp_matches(link, E'\\d+$'))[1] AS userid,
             link AS userpage,
             split_part(link,'mgUserInfo',1) || 'UserData/' || SUBSTRING(lpad(substr(link, strpos(lower(link),'id=')+3,3), 4, '0'),4,1) || '/' || SUBSTRING(lpad(substr(link, strpos(lower(link),'uid=')+4,3), 4, '0'),3,1) || '/' || SUBSTRING(lpad(substr(link, strpos(lower(link),'uid=')+4,3), 4, '0'),2,1) || '/Info' || lpad(substr(link, strpos(link,'UID=')+4,3), 8, '0') || '/bigpic.jpg' AS photo
      FROM "Moderngov_regional") AS mg ) AS mg
WHERE lower(geom."region") = lower(mg.region)
ORDER BY mg.name

Welsh Language

For Welsh Authorities using Modern.gov, a Welsh language version of their Modern.gov site is normally available with a different domain name to the English language site. The Welsh language site provides the equivalent RSS feeds containing Welsh language content and links.

Create Data Share Connections for each Welsh language equivalent together with Workflow Tasks to create the spatial tables and MapSource / MapServer layers in the Welsh language My House MapSource.

Watch out for:

  • The SQL to create the spatial tables may need updating to take into account the following:
    • Keywords such as Ward, which are used to split the parts of the description value, being spelt in Welsh
    • BoundaryLine substitutes accented characters such as ô with their English equivalents (o for ô) so you might not get a match in the join condition without a bit of replacing