Create Table for Nursery Schools

Owned by Kim Stimpson
Last updated: 16 Feb, 2017
2 min read

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

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

Task Name

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

Source Data

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

Table

Leave this entry blank as we are going to specify

Output

We want to output to the PostgreSQL database so select then name of your Data Share database.

Table

Enter a name for the table that you wish to create in the PostgreSQL database; we have chosen education.nurseries as we want to create a nurseries table in the education 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.

Show expert mode

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 t inner join boundaryline.county_surrey as extents on ST_DWithin(extents.wkb_geometry, t.wkb_geometry, 5000) where t.phase_of_education_name = 'Nursery' and t.establishment_status = 'Open'"

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.


(statutory_low_age || '-' || statutory_high_age || ' yrs') as admissions

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_name = 'Nursery' and t.establishment_status = 'Open'

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 =.


  • 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 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 Schools 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 you wish to display in iShare.