# Reading values from database and converting it using python

**URL:** <https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508>\
**Category:** Maker\
**Created:** [March 16, 2021, 5:58am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508 "2021-03-16T05:58:19Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 16, 2021, 5:58am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/1 "2021-03-16T05:58:19Z")

</div>

Hi all. I am currently trying to read values from MySQL database and convert it into like an image to be displayed using the Image Perspective using Scripting since it’s feature in Ignition but I am not too entirely sure how to go about and do it…

Example: I want to read like 32x32 values from database in MySQL then run a script to process the values which in turn will return an Image that will be displayed using the Image Perspective.

Values from Database:

 ![Database_Values](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/e/ee4d74c804e554c32d19d53163e2e1a72b2c7ef2.jpeg)

The Data type is Decimal [5,2]

Create something like this to Display in Ignition:

 ![Thermal Image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/0/e0e3cea2ffb590f6ec0be03ac8108ffcb632be2e.jpeg)

Wondering if I could use this to dynamically change the picture through scripting by doing a transformation of the 32x32 values into an image file for display

 ![Scripting using a Tag](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/018ebcac8c188a83da1f1257f08f64a3ad7b0e3c.jpeg)

Taken from the Image Binding

[imagevalues (1).csv](https://forum.inductiveautomation.com/uploads/short-url/cteJXZJ0NPjvAVZvnIs28UROx2t.csv) (26.8 KB)

Edited the post for clarity  
Edited 2: Added CSV file for sample Data

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [March 16, 2021, 11:22am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/2 "2021-03-16T11:22:53Z")

</div>

What values are your database table storing? Are they binary values or something? And you want to create an image showing a black or white pixel map depending on the table values? Or what are you trying to create? You haven’t given any useful details…

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [March 16, 2021, 11:57am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/3 "2021-03-16T11:57:51Z")

</div>

Hi @e0310912, is this related to the image question you had [here](http://forum.inductiveautomation.com/t/dynamic-image-using-perspective-image/44288)?

If so, this may provide a bit more context for @nminchin. Unfortunately, I am only familiar with displaying BLOBs from the DB so I am unlikely to provide anything else of further use.

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 16, 2021, 12:22pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/4 "2021-03-16T12:22:37Z")

</div>

I edited the question, sorry for the lack of the information and context

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 16, 2021, 12:36pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/5 "2021-03-16T12:36:24Z")

</div>

Yes, I realized that I need to do quite a bit of things to get all 1024 panels up…

Which I don’t think is quite effective for my case… was hoping maybe the scripting transformation could help with this

 ![XY Chart Heat Map](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c4479c251a9548c9854b3050e978dcbad0f4c2f.jpeg)

---

<div class="post-metadata">

**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\
**Post date:** [March 16, 2021, 12:45pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/6 "2021-03-16T12:45:58Z")

</div>

I don’t have a solution for you, but what you asking for is called a Heat Map. There are many Python libraries out there that can build Heat Maps from raw data. But as I know nothing about using them I can’t recommend any because I don’t what is compatible with Ignition.

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [March 16, 2021, 1:02pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/7 "2021-03-16T13:02:03Z")

</div>

Hi @e0310912, I agree it might be a bit of work but I still think an XY chart possibly the best solution if you plan on using native Ignition components.

As @peter stated, there are other python solution options out there but this would take a fair amount of research to get the right one, unless we’re lucky and the right person stumbles across this post and can point us in the right direction.

If I get some time later (that’s a pretty big IF unfortunately 😅), I may try and create something using the data you have provided. I will post any feedback here.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 16, 2021, 4:16pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/8 "2021-03-16T16:16:15Z")

</div>

Can we get some sample data, say in a csv file? It would be easier than trying to manually type 32^2 values. 😉

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 16, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/9 "2021-03-16T16:50:09Z")

</div>

Edited in the Post now in a .csv file

Though the data is just randomly generated to test… so the image obtained might mean nothing

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 16, 2021, 4:51pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/10 "2021-03-16T16:51:02Z")

</div>

No worries, it will give us something to play with.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 16, 2021, 8:36pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/11 "2021-03-16T20:36:03Z")

</div>

Easysvg is a nice little wrapper to make svg files. Unzip and put in the gateway’s user-lib/pylib/site-packages folder.  
[easysvg.zip](https://forum.inductiveautomation.com/uploads/short-url/wY51CE88Fu5FMMVecxJajTF2wx2.zip) (6.7 KB)

View attached using the csv data posted. The SVG can be converted to base64 to display in an image component.  
[heatmap\_test.zip](https://forum.inductiveautomation.com/uploads/short-url/DNp1RjLC9yUNKbbFzdiZdg5LHc.zip) (76.0 KB)

[https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/5158deeed962746b90ecd7748826802287630b09.mp4](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/1/5158deeed962746b90ecd7748826802287630b09.mp4)

Script:

```auto
	import easysvg
	import base64
	
	class heatPalette:
		''' 
			Calculate the RGB value of a point on a gradient.
			 Usage: heatPalette(minval, maxval, [colors])
		    	minval - minimun value to scale to
		    	maxval - maximum value to scale to
		    	colors - A list of RGB colors delineating a series of
			             adjacent linear color gradients between each pair.
			 Example: scale the values between 20 and 35 to a three color gradient.
			 	p = heatPalette(20, 35, ['#0000FF', '#FFFF00', '#FF0000']) 
			 	After the instance is created, call it with a value to get the scaled color
				p(20) returns '#0000FF'
				p(25) returns '#AAAA55'
		'''
		
		def __init__ (self, minval, maxval, colors):
			self.minval = minval
			self.maxval = maxval
			self.colors = []
			for color in colors:
				if type(color). __name__ == 'str':
					c = self.hex_to_rgb(color)
				elif type(color). __name__ == 'list':
					c = tuple(color)
				elif type(color). __name__ == 'tuple':
					c = color
				self.colors.append(c)
				
		def __call__ (self, value):
			return self.calc(value)
		
		def calc(self, value):
			import sys
			# Smallest possible difference.
			epsilon = sys.float_info.epsilon
			
			# Keep value within limits
			if value < self.minval:
				value = self.minval
			if value > self.maxval:
				value = self.maxval
			''' 
			 	Determine where the given value falls proportionality within
			 	the range from minval->maxval and scale that fractional value
			 	by the total number in the "colors" pallette.
			 '''
			i_f = float(value-self.minval) / float(self.maxval-self.minval) * (len(self.colors)-1)
			''' 
				Determine the lower index of the pair of color indices this
			 	value corresponds and its fractional distance between the lower
			 	and the upper colors.
			 '''
			# Split into integer & fractional parts. 
			i, f = int(i_f // 1), i_f % 1  
			# Does it fall exactly on one of the color points?
			if f < epsilon:
				return self.rgb_to_hex(self.colors[i])
			# Otherwise return a color within the range between them.
			else:  
				(r1, g1, b1), (r2, g2, b2) = self.colors[i], self.colors[i+1]
				print (r1, g1, b1), (r2, g2, b2)
				print int(r1 + f*(r2-r1)), int(g1 + f*(g2-g1)), int(b1 + f*(b2-b1))
				return self.rgb_to_hex((int(r1 + f*(r2-r1)), int(g1 + f*(g2-g1)), int(b1 + f*(b2-b1))))
		
			
		def hex_to_rgb(self, value):
			'''
				Convert a hex RGB value to a tuple of 0-255 values
			'''
			value = value.lstrip('#')
			return tuple(int(value[i:i+2], 16) for i in (0, 2, 4))
		
		def rgb_to_hex(self, rgb):
			'''
				Convert an RGB tuple to a hex string
			'''
			return '#%02x%02x%02x' % rgb
	
	# Define palette for heatmap
	palette = heatPalette(20, 35, ['#0000FF', '#FFFF00', '#FF0000']) 
		
	# Width, Height of each cell in heatmap
	cellSize = (30, 30)
	# Define padding area aroung the heatmap
	padding = 50
	
	# Create SVG
	svg = easysvg.SvgGenerator()
	svg.begin(cellSize[0] * (dataIn.columnCount - 2) + 2 * padding, cellSize[1] * dataIn.rowCount + 2* padding)
	
	for i, row in enumerate(dataIn):
		for j, col in enumerate(list(row)[2:]):
			color = palette(col)
			svg.rect(j*cellSize[0] + padding, i * cellSize[1] + padding, cellSize[0], cellSize[1], color)
	
	
	#svg.text('This is text', 20, 20)
	#svg.circle(75, 75, 50, '#ff0000')
	#svg.rect(150, 150, 50, 75, '#ff0000')
	
	
	svg.end()
	svg_string = svg.get_svg()
	
	b64 = base64.b64encode(svg_string)
	
	self.getSibling("Image").props.source = 'data:image/svg+xml;base64,' + b64

```

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [March 16, 2021, 8:59pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/12 "2021-03-16T20:59:32Z")

</div>

Looks like @JordanCClark beat me to it 😄

However, if you do want to go down the XY chart route here is what I have created:

 ![HeatMap](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2a0dfcd7770d9ee13fd4ce7249251a312970d3b2.jpeg)

> **xAxis Props**
>
> ```auto
> [
> {
> "name": "col",
> "label": {
> "enabled": false,
> "text": "Time",
> "color": ""
> },
> "inversed": false,
> "visible": false,
> "tooltip": {
> "enabled": true,
> "text": "",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "category",
> "category": {
> "break": {
> "enabled": false,
> "startCategory": "",
> "endCategory": "",
> "size": 0.05
> }
> },
> "date": {
> "baseInterval": {
> "enabled": false,
> "timeUnit": "hour",
> "count": 1,
> "skipEmptyPeriods": false
> },
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "break": {
> "enabled": false,
> "startDate": "",
> "endDate": "",
> "size": 0.05
> },
> "inputFormat": "yyyy-MM-dd kk:mm:ss",
> "format": "M/d"
> },
> "value": {
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "logarithmic": false,
> "break": {
> "enabled": false,
> "startValue": 0,
> "endValue": 100,
> "size": 0.05
> },
> "format": "#,###.##"
> },
> "appearance": {
> "opposite": false,
> "inside": false,
> "labels": {
> "color": "",
> "opacity": 1
> },
> "grid": {
> "color": "",
> "opacity": 1,
> "dashArray": "",
> "minDistance": 60,
> "position": 0.5
> },
> "font": {
> "size": "",
> "weight": 500
> }
> }
> }
> ]
> 
> ```

> **yAxis Props**
>
> ```auto
> [
> {
> "name": "row",
> "label": {
> "enabled": false,
> "text": "Process Temp",
> "color": ""
> },
> "inversed": false,
> "visible": false,
> "tooltip": {
> "enabled": true,
> "text": "",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "category",
> "category": {
> "break": {
> "enabled": false,
> "startCategory": "",
> "endCategory": "",
> "size": 0.05
> }
> },
> "date": {
> "baseInterval": {
> "enabled": false,
> "timeUnit": "hour",
> "count": 1,
> "skipEmptyPeriods": false
> },
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "break": {
> "enabled": false,
> "startDate": "",
> "endDate": "",
> "size": 0.05
> },
> "inputFormat": "yyyy-MM-dd kk:mm:ss",
> "format": "M/d/yyyy HH:mm:ss"
> },
> "value": {
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "logarithmic": false,
> "break": {
> "enabled": false,
> "startValue": 0,
> "endValue": 100,
> "size": 0.05
> },
> "format": "#,###.##"
> },
> "appearance": {
> "opposite": false,
> "inside": false,
> "labels": {
> "color": "",
> "opacity": 1
> },
> "grid": {
> "color": "",
> "opacity": 1,
> "dashArray": "",
> "minDistance": null,
> "position": 0.5
> },
> "font": {
> "size": "",
> "weight": 500
> }
> }
> }
> ]
> 
> ```

> **series props**
>
> ```auto
> [
> {
> "name": "data",
> "label": {
> "text": "Process Temp"
> },
> "visible": true,
> "hiddenInLegend": false,
> "defaultState": {
> "visible": true
> },
> "data": {
> "source": "data",
> "x": "col",
> "y": "row"
> },
> "xAxis": "col",
> "yAxis": "row",
> "zIndex": 0,
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "column",
> "candlestick": {
> "open": {
> "x": "",
> "y": ""
> },
> "high": {
> "x": "",
> "y": ""
> },
> "low": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "opacity": 1,
> "width": 1
> },
> "stacked": false,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> }
> },
> "heatRules": {
> "enabled": false,
> "max": "",
> "min": "",
> "dataField": ""
> }
> }
> },
> "column": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "opacity": 1,
> "width": 1
> },
> "width": null,
> "height": null,
> "stacked": false,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> }
> },
> "heatRules": {
> "enabled": true,
> "max": "#F40505",
> "min": "#2D10FC",
> "dataField": "val"
> }
> }
> },
> "line": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "connect": true,
> "tensionX": 1,
> "tensionY": 1,
> "minDistance": 0.5,
> "stroke": {
> "width": 3,
> "opacity": 1,
> "color": "",
> "dashArray": ""
> },
> "fill": {
> "color": "",
> "opacity": 0
> },
> "bullets": [
> {
> "enabled": false,
> "render": "circle",
> "width": 10,
> "height": 10,
> "label": {
> "text": "{value}",
> "position": {
> "dx": 0,
> "dy": 0
> }
> },
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "width": 1,
> "opacity": 1
> },
> "rotation": 0,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> },
> "rotation": ""
> },
> "heatRules": {
> "enabled": false,
> "max": 100,
> "min": 2,
> "dataField": ""
> }
> }
> ]
> }
> },
> "stepLine": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "connect": true,
> "tensionX": 1,
> "tensionY": 1,
> "minDistance": 0.5,
> "stroke": {
> "width": 3,
> "opacity": 1,
> "color": "",
> "dashArray": ""
> },
> "fill": {
> "color": "",
> "opacity": 0
> },
> "bullets": [
> {
> "enabled": true,
> "render": "circle",
> "width": 10,
> "height": 10,
> "label": {
> "text": "{value}",
> "position": {
> "dx": 0,
> "dy": 0
> }
> },
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "width": 1,
> "opacity": 1
> },
> "rotation": 0,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> },
> "rotation": ""
> },
> "heatRules": {
> "enabled": false,
> "max": 100,
> "min": 2,
> "dataField": ""
> }
> }
> ]
> }
> }
> }
> ]
> 
> ```

> **Named Query for Binding**
>
> Binding for dataSources property in chart (I used MSSQL), also see the script transform.
> 
> ```auto
> SELECT Data_0,
> Data_1,
> Data_2,
> Data_3,
> Data_4,
> Data_5,
> Data_6,
> Data_7,
> Data_8,
> Data_9,
> Data_10,
> Data_11,
> Data_12,
> Data_13,
> Data_14,
> Data_15,
> Data_16,
> Data_17,
> Data_18,
> Data_19,
> Data_20,
> Data_21,
> Data_22,
> Data_23,
> Data_24,
> Data_25,
> Data_26,
> Data_27,
> Data_28,
> Data_29,
> Data_30,
> Data_31
> FROM heatmap
> WHERE id = :id
> 
> ```

> **Binding Script Transform**
>
> Script transform for the query binding.
> 
> ```auto
> # Return variable
> dataSources = {}
> 	
> # Formatted data array
> dataArr = []
> 	
> # Convert to pyDataSet as easier to iterate
> pyData = system.dataset.toPyDataSet(value)
> 	
> # Instantiate the row index
> rowId = 0
> 	
> # Loop through the rows
> for row in pyData:
> # Instantiate the column index
> colId = 0
> 		
> # Loop through the columns
> for val in row:
> # Create a dictionary in a format that is easy to recognise by the chart
> data = {
> 'row':rowId,
> 'col':colId,
> 'val':val
> }
> 			
> # Append the data to the data array
> dataArr.append(data)
> 			
> # Iterate the column index
> colId += 1
> 		
> # Iterate the row index
> rowId += 1
> 	
> # Create the return dictionary
> dataSources = {'data':dataArr}
> 	
> # Pass to the component property
> return dataSources
> 
> ```

You may find @JordanCClark solution is better as the chart can appear a bit laggy when rendering the data. But at least this gives you another option if you need it.

If you would like a backup of the project feel free to DM me and I can send it over to you.

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 17, 2021, 1:58am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/13 "2021-03-17T01:58:14Z")

</div>

This looks good, I think I will need time to read the program to understand it and edit it slightly.

The idea would be to actually read the values from a database and and it will update the picture after a period of time as new values comes in.

I will continue to work on it, thank you for the help

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 17, 2021, 2:03am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/14 "2021-03-17T02:03:03Z")

</div>

This looks great too, I appreciate the help and advice you have given me so far. I might run the two options to see what’s the big difference and see how it goes from there. Ignition does have quite an elaborate way of implementing things here… I guess no harm learning different ways of doing it

Thank you

---

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [March 19, 2021, 9:35pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/15 "2021-03-19T21:35:14Z")

</div>

Hi @e0310912, the heat map example I produced is now on the Exchange [here](https://inductiveautomation.com/exchange/2127/overview) if you need a further reference.

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 22, 2021, 5:08am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/16 "2021-03-22T05:08:40Z")

</div>

Thank you for uploading your code, managed to get it working on my side.

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 25, 2021, 6:38am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/17 "2021-03-25T06:38:56Z")

</div>

Hi, I would like to ask is it possible to do interpolation for the image ?

Like increase it from 32 X 32 to 1024 X 1024 pixels ?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 26, 2021, 2:00pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/18 "2021-03-26T14:00:58Z")

</div>

Is it possible? Yes.  
Do I know how to do it? Nope. Some of this stuff I haven’t had to think about in 30+ years

If memory serves, you would need to perform what is called a bicubic interpolation on your dataset. This can be done through Apache Math’s [PiecewiseBicubicSplineInterpolator](https://commons.apache.org/proper/commons-math/javadocs/api-3.6/org/apache/commons/math3/analysis/interpolation/PiecewiseBicubicSplineInterpolator.html), which is now included in Ignition.

Start reading up on it from the link above, and go over the [Java example here](https://www.programcreek.com/java-api-examples/?api=org.apache.commons.math3.analysis.interpolation.PiecewiseBicubicSplineInterpolator).

I’ve been pretty busy with work the past few days, so I’m not certain how much I can help. Hopefully things will calm down a bit, paperwork will be complete, and I can take a closer look. Maybe.

---

<div class="post-metadata">

**Author:** ![e0310912](https://avatars.discourse-cdn.com/v4/letter/e/6de8d8/32.png) [@e0310912](https://forum.inductiveautomation.com/u/e0310912)\
**Post date:** [March 26, 2021, 2:19pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/19 "2021-03-26T14:19:41Z")

</div>

Thank you , I will go and read up the on the links and build it on my own thank you.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 27, 2021, 3:39pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/20 "2021-03-27T15:39:15Z")

</div>

Got some time this morning to check it out.  
[heatmap2.zip](https://forum.inductiveautomation.com/uploads/short-url/uIPHx5SwSmYYlr1R8Quuxk7ayCD.zip) (872.8 KB)

Keep in mind that for each doubling of resolution, the amount of cells multiplies by 4. This means a significant increase in memory usage on the gateway, which would likely result in an out of memory error if the heap settings are at default and using a high interpolation.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/9/69250f3bffa0b5f5dd46067e595715e00eb83e7f.png)

Higher interpolation does not equate to better results. Pick the lowest output that gives you the result you want. As shown in my example the image starts to fade at higher settings.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/8/68d16c08e79389ab18ae39fe9e76c8b7a935ce46.jpeg)

My last recommendation is _ **do not run this in a client** _. As stated earlier, this can use quite a bit of gateway memory, multiplied by the number of clients doing it at the same time.

I would do the following:

- add a column to your table to hold the status of samples not yet processed.
- use a gateway script to process the data into your image
- store the base64 data in another db table, along with the timestamp to to tie the two tables together.
- in the client, just grab the base64 data from the db and display it.

Full code minus the dataset creation:

```python
    # _________________________________ #
    # #
    # BEGINNING OF PROGRAM #
	# _________________________________ #
	
	
    # _________________________________ #
	# #
	# IMPORTS AND CLASSES #
	# _________________________________ #
	
	import org.apache.commons.math3.analysis.interpolation.PiecewiseBicubicSplineInterpolator as PBSI
	import easysvg
	import base64
		
	class heatPalette:
		''' 
			Calculate the RGB value of a point on a gradient.
			 Usage: heatPalette(minval, maxval, [colors])
		    	minval - minimun value to scale to
		    	maxval - maximum value to scale to
		    	colors - A list of RGB colors delineating a series of
			             adjacent linear color gradients between each pair.
			 Example: scale the values between 20 and 35 to a three color gradient.
			 	p = heatPalette(20, 35, ['#0000FF', '#FFFF00', '#FF0000']) 
			 	After the instance is created, call it with a value to get the scaled color
				p(20) returns '#0000FF'
				p(25) returns '#AAAA55'
		'''
		
		def __init__ (self, minval, maxval, colors):
			self.minval = minval
			self.maxval = maxval
			self.colors = []
			for color in colors:
				if type(color). __name__ == 'str':
					c = self.hex_to_rgb(color)
				elif type(color). __name__ == 'list':
					c = tuple(color)
				elif type(color). __name__ == 'tuple':
					c = color
				self.colors.append(c)
				
		def __call__ (self, value):
			return self.calc(value)
		
		def calc(self, value):
			import sys
			# Smallest possible difference.
			epsilon = sys.float_info.epsilon
			
			# Keep value within limits
			if value < self.minval:
				value = self.minval
			if value > self.maxval:
				value = self.maxval
			''' 
			 	Determine where the given value falls proportionality within
			 	the range from minval->maxval and scale that fractional value
			 	by the total number in the "colors" pallette.
			 '''
			i_f = float(value-self.minval) / float(self.maxval-self.minval) * (len(self.colors)-1)
			''' 
				Determine the lower index of the pair of color indices this
			 	value corresponds and its fractional distance between the lower
			 	and the upper colors.
			 '''
			# Split into integer & fractional parts. 
			i, f = int(i_f // 1), i_f % 1  
			# Does it fall exactly on one of the color points?
			if f < epsilon:
				return self.rgb_to_hex(self.colors[i])
			# Otherwise return a color within the range between them.
			else:  
				(r1, g1, b1), (r2, g2, b2) = self.colors[i], self.colors[i+1]
				return self.rgb_to_hex((int(r1 + f*(r2-r1)), int(g1 + f*(g2-g1)), int(b1 + f*(b2-b1))))
		
			
		def hex_to_rgb(self, value):
			'''
				Convert a hex RGB value to a tuple of 0-255 values
			'''
			value = value.lstrip('#')
			return tuple(int(value[i:i+2], 16) for i in (0, 2, 4))
		
		def rgb_to_hex(self, rgb):
			'''
				Convert an RGB tuple to a hex string
			'''
			return '#%02x%02x%02x' % rgb
			
	# _________________________________ #
	# #
	# INITIAL SETTINGS #
	# _________________________________ #

	# num_coordsIn is the input resoultion. num_coordsOut is the output resolution.
	numXcoordsIn, numYcoordsIn = (dataIn.rowCount, dataIn.columnCount - 2)
	numXcoordsOut, numYcoordsOut = (256, 256)

	self.getSibling('Progress1').meta.visible = True
	self.getSibling('Progress1').props.max = numXcoordsIn - 1
	self.getSibling('Progress1').props.value = 0

	self.getSibling('Progress2').meta.visible = True
	self.getSibling('Progress2').props.max = numXcoordsOut - 1
	self.getSibling('Progress2').props.value = 0

	# Define palette for heatmap
	palette = heatPalette(20, 35, ['#0000FF', '#FFFF00', '#FF0000']) 

	# Width, Height of each cell in heatmap
	cellSize = (30, 30)
	# Define padding area aroung the heatmap
	padding = 50			

	# _________________________________ #
	# #
	# DISPLAY ORIGINAL SAMPLE #
	# _________________________________ #

	svg = easysvg.SvgGenerator()
	# Define svg size
	svg.begin(cellSize[0] * numYcoordsIn + 2 * padding, cellSize[1] * numXcoordsIn + 2* padding)
	
	for i, row in enumerate(dataIn):
		self.getSibling('Progress1').props.value = i
		for j, col in enumerate(list(row)[2:]):
			color = palette(col)
			svg.rect(j*cellSize[0] + padding, i * cellSize[1] + padding, cellSize[0], cellSize[1], color, color)
	
	## Other examples of usage
	#svg.text('This is text', 20, 20)
	#svg.circle(75, 75, 50, '#ff0000')
	#svg.rect(150, 150, 50, 75, '#ff0000')
		
	svg.end()

	## Moved to base64 function
	# svg_string = svg.get_svg()
	
	b64 = base64.b64encode(svg.get_svg())
	
	self.getSibling("Image1").props.source = 'data:image/svg+xml;base64,' + b64
	
	# _________________________________ #
	# #
	# DISPLAY INTERPOLATED DATA #
	# _________________________________ #
	
	# Create a function to scale coordinates of the output image to the input image
	scale = lambda valIn, outScale, inScale: valIn/float(outScale) * inScale

# These functions have been moved into the interpolator constructor to save memory.
	## This may look odd, but these will give a list of floats for the coordinates.
	## [0.0, 1.0, 2.0, ...]
	# coordValsX = [i/10.0 for i in range(0, numXcoordsIn*10, 10)]
	# coordValsY = [i/10.0 for i in range(0, numYcoordsIn*10, 10)]
	## Make a values from the dataset a list of lists
	# fVals = [list(row)[2:] for row in dataIn]
	## Create the interpolator
	# interpolator = PBSI().interpolate(coordValsX, coordValsX, fVals)
	
	interpolator = PBSI().interpolate([i/10.0 for i in range(0, numXcoordsIn*10, 10)], [i/10.0 for i in range(0, numYcoordsIn*10, 10)], [list(row)[2:] for row in dataIn])
		
	svg = easysvg.SvgGenerator()
	# Define svg size
	svg.begin(cellSize[0] * numXcoordsOut + 2 * padding, cellSize[1] * numYcoordsOut + 2 * padding)
	
	for i, row in enumerate(range(numYcoordsOut)):
		self.getSibling('Progress2').props.value = i
		for j, col in enumerate(range(numYcoordsOut)):
			scaleX = scale(i, numXcoordsOut-1, numXcoordsIn-1)
			scaleY = scale(j, numYcoordsOut-1, numYcoordsIn-1)
			value = interpolator.value(scaleX, scaleY)
			color = palette(value)
			svg.rect(j*cellSize[0] + padding, i * cellSize[1] + padding, cellSize[0], cellSize[1], color, color)		
	svg.end()

	## Moved to base64 function
	#svg_string = svg.get_svg()

	b64 = base64.b64encode(svg.get_svg())
	
	self.getSibling('Image2').props.source = 'data:image/svg+xml;base64,' + b64
	
	# _________________________________ #
	# #
	# REMOVE PROGRESS BARS #
	# _________________________________ #	
	self.getSibling('Progress1').meta.visible = False
	self.getSibling('Progress2').meta.visible = False

```

[Next page](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508.md?page=2)