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.

Select the Stored Procedure type and click OK.

Task Details

Enter the following:

Task Name

Enter a Name for the task e.g. Format Moderngov Modern.gov Councillors

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

Parameters

Click on Parameter 1. tablename and enter moderngov_spatial in the Edit parameter 'tablename' box e.g.

Image Removed

Now click on Parameter 2. selectstatement and enter the following select statement in the Edit parameter 'selectstatement' box cmdtoexecute and enter the following:

Note

title	Note

You may need to replace the "boundaryline.county_electoral_division". 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_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,
link as userpage,
substrsplit_part(link, 0, strpos'mgUserInfo',1) as cmissite,
(split_part(link,'mgMembermgUserInfo'),1) || 'mgUserInfo.aspx?UID=UserData/' || substring(substr(link, strpos(link,'idUID=')+3,3) as userpage,
4),char_length(substr(link, 0, strpos(link,'mgMemberUID=')+4)) as cmissite,
substr(link, 0, strpos(link,'mgMember'),1) || 'UserData/' || SUBSTRINGsubstring(lpad(substr(link, strpos(link,'idUID=')+3,3), 4, '0'),44),char_length(substr(link, strpos(link,'UID=')+4))-1,1) || '/' || SUBSTRING(lpadsubstring(substr(link, strpos(link,'idUID=')+3,3), 4, '0'),3,1) || '/'
|| SUBSTRING(lpad(),char_length(substr(link, strpos(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

E.g.

...

Now click the Run button Image Added to run the Task to create the moderngov_spatial table.

Info
This example provides county councillor information using OS BoundaryLine's County Electoral Division data. To run the same process for borough, district or unitary councillors use the 'boundaryline.district_borough_unitary_ward' dataset and change references from 'ED' to 'Ward'.

Versions Compared

Old Version 3

New Version Current

Key

Task Details