Page Comparison

Once you have created and run the Task to download the Edubase NHS Choices data into the PostgreSQL database you can create individual Workflow Tasks to create a table for each type of SchoolNHS data.

• Right click on the Job entry you have created to Refresh the EduBase DataUpdate NHS Choices in the tree and select New Task.
• Select Spatial Data Transformation as the task type and click OK.

Image RemovedImage Added

Task Name

Enter a Name for the task. In the above example we have chosen Create Nursery SchoolsDoctors

Source Data

Here you need to select the name of your Data Share database as we are selecting data from the PostgreSQL database.

...

Enter a name for the table that you wish to create in the PostgreSQL database; we have chosen educationnhs_choices.nurseries doctors as we want to create a nurseries doctors table in the education nhs_choices schema.

Options

Tick Force Geometry and pick POINT from the selection. If you don't do this ogr incorrectly sets the geometry type to GEOMETRY.

...

This this box and then paste the following into the Additional parameters.

-sql "select t.*, t.school_capacity::integer as capacity_numeric, (statutory_low_age || '-' || statutory_high_age || ' yrs') as admissions from education.edubase from nhs_choices.all as t inner join boundaryline.county_surrey as extents on ST_DWithin(extents.wkb_geometry, t.wkb_geometry, 5000) where t.phase_of_education_nameservice = 'Nursery' and t.establishment_status = 'Open'gppractices'"

You may need to change the name of the table education.edubase to whatever you called it when you created the Task to Update Edubase.  

We have also included the following:

t.school_capacity::integer as capacity_numeric

Creates a new field capacity_numeric as an integer based on the text field school_capacity. This means that we can filter using mathematical operators on this in iShare GIS.

...

Creates a new field called admissions that shows the age range e.g. 3-5

t inner join boundaryline.county_surrey as extents on ST_DWithin(extents.wkb_geometry, t.wkb_geometry, 5000)

This will return data up to 5km from the surrey county boundary line.

where t.phase_of_education_nameservice = 'Nursery' and t.establishment_status = 'Opengppractices'

Only selects data where the phase_of_education is Nursery and the establishment_status is Open.

where t.type_of_establishment like '%Special%' and t.establishment_status = 'Open'

You may prefer to use type_of_establishment instead of phase_of_education to filter your records. We have also used like instead of =.

service is gppractices.

• Modify the statement for your own requirements.
• Click Save to update the Studio with your changes. 

Although we have created the Workflow Task to create the nurseries doctors table we need to actually Run the task to import the data.

• Right Click on the Task you have just created and select Run Task Create Nursery SchoolsDoctors or whatever you have called it. This may take a little while but you should see a successful completion message when the table has been created. 

Now you can repeat the above steps to create Tasks for each of the different Types of Schools NHS data you wish to display in iShare.