# Perspective Map locations from db with markerColors based on db values

**URL:** <https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273>\
**Category:** Ignition\
**Tags:** namedqueries, perspective, scripting\
**Created:** [November 6, 2024, 11:48am UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273 "2024-11-06T11:48:45Z")\
**Posts on this page:** 1\
**Showing post:** 5

<div class="post-metadata">

**Author:** ![Andreas\_Wallevik](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/andreas_wallevik/32/64045_2.png) [@Andreas\_Wallevik](https://forum.inductiveautomation.com/u/Andreas_Wallevik)\
**Post date:** [November 6, 2024, 2:44pm UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273/5 "2024-11-06T14:44:42Z")

</div>

**main view - displaying colored markers on the map**

_Data source:_  
I created a named query (`GetAllEventLocations`) to fetch event data with location, type, and details.

_Marker setup:_  
Using the solution from [this forum post](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/16)

I defined a marker template (`eventMarker`) with position, color, popup, and tooltip properties. Each marker’s color corresponds to the event type:

**Green - for no accident.**  
**Yellow - for near accident.**  
**Red - for accidents.**

_Transform script:_  
In the `Map.props.layers.ui.marker` binding, I used a transform script to iterate over event data, creating color-coded markers. Popups were initially disabled to keep the map clean on load.

```python
def transform(self, value, quality, timestamp):

    import system
    logger = system.util.getLogger("MAPLOGGER")

    logger.info("Starting transform script for map markers.")

    # Function to recursively copy the eventMarker template without modifying the original
    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.iteritems()}
        if isinstance(original, AbstractMutableJythonSequence):
            return [recursiveCopy(item) for item in original]
        return deepcopy(original)

    markers = []

    # Check if data was received
    if not value:
        logger.error("No data received from eventsDict. Check named query.")
        return markers

    logger.info("Data received from eventsDict: " + str(value))

    # Loop over each event in the eventsDict dataset
    for i, event in enumerate(value):
        logger.info("Processing event " + str(i) + ": " + str(event))

        # Start with a deep copy of the eventMarker to avoid modifying the original template
        protoMarker = recursiveCopy(self.custom.eventMarker)

        # Set the latitude and longitude for the marker based on event data, with defaults
        protoMarker['lat'] = event.get('latitude', 0)
        protoMarker['lng'] = event.get('longitude', 0)

        # Set tooltip text to the accident name, with a default
        protoMarker['tooltip']['content']['text'] = event.get('name', "Unknown Event")

        # Set popup content with accident details, using defaults if data is missing
        protoMarker['popup']['enabled'] = False # Disable popup by default
        protoMarker['popup']['content']['text'] = "Accident Type: " + str(event.get('typeId', "N/A")) + " - " + event.get('name', "Unknown")

        # Set color based on typeId for different accident severity
        typeId = event.get("typeId", 0)
        if typeId == 1:
            protoMarker['icon']['color'] = "#00FF00" # Green for no accident
        elif typeId == 2:
            protoMarker['icon']['color'] = "#FFFF00" # Yellow for near accident
        else:
            protoMarker['icon']['color'] = "#FF0000" # Red for accident

        # Add the customized marker to the list of markers
        markers.append(protoMarker)
        logger.info("Marker created for event " + str(i))

    logger.info("Transform script completed with markers count: " + str(len(markers)))
    return markers

```

**day-specific popup - filtering the events by date**

Date-specific query:  
I created a second query (`GetLocationForSelectedEvent`) to fetch events for a specific date using a `selectedDate` parameter.

_Popup view (DayDetailsPopup):_  
I copied the map component with configurations to `DayDetailsPopup`, binding `custom.eventsDict` to the date-specific query with a transform script to get the json back and passing `selectedDate` as a parameter.

```python
def transform(self, value, quality, timestamp):

    filtered_events = []
    
    for event in value:
        # Only keep events with a typeId of 2 or higher (e.g., near accidents and accidents)
        if event.get("typeId", 0) >= 2:

            simplified_event = {
                "latitude": event.get("latitude"),
                "longitude": event.get("longitude"),
                "name": event.get("eventName"),
                "typeId": event.get("typeId")
            }
            filtered_events.append(simplified_event)
    

    return filtered_events

```

I reused the same transform script to display the date-filtered events, maintaining the color-coding for consistency.

**the results**

The main map shows all events with color-coded markers. The `DayDetailsPopup` map shows only events for the selected date, with popups displayed only when markers are clicked.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/1/61610ac8633f34226392470eb1cc2e2118963ee6.jpeg)

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

---

_[View the full topic](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273)._