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 MPs
Connection
Select the Data Share database from the list.
Stored Procedure
First select the AstunWorkflow radio button as this will limit the list of available stored procedures and then select the at_syswkf_create_tableexecutecmd entry from the list.
Parameters
Click on Parameter 1. tablename and enter moderngov_mps_spatial in the Edit parameter 'tablename' box e.g.
Now click on Parameter 2. selectstatement and enter the following select statement in the Edit parameter 'selectstatement' box.Parameter 1. cmdtexecute and enter the following:
| Note |
|---|
You may need to replace the "boundaryline.westminster_const" in the example below, with the name of your table if this is different. |
| Code Block | ||||
|---|---|---|---|---|
| ||||
drop table if exists moderngov_mps_spatial;
Create table moderngov_mps_spatial as
select
ogc_fid, wkb_geometry, name,
CASE WHEN constit is null THEN
replace(name, ' ED','')
ELSE
constit
END,
CASE WHEN full_html is null THEN
'<div class="modern-dot-gov-councillor">
<h5 class="name">
No elected Member of Parliament at present.
</h5>'
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.westminster_const as d LEFT JOIN
(select constit, '<div class="modern-dot-gov-councillor">
<h5 class="name">
<a href="' || "userpage" || '" alt="View Member of Parliament' || "name" || ' web page">' || "name" || '</a>
</h5>
<ul>
<li class="modern-dot-gov-councillor-portrait">
<a href="' || "userpage" || '" alt="View Member of Parliament ' || "name" || ' web page"><img src="' || "photo" || '"></a>
</li>
<li>' || "party" || '</li>
</ul>
</div>' as full_html
from (select title as name,
replace(replace(substr(description, strpos(description,'for ')+4,100),'&','and'), '-',' ') as constit,
substr(description, 0,strpos(description,', ')) as party,
substr(link, strpos(link,'id=')+3,3) as userid,
substr(link, 0, strpos(link,'mgMember')) || 'mgUserInfo.aspx?UID=' || substr(link, strpos(link,'id=')+3,3) as userpage,
substr(link, 0, strpos(link,'mgMember')) as cmissite,
substr(link, 0, strpos(link,'mgMember')) || 'UserData/' || SUBSTRING(lpad(substr(link, strpos(link,'id=')+3,3), 4, '0'),4,1) || '/'
|| SUBSTRING(lpad(substr(link, strpos(link,'id=')+3,3), 4, '0'),3,1) || '/'
|| SUBSTRING(lpad(substr(link, strpos(link,'id=')+3,3), 4, '0'),2,1) || '/Info'
|| lpad(substr(link, strpos(link,'id=')+3,3), 8, '0') || '/bigpic.jpg' as photo
from moderngov_mps) as mg) as mg
ON lower(replace(substr(d.name,0,strpos(replace(d.name,'Boro ','Co '),' Co Const')), '.','')) = lower(constit)
order by name)
as results |
E.g.
...
; |