Home / , / SQL Database Query Data Source

SQL Database Query Data Source

In Geovonic Connect, data sources provide connectivity to third-party business systems. The SQL Database Query data source allows you to fetch real-time data from your relational databases to display in your ArcGIS web mapping applications using SQL queries.

Using SQL you can configure queries that will show live information from your database based on features selected on your ArcGIS web map.

The SQL Database Query data source supports MS SQL Server, MySQL, Postgres, Maria DB and Oracle databases.

Below is an example query that will retrieve information about noise meter readings from around the airport, based on a map selection.

SELECT updated_at, reading_db, meter_status
FROM noise_meter_readings
WHERE meter_id = '{{NMID}}'
ORDER BY updated_at DESC
LIMIT 1;

In this example we have a map layer with an attribute called NMID which is used to join the map features to the noise_meter_readings table in our database.

Data Source Configuration

The SQL Database Query data source configuration requires a few fields: –

  • Database type – select from the list of supported databases.
  • Host URL – the URL pointing to the database server.
  • Port – the port number that the database is running on (e.g. 1433 for MS SQL Server).
  • Username – the username that Geovonic Connect will use to login to your database.
  • Password – the password for the account.

It is important when configuring any integration service to follow the Principle of Least Privilege. Any credentials used for the data source should only be able to access the minimum amount of information to meet the needs of your Geovonic Connect application. For example, do not provide the admin credentials for your database if Geovonic Connect only needs to access a single view. Best practice is to create a new user with the least required privileges.

Layer Link Configuration

The SQL Database Query layer link configuration requires just one field: –

  • Query – a query statement using the SQL syntax. Use the [+] button to include feature attributes as part of your query statement.

Where queries are potentially returning multiple records for a single map feature, be sure to include a LIMIT statement in your query to set the maximum number of rows that should be returned. This prevents very large datasets being inadvertently returned to the user’s browser. Consider adding indexes to your database as necessary to deliver the maximum performance when fetching data for your ArcGIS web mapping applications.