Open topic with navigation

Designing Queries for GIS Map Markers

As a simpler alternative to specifying the SQL query within a Map Set hyperlink, you can use a User Query database item. You can configure the User Query to hold a database query of Geo SCADA Expert database items that are to be displayed on maps. When configured, the context-sensitive menu for the User Query database item will include a Display Query Results option. You can use this menu option to display the query results in a list, which is useful for testing the SQL query. The columns that are shown in the Queries List differ, depending on the SQL columns that you include in the SQL query. Each individual entry within the SQL query list should translate to a Map Marker when shown on a map, although entries with the same location will appear superimposed over each other, obscuring the view of some markers. Data referred to by the SQL query will update whenever the map moves, or at the rate of once per minute (this can be customized in a User Query).

You can use the following SQL column names to define the behavior of the Map Marker:

If you want to specify a custom Map Marker to use in place of a default Map Marker symbol, you can also include the following SQL column names:

If you want to customize the action that is to occur when a user clicks on a Map Marker on a map, include the following SQL column name:

In order to display the assets on a map, the SQL queries that are within hyperlinks need to contain the following columns as a minimum:

The above columns are parsed based on Name rather than Attribute, allowing for color animations to be customized as required. You can configure the ToolTip to contain HTML formatting codes.

 

The following map hyperlink uses the AlarmSetCount, AlarmAcceptedCount and AlarmClearedCount variables on the CGroup table to manipulate the Foreground color, adjusting the color used to display the associated map icon for each Group. The ordering of the result set causes the assets with active alarms to display on top of those without, if there is any overlap.

SELECT FULLNAME, FULLNAME || '<br>Highest_Severity:_' || AlarmSeverityDesc as "TOOLTIP", GISLOCATION->LATITUDE, GISLOCATION->LONGITUDE,

CASE WHEN AlarmSetCount > 0 THEN 255

WHEN AlarmAcceptedCount > 0 THEN 255

WHEN AlarmClearedCount > 0 THEN 150*256 ELSE 0 END AS "Foreground", Background,

CASE WHEN AlarmSetCount > 0 THEN 1 ELSE 0 END AS "Blink", AlarmSetCount, AlarmAcceptedCount, AlarmClearedCount

FROM CGROUP

ORDER BY AlarmSetCount, AlarmAcceptedCount, AlarmClearedCount

The color value 255 is red. This is the equivalent of 225+0*256+0*256*256, but as the green and blue color value elements each equate to 0 there is no need to specify them as 0 is the default for any color value element that is omitted from the SQL query.

The color value 150*256 is the equivalent of 0+150*256+0*256*256. Again as the red and blue color value elements each equate to 0, there is no need to specify them.

 

This example results in the display containing a smooth range of colors, as the Map Marker's foreground color is based on the value of a point that is associated with a group’s location:

SELECT g.Id as "Id", g.FullName as "FullName",

g.FullName || '<br>Value: ' || FormatValue (r.Walk Using '0.00') as "Tooltip",

g.GISLOCATION->LATITUDE as "GISLOCATION->LATITUDE", g.GISLOCATION->LONGITUDE as "GISLOCATION->LONGITUDE",

case

when r.Walk < 50 then (cast(r.Walk/100*2*255 as INT) + 255*256)

when r.Walk > 50 then (255 + cast( (100-r.Walk)/100*2*255 as INT)*256)

end as "Foreground",

255+256*255+256*256*255 as "Background", 0 as "Blink"

FROM CGROUP AS g left join CRandom as r on r.ParentGroupId = g.Id

WHERE g.FULLNAME LIKE 'Sites.%'

 

This example results in custom square Map Markers appearing on a map, overlaid with the marker text 'S'. One marker appears for each Advanced OPC Analog Point that exists in the database. The size of each Map Marker is determined by that particular point's current value (in the range 0-100). The marker position indicates the geographical location of the plant to which each point relates, specified on the relevant configuration Form's Location tab.

SELECT FullName, gislocation->latitude, gislocation->longitude,

Foreground, Blink, Background,

'SQUARE' AS MarkerShape,

'S' AS MarkerText,

0 AS MarkerTextColor,

65280 AS MarkerTextOutline,

15 AS MarkerTextSize,

0 AS MarkerTextOffset,

CurrentValue AS MarkerSize FROM CAdvOPCAnalog

The color value 65280 is the decimal equivalent of 255*256 (or 0+255*256+0*256*256 but as the red and blue color value elements each equate to 0, there is no need to specify them).

Colors

As with colors used elsewhere in expressions in Geo SCADA Expert, you specify Map Marker colors by their RGB (red, green, and blue) values, however the syntax that you use differs slightly to that used for some other color properties and functions.

You use an integer to specify the Map Marker color. You calculate the integer value using the following formula:

<red value>+<green value>+<blue value>

with the required color values being within the following ranges:

0-255 for the red value

0-255*256 for the green value

0-255*65536 (or 0-255*256*256) for the blue value

If you omit any color value element from a Map Marker color, that color value element will default to 0.

You can specify a Map Marker color using the format shown above, or as a decimal value that comprises the sum of the 3 color value elements (within the range 0 (black) to 16777215 (white), with 16777215 being the sum of 255+(255*256)+(255*65536)).

Aliases

Query column names are not case-sensitive, and should not contain the table name or alias; therefore, a column alias should be used if more than one table is referenced in the query.

If you are using table alias names, then you must also use an alias for the column.

 

If you use this table alias name:

FROM CGROUP AS g

Then you must also use an alias for the column:

g.GISLOCATION->LATITUDE as "GISLOCATION->LATITUDE"

It is important that you use the AS keyword for table and column aliases. While it can be omitted and the query will still work, the keyword must be present in order for the map to parse the query.

Spacing

Take care with spacing between:

Ensure that there is only one space (or a single new line) between each of the above clauses and column or table names.

You can check the database server log to see how the query has been interpreted and modified.

Linked Tables and Nested SELECTs

The query used by the map display is altered automatically by the map code. This is to limit the data that the query retrieves, because the markers are outside the bounds of the display.

 

The following additional WHERE clause is added, and if there is an existing WHERE clause it is added with an additional AND keyword:

WHERE (GEOPOSITION WITHIN REGION '5.05811437435571,-95.017822265625' TO '-5.05811437435572,-60.982177734375' )

When using data from external Geo SCADA Expert databases, you may need to use a nested SELECT.

 

SELECT * FROM ( SELECT TOP (5000)

A.FULLNAME, B.LATITUDE AS "GISLOCATION->LATITUDE", B.LONGITUDE AS "GISLOCATION->LONGITUDE"

FROM

ODBC_GROUP AS A JOIN ODBC_TEST AS B ON A.ID = B.ID

WHERE (A.GISLOCATIONSOURCE = True)

)

You should structure your query slightly differently if either of the following scenarios applies:

With either scenario, you may want to alter the query to prevent the additional WHERE clause from being added. Do this by including an additional WHERE clause within a nested SELECT, although this will affect performance because the map will query all rows. There is a limit of 5000 records in map queries, which will apply in this case.

For more information about custom tables, see Custom Tables - An Overview.

Sorting

Map markers are drawn from the start of the results to the end of those results. By using an ORDER BY clause you can control which markers appear on top. You may want to take advantage of this to display significant locations last, such as those indicating alarm states.

Further Information

Associating User Queries with a Map Set.

Specify the Refresh Interval of a query on a map: see Configuring a User Query.

Working with SQL: see the Geo SCADA Expert Guide to SQL.

Creating a Hyperlink to a Map.

GISLOCATION: see Expressions.expr-primary-location, also see de-reference operator and Geospatial Index in the Geo SCADA Expert Guide to SQL.

GEOPosition: see Expressions.expr-primary-region and see Expressions.expr-primary-location in the Geo SCADA Expert Guide to SQL.

Disclaimer

Geo SCADA Expert 2022

© 2022 AVEVA Group Plc.  All Rights Reserved.

The Schneider Electric industrial software business and AVEVA have merged to trade as AVEVA Group plc, a UK listed company. Schneider Electric, Life is On Schneider Electric and EcoStruxure are trademarks and the property of Schneider Electric SE and are being licensed to AVEVA by Schneider Electric.