Create Table for Doctors

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

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

  • Right click on the Job entry you have created to Update NHS Choices 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 Doctors

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 nhs_choices.doctors as we want to create a doctors table in the 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.

Show expert mode

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


sql "select t.* 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.service = 'gppractices'"

You may need to change the name of the table nhs_choices.all to whatever you called it when you created the Task to Update NHS Choices.  

We have also included the following:

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.service = 'gppractices'

Only selects data where the 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 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 Doctors 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 NHS data you wish to display in iShare.