Importing data into PostgreSQL

Owned by Kim Stimpson
Last updated: 08 Nov, 2023 by Peter Goulborn
3 min read

Before you can add your Layer in Studio your need to import your data into the PostgreSQL database using Studio. This example shows you how to import a Shape file into PostgreSQL, so that in later stages it can be configured as an iShare layer.

The first thing you would do would be to create a schema in the iShareData PostgreSQL database to hold your data. In this example we will create a schema called workshop.

  • Open pgAdmin and double click on the iShareData node.

  • Right click on the Schemas node and select New Schema.

  • Enter workshop as the Name and click OK.

Now we need to use Studio to upload the data to the PostgreSQL database. This is done via a Workflow Spatial Data Transformation Task.

  • Open either iShare Studio or iShare GIS Studio.

  • Expand the Workflow node by clicking on the plus beside the node.

  • Now expand the Jobs node.

  • Right click on Unassigned tasks and select New Task.

  • Select Spatial Data Transformation as the Task Type and click OK. This is because we want to transform the spatial data to load it into Data Share.

     

  • Here we are importing data from the Shapefiles in E:\iShareData\Data\Workshop, selecting the workshop_polygons shape file and putting it in the workshop schema of the iShareData database - (this has been configured as the DataShare Workflow Connection). Use the same Table name as the file.

  • If you believe that your dataset contains data for more than one Geometry Type e.g. Polygon & Multi Polygon then you will need to tick the Force Geometry box and pick the best fit, in this case MULTIPOLYGON. If you don’t select a Geometry Type then OGR checks the first record in the dataset and sets the Geometry Type accordingly. If this happens to be Polygon then all of the Multi Polygon entries will fail to import. 

     

  • If you wish to receive email notification if records fail to load when the Task is run as part of a Scheduled Workflow Job, then you will need to untick the Skip Failures check box which is ticked by default. An email will be sent to the email address configured under Workflow Notification Setup.


    You can then check the the iShare logs where you will see something similar to:

Warning 1: Geometry to be inserted is of type Multi Polygon, whereas the layer geometry type is Polygon. Insertion is likely to fail

  • Save the changes using the main toolbar.

  • Run the task from the button to the right of the task name. The data from the shape file has now been loaded into PostgreSQL.

Now we can check in pgAdmin to see if the data has been loaded.

  • Open pgAdmin and double click on the iShareData node.

  • Expand the Schemas node and then the workshop node.

     

  • You should be able to see the new workshop_polygons table (in the above screenshot we have also imported workshop_points).

  • Right click on the table and select View Data > View All Rows to view the data.