Welsh Assembly Members (AMs)
...
Import Modern.gov RSS feed
Set up an RSS type DataShare 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.
| Name | Format Modern.gov AMs (moderngov_regional_spatial) |
|---|---|
| schemaname | public |
| tablename | moderngov_regional_spatial |
| selectstatement | See below |
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 |
...
For Welsh Authorities using Modern.gov, a Welsh language version of 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 which provide containing Welsh language content and links.
Create DataShare 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.
...
- 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