# Perspective Map locations from db with markerColors based on db values **URL:** \ **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:** 5\ **Page:** 1
**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, 11:48am UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273/1 "2024-11-06T11:48:45Z")
Hi, I'm buliding a demo for a traffic-monitoring app for a customer. I have set up a simple db with Events, EventType and EventLocations I want lat and long coordinates from EventLocations to display on a map with different colored markers based on the severity of the event ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/3/a316ea809011dc2580a3a457e470133aa6083045.png) 1 = Green, 2 = Yellow, 3 = Red. I have implemented this in other parts of my apps like so: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3ddde21481b5f135dc76c8388bf786e793d3f869.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/c/9cd7ac80153ddefe2bad32685384f54290299fed.jpeg) So, going back to the map idea. I'm starting by trying to get this working for all EventLocations, and will be doing the filtereing per day after the fact. I have made this Named Query: SELECT e.id AS eventId, e.eventName, e.eventTimestamp, e.location, l.latitude, l.longitude, et.id AS typeId FROM KANUF\_Event e JOIN KANUF\_Locations l ON e.id = l.event\_id JOIN KANUF\_EventType et ON e.eventType = et.id From what i can find in some of the [resources](https://forum.inductiveautomation.com/t/map-marker-in-perspective/90276/16) here, the correct way to do this is to create custom properties on the map component - - one to hold the events (e.g eventLocations) - and one to hold the markers (e.g eventMarkers) then, doing a property binding from map.props.layers.ui.marker to the custom prop: **this.custom.eventLocations** I assume then i could do a query binding with a transform to populate the **eventLocations** custom prop with a dataset or list of json objects? I've tried this, but i keep getting a component error. Suspecting something is wrong with my syntax. Anyone here attempted something similar and can share some insight? ---
**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:00pm UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273/2 "2024-11-06T14:00:27Z")
Solved! ---
**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:** [November 6, 2024, 2:01pm UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273/3 "2024-11-06T14:01:28Z")
Would you like to share how? (You were asking us to!) ---
**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:05pm UTC](https://forum.inductiveautomation.com/t/perspective-map-locations-from-db-with-markercolors-based-on-db-values/98273/4 "2024-11-06T14:05:51Z")
Absolutely. I will. Just want to figure out the filtering first so i can post the whole solution and make sure it's working as expected! 🙂 ---
**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")
**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)