Child Datasets

A child dataset contains complementary attributes that relate to a selected feature from a map layer.

When a user selects a feature from a map layer (the ‘parent’ dataset), Pozi fetches and displays records from any suitably configured table (the ‘child’ dataset) based on spatial or non-spatial criteria.

Use cases:

• click on Property feature to reveal:
  • owner details
  • building and planning permits
  • animal registrations
  • associated land parcels
  • intersecting planning zones and overlays
• click on Asset feature to reveal:
  • inspections
  • defects
  • maintenance
  • photos
• click on Council Ward feature to reveal:
  • representatives and contact details
• more…

The parent record contains various attributes, such as the ones that are visible in the Details pane, other hidden attributes, and also the record’s geometry. Any of these can be used as a key for querying other datasets. And any child record can become a parent of another dataset, and so on.

In the above example:

• Property Information is queried using the parent record’s property number
• Property Parcels is queried using the parent record’s property pfi
• Property Planning Overlays and Zones both use the parent record’s geometry (ie, property boundary) in a spatial intersection query

Administrators can use QGIS to configure child datasets for viewing by internal users. The parent dataset can be an internal or external dataset.

Joined Datasets vs Child Datasets

Before configuring a child dataset, you may consider using a joined dataset.

Joined Datasets

Joined datasets take advantage of QGIS’s built-in table join function.

Configuring joined tables in QGIS may be suitable when:

• there is a one-to-one relationship between records in parent and child tables
• the table has fewer than 10,000 records (larger tables are still supported, however you may observe that they are slow to load)
• users expect to search on joined attributes or view them in a table

Think of joined tables as adding fields from one table to another. Attributes from joined tables appear in the Pozi info panel in the same information tab as the parent dataset.

If you decide a joined dataset is right for you, see here for information on how to configure it in QGIS. The remainder of the instructions on this page are not relevant to joined datasets.

Child Datasets

Alternatively, adding child datasets (as covered in this page) is recommended when:

• there is a one-to-many relationship between records in the tables, or
• for tables with more than 10,000 records, or
• the child dataset is a spatial layer and uses a spatial relationship to link to its parent

Attributes from child tables appear in the Pozi info panel in separate tabs below the parent’s information tab.

If you decide a child dataset is right for you, continue to the instructions below.

Add

Add the source table for the child dataset to a QGIS project. The project can be an existing project that contains map layers or it can be a dedicated project that contains no layers and only child datasets.

1. open project file in QGIS
2. Layer > Add Layer > pick from file or database options
3. pick source table
4. Add
5. Close
6. Project > Properties > QGIS Server
7. WFS capabilities > Published: tick on for the new dataset
8. OK
9. Project > Save (Ctrl + S)

Configure

The parent/child settings are maintained in the child layer’s QGIS Server keyword list. Pozi obtains these settings, along with any other keywords, when it loads and imports and project’s catalogue.

Only the child dataset needs to be configured with the keyword settings. The parent dataset needs no configuration for it to used by a child dataset.

Below are details of how to derive and combine the components of the keyword setting.

Parent

The parent dataset contains the features which, when selected, will trigger a data request to the child dataset.

For example, if the parent is the “Property” layer, then every time a user selects a feature in the Property layer, Pozi will trigger a request to the child dataset.

Example:

parent=Property

The parent layer can exist in the same QGIS project, or a different project, or even a layer from an external source.

Parameter

A parameter expression can be used to override certain parameters for the WFS GetFeature request. A common use of this feature is to provide instructions for filtering the child dataset to return only records that relate to an individual parent feature.

The following are some examples of how parameter can be used. Multiple parameters can be assigned by using a semi-colon as a delimiter.

Spatial Filter

If you are configuring a child dataset as a spatial intersection of its parent, you don’t need to specify any parameter filter. In the absence of any filter, Pozi assumes that the parent/child relationship is a spatial intersection, and it will generate the necessary query to return all child records that intersect with the selection spatial record.

However if you want to explicitly specify a filter, you can do so by using the following:

parent=Property, parameter=filter=<Filter><Intersects><PropertyName>geometry</PropertyName>[$gml]</Intersects></Filter>

Alternatively you can also specify a custom spatial filter. In this example, we are applying a negative buffer to the parent geometry to exclude records that have only a slight (<1m) overlap:

parent=Property, parameter=EXP_FILTER=intersects(@geometry [$comma] buffer(geom_from_wkt('[$wkt]') [$comma] -0.00001 ))

Note that the [$comma] is required in the place of a normal comma because QGIS considers a normal comma to be a keyword delimiter.

Non-Spatial Filter

If you are configuring a child dataset to use an id-based join, use the layer’s Query Builder to construct and test a filter expression.

1. Layer Properties > Source > Query Builder
2. double-click the name of the field to be used as the link - this will be added to the expression
3. click IN, and add an open bracket [(]
4. click the Sample button to obtain a list of existing values from the target field
5. double click one of the sample values to add it to the expression
6. add a close bracket [)]
7. click Test

If the test query returns one or more rows, your expression is valid, and it can be used in the child parameter. Clear the Query Result dialog box, select your expression text, and copy it to your clipboard.

Exit the Query Builder by clicking Cancel. DO NOT click OK. (If you accidentally click OK, re-open the Query Builder and clear the expression before saving.)

Construct the parameter text as follows:

1. parameter= + EXP_FILTER= + the expression from your test pasted from the clipboard
2. remove any double quotes from around the target field
3. replace the sample value with the name of the link field from the parent dataset where the target value is obtained, enclosed in a pair of square brackets (eg, replace 1000900200 with [Property Number])

Example:

parent=Property, parameter=EXP_FILTER=Assess_NumberX IN ('[Property Number]')

Pozi will substitute any field names within square brackets with values from those fields from the parent.

Ordering

To ensure child data is returned in a logical order, the SORTBY parameter can be used. By default, the field specified will be sorted in an ascending order. To reverse the order append DESC after the field name.

In the following example, a playground inspection dataset is linked to the parent Playgrounds layer using the asset_id field in both datasets. The data is sorted so that the most recent inspection is returned first by using the SORTBY=inspection_date DESC parameter.

parent=Playgrounds, parameter=EXP_FILTER=asset_id in ('[asset_id]');SORTBY=inspection_date DESC

This example demonstrates the use of a semi-colon to specify multiple parameters.

Return Geometry

There are instances when returning the actual geometry for a child dataset is not desired. An example of this would be when the child layer includes very complex geometries, which result in excessively large datasets being returned (eg. flooding or bushfire prone layers) which can significantly impact the applications performance.

The GEOMETRYNAME parameter can be used to alter the geometry that is returned. The values that can be used with this parameter are: extent, centroid and none.

Example: parameter=GEOMETRYNAME=none

Optional Settings

These settings provide an override for some of the default layer behaviours in Pozi.

Combine and Configure

In the child dataset’s Layer Properties, go to QGIS Server. Fill in the ‘Keyword list’ with the parent, parameter and optional settings derived above, each separated by commas.

• Spatial example: parent=Property
• Non-spatial example: parent=Property, parameter=EXP_FILTER=Assess_NumberX in ('[Property Number]')

Click OK, then save the project.

Open your Pozi site in your browser, refresh the page, click on the map, select a parent record, and confirm that you can see the related child record(s) in the info panel.

Pozi automatically prevents the child table appearing in the layer panel due to the presence of the parent keyword. Do NOT add the table to the list of excluded layers in the QGIS Server settings, except if you want to disable the child dataset lookup.