3. Anatomy of data extraction

This section describes how a typical data extraction might be carried out manually, and how the Data Extraction Tool automates this process. Please note that the examples used in this illustration are purely fictional and do not represent a real-world scenario.

3.1. Data extraction process

The process of a typical data extraction can be broken down into a number of distinct steps that are described here. In the next section the way that the Data Extraction Tool carries out these steps is explained.

Defining a partner boundary

Before any extraction can be carried out, a polygon describing the area for which data will be extracted for a partner has to be entered into a GIS system. This would be held in a single GIS layer together with some associated attributes such as the name of the organisation represented by the boundary. Once this area has been entered into the data layer it can be used time and again.

Selecting the relevant data layers

Using the boundary defined in the previous step, each of the data layers relating to the presence of protected sites, habitats, species, etc. is selected one by one. Where data is held within SQL Server this process is carried out within the SQL Server database.

Exporting the results

The selected features are extracted in the format required by the user, containing only the relevant columns required from each data layer. Symbology may need to be defined at this step.

Repeating the process

Where there is more than one partner for which data needs to be extracted, the process will be repeated for each partner boundary.

3.2. The Data Extractor tool

3.2.1. Tool components

There are four component parts to the Data Extractor tool that work together to automate the process described above:

1. A GIS layer that describes the boundaries of all relevant partners and stakeholders. This is held both within the GIS system and within SQL Server.
2. Spatial data held in an SQL database and / or in spatial data layers within the GIS system. Where data is held within SQL Server a stored procedure for its extraction is also required.
3. An XML configuration file that specifies how the extractions are set up and what data should be exported for each data layer.
4. The Data Extractor tool itself.

The Data Extractor tool is used within a GIS environment and requires all the required data layers to be preloaded in the GIS (see Fig. 3.1). Where data is to be extracted from SQL Server the partner boundary layer must also be preloaded into the SQL Server database.

Fig. 3.1 A MapInfo workspace configured for using the Data Extractor tool

3.2.2. Tool workflow

The Data Extractor tool requires minimum user input in order to carry out its processes once it is configured. The simple workflow is as follows (see Fig. 3.2):

1. The user selects which partner(s) the extraction should be carried out.
2. The user specifies which data layers to extract from. Only layers that are loaded in the GIS or tables found in the SQL Server database are made available at this point.
3. The user selects whether the extracted files should be added to a zip file, whether confidential data from any SQL Server tables should be included, and whether the log file should be cleared before the process starts.
4. Finally, the user selects whether the selection of SQL Server data should be based on spatial location only, survey tags (names) only, or both. This allows for the inclusion of data relevant to a partner that is outside of that partner’s boundary.
5. Once the user clicks OK the process starts.

Fig. 3.2 The Data Extractor tool workflow

In essence, the process that the tool follows is identical to the manual process a user would perform:

1. The boundary of each selected partner is processed in sequence.
2. The specified SQL and GIS data layers are selected using the boundary (and/or the survey tags) for this partner.
3. The resulting selections are exported to the output folder as specified in the configuration file, using the columns and symbology specified in this configuration file (for SQL data) or defined within the GIS layer.
4. During the process the tool reports its progress to a log file and, when the process finishes, this log file is displayed allowing the user to assess the success of the data extraction.

3.3. Tool outputs

Below is a selection of outputs generated from an example data extraction using the data selections shown in figure Fig. 3.1. The extraction was carried out for all partners shown in the menu.

3.3.1. Output folder

The outputs are stored in a user defined folder (Fig. 3.3). These outputs may include a combination of GIS layers and text files in different formats, and the log file.

Fig. 3.3 Example of the Data Extractor tool output folder

3.3.2. Output files

Text file outputs can be produced in CSV format (Fig. 3.4). GIS layers can be output in MapInfo (.tab) format and / or converted into ArcGIS (.shp) format.

Fig. 3.4 Example of a text file output from the Data Extractor tool

3.3.3. Output options

Options in the tool include compressing all outputs into a single zip file for each partner (Fig. 3.5; MapInfo only), including confidential records (defined in the configuration document) in any SQL table extracts, and clearing the log file before use.

Fig. 3.5 Example of a compressed output file containing a single GIS layer (MapInfo)

Finally, the log file details each step that was taken during the process, and gives some feedback about the outcomes of the steps. This includes reporting on the input for the search, the number of features that were selected in each data layer, and which data layers did not return any features (Fig. 3.6).

Fig. 3.6 Example of a Data Extractor tool log file

The following chapters, setting up the tool and running the tool, will guide you through setting up and operating the tool in such a way that these tool outputs meet the exact requirements of data extraction within your organisation.