Database Feature Queries

Database queries allow you to query a table, view or stored procedure in the database selected.  To get started click New Database Query in the top left.

In this section:

Open

Create a Database Query

Query a Table\View

1. To define the table to query against, fill in the details below. 

• Datasource: The database that contains the table you want to query.

• Query Types: Select whether you would like to perform the query on a table or use a stored procedure.

• Table\View\Stored Procedure:The table, view or stored procedure to use.

• Optional

  • Include a Group by clause e.g. if the database table already has an aggregation field, you may wish to use this count in your feature query clause. 

  • Include an Order by clause e.g. this will define the queries are ordered in the popup e.g. for resource consents on a property, you may wish to see the most recent consents first. By using the date field in your order by clause, you can achieve this.

Example of a MySQL Table feature query.

Open

2. Select the fields you wish to include in the Feature Query. Note that the Alias can be altered here for Database Queries. 

• Query name: The title that will appear in the popup.

• Select: This box will be populated as you create your query from the table fields.

• Fields: The fields you wish to display in the popup.

3. Reorder how the fields are presented in the popup by clicking the dotted icon and dragging the field to a new position.

4. Hyperlinks can be added to a field by checking the Create hyperlink checkbox

• If the field contains a hyperlink, eg the value of the field is https://www.myawesomesite.com then simply enter the fieldname textbox between two $. 

  • E.g if you have a field called link in the database, then you would enter $link$ in the hyperlink textbox.

  • The alias then becomes the name of the link, so if the alias is Click Me, then this would appear as the text which when clicked would send me to https://www.awesomesite.com. Eg Click Me

• If you want to use the value from the field as part of a url, then enter the url in to the textbox, but replace the part where the value should be with the fieldname between two $.

  • E.g You want to use a property number to send users to property files on a url https://www.marlborough.govt.nz/services/property-files-online?searchType=Property+Number&propertyNumber=

  • Where we need to append the property number to the link from a field named propertyno.

  • Enter https://www.marlborough.govt.nz/services/property-files-online?searchType=Property+Number&propertyNumber=$propertyno$ and change the alias to Property Files.

  • This would have the following result Property Files

5. Review your clause and save the new Feature query. This will then be added to the main table. If you wish to change your configuration set up at any stage in the wizard, simply click on the dot symbol above each step to go back (as per the below image).

Query a Stored Procedure

1. To use a stored procedure in a feature queries, select it as the query type and select your stored procedure from the drop down.

2. In the next step, set the name that will appear in the popup for your feature query. By default, this is populated with the Stored Procedure title.This can be changed by typing in a new name. 

3. Continue to create your new feature query. This will then be added to the main table.

Assign to a map

Once created, feature queries must be assigned to a layer. This will allow the information to be displayed in the layers popup. To see how to add a feature feature to a layer in your map, head to Configure Widgets.