The data from the Modern.gov RSS feed needs to be reformatted before it can be used by iShare. This is done via a Stored Procedure Task. In the tree, right click on the Update Modern.gov Data Job you have just created and select New Task.
...
Task Details
Enter the following:
Task Name
Enter a Name for the task e.g. Format Modern.gov Councillors
Connection
Select the Data Share database from the list.
Stored Procedure
First select the Workflow radio button as this will limit the list of available stored procedures and then select the at_wkf_executecmd entry from the list (from the listv6.0.0 this is called wkf_executecmd under the -Workflow- Function filter).
Parameters
Click on Parameter 1. cmdtexecutecmdtoexecute and enter the following:
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
drop table if exists moderngov_spatial;
Create table moderngov_spatial as
select
ogc_fid, wkb_geometry, name,
CASE WHEN ward is null THEN
replace(name, ' ED','')
ELSE
ward
END,
CASE WHEN full_html is null THEN
'<div class="modern-dot-gov-councillor">
<h5 class="name">
No elected councillor at present.
</h5>
</div>'
ELSE
full_html
END
from (select d.ogc_fid,d.wkb_geometry,d.name, replace(lower(substr(d.name,0,strpos(d.name,' ED'))), '.',''),mg.* from
boundaryline.county_electoral_division as d LEFT JOIN
(select ward, '<div class="modern-dot-gov-councillor">
<h5 class="name">
<a href="' || "userpage" || '" alt="View Councillor ' || "name" || ' web page">' || "name" || '</a>
</h5>
<ul>
<li class="modern-dot-gov-councillor-portrait">
<a href="' || "userpage" || '" alt="View Councillor ' || "name" || ' web page"><img src="' || "photo" || '"></a>
</li>
<li>' || "party" || '</li>
<li>
<a href="' || "cmissite" || '" alt="More information on Councillors and Committees">More information on Councillors and Committees</a>
</li>
</ul>
</div>' as full_html
from (select title as name,
replace(replace(substr(description, strpos(description,'Ward: ')+6,100),'&','and'), '-',' ') as ward,
substr(description, 0,strpos(description,', ')) as party,
substr(link, strpos(link,'idUID=')+3,34) as userid,
substr
link as userpage,
split_part(link,'mgUserInfo',1) as 0cmissite,
strpos(split_part(link,'mgMembermgUserInfo'),1) || 'mgUserInfo.aspx?UID=UserData/' || substring(substr(link, strpos(lower(link),'idUID=')+3,3) as userpage,
4),char_length(substr(link, 0, strpos(link,'mgMemberUID=')) as cmissite,
substr(link, 0, strpos(link,'mgMember')+4)),1) || 'UserData/' || SUBSTRING(lpadsubstring(substr(link, strpos(lower(link),'idUID=')+3,3), 4, '0'),44),char_length(substr(link, strpos(link,'UID=')+4))-1,1) || '/' || SUBSTRING(lpadsubstring(substr(link, strpos(lower(link),'idUID=')+3,3), 4, '0'),3,1) || '/'
|| SUBSTRING(lpad),char_length(substr(link, strpos(lower(link),'idUID=')+3,3), 4, '0'),4))-2,1) || '/Info'
|| lpad(substr(link, strpos(link,'idUID=')+3,34), 8, '0') || '/bigpic.jpg') as photo
from moderngov_councillors) as mg) as mg
ON lower(replace(substr(d.name,0,strpos(d.name,' ED')), '.','')) = lower(ward)
order by name)
as results; |
...