# Map marker in perspective

**URL:** <https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 12, 2024, 7:02am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276 "2024-05-12T07:02:36Z")\
**Posts on this page:** 8\
**Page:** 2

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 21, 2024, 8:44am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/21 "2024-05-21T08:44:57Z")

</div>

I just don´t know where to paste that Json into my custom.vehicleStatus

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/8/08ce0ee9cad5a332e50e303a9e8f1c1850a4536d.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 21, 2024, 8:52am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/22 "2024-05-21T08:52:45Z")

</div>

![vehicleStatus paste](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/c/ccb2122a250254712fbcab4c4aa9f45f3abc4152.gif)

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 21, 2024, 8:55am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/23 "2024-05-21T08:55:20Z")

</div>

> [@Transistor](#):
>
> ```python
> [
> {
> "latitude": 42.472783,
> "longitude": -2.40938,
> "name": "05-RIO-MENDA"
> },
> {
> "latitude": 42.789632,
> "longitude": -1.639813,
> "name": "05-NAV-CORDO"
> },
> {
> "latitude": 41.624986,
> "longitude": -4.735528,
> "name": "05-CAL-FORJA"
> },
> {
> "latitude": 40.96796,
> "longitude": -5.661133,
> "name": "05-CAL-RINCO"
> }
> ]
> 
> ```

ah, had no idea can do this, thanks  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/d/9d4c36dad22aff5aaa320f86434d155926604376.png)

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 21, 2024, 10:18am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/24 "2024-05-21T10:18:10Z")

</div>

I have a marker for all of our buildings.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/b/4b43b2d10ed7a69b51662d754acd70509de5e77d.jpeg)

I would like to change the color of each marker depending on the Status value that comes from a Named Querie, status can be 1,2,3,4,5

The column BUSSINESSUNIT match the names of the name in my Json...  
the column Status show the statues.

```python
[
  {
    "latitude": 42.472783,
    "longitude": -2.40938,
    "name": "05-RIO-MENDA"
  },
  {
    "latitude": 42.789632,
    "longit
...

```

The named querie is something like:

```python
SELECT BUSINESSUNIT,
CASE 
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 0 THEN 0 -- La sede no ha tenido presencia ni actividad
WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 1 THEN 1 -- La sede no ha tenido presencia pero sí actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 0 THEN 2 -- La sede ha tenido presencia pero no actividad
WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 1 THEN 3 -- La sede ha tenido presencia y actividad
WHEN ACC00 = 1 AND ACC60 = 0 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
WHEN ACC00 = 1 AND ACC60 = 0 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
WHEN ACC00 = 1 AND ACC60 = 1 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
WHEN ACC00 = 1 AND ACC60 = 1 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
END AS STATUS
FROM
(
SELECT WO.BUSINESSUNIT,
CASE WHEN SUM(CASE 
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('00')
THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS ACC00,
CASE WHEN SUM(CASE 
WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('60','90')
THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END AS ACC60,
case when SUM(CASE 
WHEN WO.WORKTYPE <> 'ACC' 
THEN 1 ELSE 0 END)>0 then 1 else 0 end AS LABOR
/*,case when SUM(CASE 
WHEN WO.WORKTYPE <> 'ACC' and WOWL.WORKER IN (SELECT WOWL.WORKER FROM WORKORDER WO INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
WHERE WO.COMPANY = 'ORAPP' AND WOWL.COMPANY = 'ORAPP' AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = to_char(CURRENT_DATE,'DD/MM/YYYY') AND WORKTYPE = 'ACC' AND WORKORDERSTATE IN ('00','60')) 
THEN 1 ELSE 0 END)>0 then 1 else 0 end AS LABOR0060*/
FROM WORKORDER WO INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
WHERE WO.COMPANY = 'ORANGE' AND WOWL.COMPANY = 'ORANGE'
AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = to_char(CURRENT_DATE,'DD/MM/YYYY')
AND LENGTH(SUBSTR(WO.BUSINESSUNIT,1,9))=9
GROUP BY WO.BUSINESSUNIT
ORDER BY WO.BUSINESSUNIT
)

```

I guess I would need to add a Transform to the marker but not sure how to bind the data from the named query into this transform

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/8/6855c435ee378171e77ccc23dd5e0713f0f79bfd.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 22, 2024, 2:18am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/25 "2024-05-22T02:18:12Z")

</div>

1. We'll make your SQL more readable by adding indents and line breaks in the right places. We can also eliminate two of the WHEN lines.

> **Tidied SQL**
>
> ```sql
> -- Returns two columns, BUSINESSUNIT, STATUS
> SELECT 
> BUSINESSUNIT,
> CASE 
> WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 0 THEN 0 -- La sede no ha tenido presencia ni actividad
> WHEN ACC00 = 0 AND ACC60 = 0 AND LABOR = 1 THEN 1 -- La sede no ha tenido presencia pero sí actividad
> WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 0 THEN 2 -- La sede ha tenido presencia pero no actividad
> WHEN ACC00 = 0 AND ACC60 = 1 AND LABOR = 1 THEN 3 -- La sede ha tenido presencia y actividad
> WHEN ACC00 = 1 AND LABOR = 0 THEN 4 -- La sede tiene presencia sin actividad
> WHEN ACC00 = 1 AND LABOR = 1 THEN 5 -- La sede tiene presencia y actividad
> END AS STATUS
> FROM (
> SELECT 
> WO.BUSINESSUNIT,
> CASE WHEN SUM(
> CASE 
> WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('00') THEN 1 ELSE 0 
> END
> ) > 0 THEN 1 ELSE 0 
> END AS ACC00,
> CASE WHEN SUM(
> CASE 
> WHEN WO.WORKTYPE = 'ACC' AND WO.WORKORDERSTATE IN ('60','90') THEN 1 ELSE 0 
> END
> ) > 0 THEN 1 ELSE 0 
> END AS ACC60,
> CASE WHEN SUM(
> CASE 
> WHEN WO.WORKTYPE <> 'ACC' THEN 1 ELSE 0 
> END
> ) > 0 THEN 1 ELSE 0 
> END AS LABOR
> FROM WORKORDER WO 
> INNER JOIN WORKORDERWORKERLABOR WOWL ON WO.WORKORDER = WOWL.WORKORDER
> WHERE WO.COMPANY = 'ORANGE' AND WOWL.COMPANY = 'ORANGE'
> AND TO_CHAR(wowl.laborinitdate,'DD/MM/YYYY') = TO_CHAR(CURRENT_DATE,'DD/MM/YYYY')
> AND LENGTH(SUBSTR(WO.BUSINESSUNIT, 1, 9)) = 9
> GROUP BY WO.BUSINESSUNIT
> ORDER BY WO.BUSINESSUNIT
> )
> 
> ```

1. On the Map component create `custom.businessUnits` property. Bind that to your query and return the data in JSON format. You should get something like,

```python
[
    {"BUSINESSUNIT": "05-RIO-MENDA", "STATUS": 5},
    {"BUSINESSUNIT": "05-NAV-CORDO", "STATUS": 3},
    {"BUSINESSUNIT": "05-CAL-FORJA", "STATUS": 0},
    {"BUSINESSUNIT": "05-CAL-RINCO", "STATUS": 2}
]

```

1. Modify the script transform:

> **Map - script transform on ui.marker property binding**
>
> ```python
> def transform(self, value, quality, timestamp):
> 
> def recursiveCopy(original):
> # With credit to https://forum.inductiveautomation.com/u/lrose
> # https://forum.inductiveautomation.com/t/copy-dictionary-and-modify-it-without-affecting-the-original/62297/9
> from copy import deepcopy
> from com.inductiveautomation.ignition.common.script.abc import AbstractMutableJythonMap,AbstractMutableJythonSequence
> 	
> if isinstance(original,AbstractMutableJythonMap):
> return {key:recursiveCopy(value) for key,value in original.iteritems()}
> 	
> if isinstance(original,AbstractMutableJythonSequence):
> return [recursiveCopy(item) for item in original]
> 	
> return deepcopy(original)
> 	
> def getStatus(businessUnit):
> for bu in self.custom.businessUnits:
> if bu['BUSINESSUNIT'] == businessUnit:
> return bu['STATUS']
> return 6	# BUSINESSUNIT not found.
> 	
> statusColors = ["white", "red", "orange", "purple", "yellow", "green", "black"]
> statusMessages = [
> 'La sede no ha tenido presencia ni actividad.',
> 'La sede no ha tenido presencia pero sí actividad.',
> 'La sede ha tenido presencia pero no actividad.',
> 'La sede ha tenido presencia y actividad.',
> 'La sede tiene presencia sin actividad.',
> 'La sede tiene presencia y actividad.',
> 'Error.'
> ]
> markers = []
> for item in value:
> protoMarker = recursiveCopy(self.custom.sampleMarker)
> protoMarker['lat'] = item['latitude']
> protoMarker['lng'] = item['longitude']
> s = getStatus(item['name'])
> protoMarker['icon']['color'] = statusColors[s]
> protoMarker['tooltip']['content']['text'] = item['name'] + ': ' + statusMessages[s]
> markers.append(protoMarker)
> return markers
> 
> ```

1. Result:  
 ![Spain with color markers](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/f/3f0a77efa64b8f70bfac5e04b03471c8e4c126cf.png)

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 22, 2024, 7:51am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/26 "2024-05-22T07:51:44Z")

</div>

Hello, works great, thanks a lot,

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/9/593e2df2dd44131acd8468b6ad8c4d7f9c8311da.jpeg)

I just added a timer to make sure businessUnits were loaded in self.custom because the transform script comes before the custom.businessUnits

```python

    import time
    

    timeout = 10  
    start_time = time.time()
    
    while not hasattr(self.custom, 'businessUnits'):
        if time.time() - start_time > timeout:
            raise AttributeError("Tiempo de espera agotado: self.custom no tiene el atributo 'businessUnits'. Asegúrate de que esté correctamente definido.")
        time.sleep(0.1) # esperar 100 ms antes de verificar nuevamente
    
    def recursiveCopy(original):
        from copy import deepcopy
        from com.inductiveautomation.ignition.common.script.abc import AbstractMutableJythonMap, AbstractMutableJythonSequence

        if isinstance(original, AbstractMutableJythonMap):
            return {key: recursiveCopy(value) for key, value in original.items()}

        if isinstance(original, AbstractMutableJythonSequence):
            return [recursiveCopy(item) for item in original]

        return deepcopy(original)

    def getStatus(businessUnit):
        for bu in self.custom.businessUnits:
            # Comparar directamente con businessUnit
            if bu['BUSINESSUNIT'] == businessUnit:
                return bu['STATUS']
        return 6 # BUSINESSUNIT no encontrado.

    statusColors = ["white", "red", "orange", "purple", "yellow", "green", "transparent"]
    statusMessages = [
        'La sede no ha tenido presencia ni actividad.',
        'La sede no ha tenido presencia pero sí actividad.',
        'La sede ha tenido presencia pero no actividad.',
        'La sede ha tenido presencia y actividad.',
        'La sede tiene presencia sin actividad.',
        'La sede tiene presencia y actividad.',
        'Error.'
    ]

    markers = []
    for item in value:
        protoMarker = recursiveCopy(self.custom.sampleMarker)
        protoMarker['lat'] = item['latitude']
        protoMarker['lng'] = item['longitude']
        s = getStatus(item['name'])
        protoMarker['icon']['color'] = statusColors[int(s)] # Asegúrate de que el índice sea un entero
        protoMarker['tooltip']['content']['text'] = item['name'] + ': ' + statusMessages[int(s)] # Asegúrate de que el índice sea un entero
        markers.append(protoMarker)

    return markers 
    

```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/0/4015e9405267178c9bb9200b75a22b6f032d8b66.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 22, 2024, 8:06am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/27 "2024-05-22T08:06:10Z")

</div>

> [@Alejandro\_Alaco](#):
>
> I just added a timer ...

Very bad. If you find yourself using `sleep()` or timer functions in your scripts that then there is something wrong and you should find a better solution.

- Add a Change Script .. to `custom.businessUnits`.  
Add the line  
`self.refreshBinding('props.layers.ui.marker')`

That will refresh the markers any time the `custom.businessUnits` changes.  
You can use the same script on the other custom tag so that the markers update if the status changes.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 22, 2024, 8:17am UTC](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/28 "2024-05-22T08:17:55Z")

</div>

Ok, did this, working well! thanks Transistor

[Previous page](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276.md?page=1)