Complex map location display requirements

[sbeadle]

Hi All,

I recently had some complex mapping requirements and I thought I'd share them here, then answer my own question!

I have a device which reports its location using a GPS source, and the Latitude, Longitude and Speed in three Analog points, in the Historic data. I want to show the location history on a map:

* Map markers each time the location changes

* Colour them by the age of the location data, e.g. green, through yellow to red (up to 2 days)

* Show the age in hours by the text label

* Show a tooltip with the sample time, speed etc

The key difference here with the main use of SQL for maps is that the location is not in the usual database columns.

I'll follow up this post with the answer I came up with!

[sbeadle]

I'll break the query down, starting with the tables:

FROM
CMQTTTEXTDEVICE AS D

This is the table containing the RTU - it could be DNP3 or something else!

LEFT JOIN CMQTTTEXTPOINTANALOG AS LA ON ( LA.DEVICEID = D.ID AND LA.NAME = 'Loc lat' )
LEFT JOIN CMQTTTEXTPOINTANALOG AS LO ON ( LO.DEVICEID = D.ID AND LO.NAME = 'Loc long' )
LEFT JOIN CMQTTTEXTPOINTANALOG AS LS ON ( LS.DEVICEID = D.ID AND LS.NAME = 'GPS Speed' )

Here are links to my three points from that device, with their names.

LEFT JOIN CDBHISTORIC AS LAH ON ( LAH.ID = LA.ID AND LAH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } )
LEFT JOIN CDBHISTORIC AS LOH ON ( LOH.ID = LO.ID AND LOH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } )
LEFT JOIN CDBHISTORIC AS LSH ON ( LSH.ID = LS.ID AND LSH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } )

And I need to join with historic data for each point. Each one needs the same time constraint.

WHERE
D.FULLNAME = 'Devices.one.Device' AND LAH.RECORDTIME = LOH.RECORDTIME AND LAH.RECORDTIME = LSH.RECORDTIME AND LAH.VALUEASREAL <> 0 AND LOH.VALUEASREAL <> 0

The where clause picks up the specific device I want to map, and 'connects' the historic data in the three points into a single record per time value. Note that this ONLY works when each time value is exactly the same for a reading of Lat, Long and Speed. If this is not the case then you'd need to use a Historic View table instead of the CDBHISTORIC database table. My device also occasionally has a bad location read from GPS and outputs zeroes, so I want to exclude those too.

SELECT
D.FULLNAME as "FullName", LAH.VALUEASREAL as "GISLOCATION->LATITUDE", LOH.VALUEASREAL as "GISLOCATION->LONGITUDE",

This part of the select query gets the main location data. It also gets the device FullName which drives the pick action.

0 as "Background",
case when { FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } < 1440
 then (cast(cast({ FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } as REAL)/1440*255 as INT) + 255*256)
else (255 + cast( (2880-cast({ FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } AS REAL) )/1440*255 as INT)*256)
end as "Foreground",
0 as "Blink",

This sets the RGB foreground colour of the symbol, using the timestamp 'age' to set a colour range over the two day time period.

'Device: ' || D.ParentGroupName || '<BR>' ||
'Time: ' || FormatValue( LAH.RECORDTIME Using 'dd-MMM-yyyy HH:mm') ||'<BR>' ||
'Speed: ' || FormatValue( coalesce(LSH.VALUEASREAL,0) Using '##') || 'kmh' AS "TOOLTIP",
'-' || FormatValue({ FN TIMESTAMPDIFF( SQL_TSI_HOUR, LAH.RecordTime, {OPC 'NOW'}) } using '#') || 'h' as "MarkerText",

A tooltip label will show the device name, time, speed/units. The marker text calculates the age in hours.

'triangledown' as "MarkerShape",
LAH.RecordTime as TIM

Finally, select the marker's shape. I want to show newer items 'on top' of older ones, so I need a time column to sort on later.

select * from (
SELECT {everything else}
)
ORDER BY
TIM ASC

And the whole query must be 'wrapped' in a nested select. This is because we need to 'tell' the query display system we are not using the usual location data fields, and the 'meta' fields can't be used to filter query data by the rectangular displayed map extent.

The Order By clause ensures that the oldest items are plotted first, so newer ones are on the top.

Next Reply - the whole query!

[sbeadle · Accepted solution]

Here's the whole query, and a snip of the result:

select * from (
SELECT
D.FULLNAME as "FullName", LAH.VALUEASREAL as "GISLOCATION->LATITUDE", LOH.VALUEASREAL as "GISLOCATION->LONGITUDE",
0 as "Background", 
case when { FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } < 1440 
then (cast(cast({ FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } as REAL)/1440*255 as INT) + 255*256)
else (255 + cast( (2880-cast({ FN TIMESTAMPDIFF( SQL_TSI_MINUTE, LAH.RecordTime, {OPC 'NOW'}) } AS REAL) )/1440*255 as INT)*256)
end as "Foreground", 
0 as "Blink",
'Device: ' || D.ParentGroupName || '<BR>' ||
'Time: ' || FormatValue( LAH.RECORDTIME Using 'dd-MMM-yyyy HH:mm') ||'<BR>' ||
'Speed: ' || FormatValue( coalesce(LSH.VALUEASREAL,0) Using '##') || 'kmh' AS "TOOLTIP",
'-' || FormatValue({ FN TIMESTAMPDIFF( SQL_TSI_HOUR, LAH.RecordTime, {OPC 'NOW'}) } using '#') || 'h' as "MarkerText",
'triangledown' as "MarkerShape",
LAH.RecordTime as TIM
FROM
CMQTTTEXTDEVICE AS D 
LEFT JOIN CMQTTTEXTPOINTANALOG AS LA ON ( LA.DEVICEID = D.ID AND LA.NAME = 'Loc lat' ) 
LEFT JOIN CMQTTTEXTPOINTANALOG AS LO ON ( LO.DEVICEID = D.ID AND LO.NAME = 'Loc long' ) 
LEFT JOIN CMQTTTEXTPOINTANALOG AS LS ON ( LS.DEVICEID = D.ID AND LS.NAME = 'GPS Speed' ) 
LEFT JOIN CDBHISTORIC AS LAH ON ( LAH.ID = LA.ID AND LAH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } ) 
LEFT JOIN CDBHISTORIC AS LOH ON ( LOH.ID = LO.ID AND LOH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } ) 
LEFT JOIN CDBHISTORIC AS LSH ON ( LSH.ID = LS.ID AND LSH.RECORDTIME BETWEEN { OPC 'Hour - 48 Hours' } AND { OPC 'M' } )
WHERE
D.FULLNAME = 'Devices.one.Device' AND LAH.RECORDTIME = LOH.RECORDTIME AND LAH.RECORDTIME = LSH.RECORDTIME AND LAH.VALUEASREAL <> 0 AND LOH.VALUEASREAL <> 0
)
ORDER BY
TIM ASC

Thank you! I hope this was interesting - feel free to reply with comments.

Steve