Page Comparison

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 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 v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter).

Parameters

Click on Parameter 1. cmdtexecutecmdtoexecute and enter the following:

Note
You may need to replace the "boundaryline._westminster_constconstituencies" in the example below, with the name of your table if this is different.

...

Code Block

language	sql
title	Example

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_region 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"Moderngov_mps") as mg) as mg
ONWHERE lower(replace(substr(d.name,0,strpos(replace(d.name,'Boro ','Co '),' Co Const')), '.','')) = lower(constit)
order by name)
as results;

Versions Compared

Old Version 6

New Version Current

Key

Task Details