Maps

What is a map in EdControls?

A map in EdControls is a spatial reference for a project — a site plan, a floor plan, or any other image that auditors and field workers use to locate work in physical space. Tickets and audits can be positioned on a map: a ticket carries its (x, y) pixel coordinate and the map it lives on; an audit can be linked to one or more maps through the audits_maps bridge.

A map row is a thin document about the map file — its name, author, which group it belongs to, when it was created or archived. The image itself is not stored in the analytics database. Maps are organized into map groups (map_groups) — for example, a "Building A" group containing one map per floor.

A map lives on a single project. Its id is project-scoped: two projects with a "Ground Floor" map are two separate maps rows.

Domain ER diagram

The audits and tickets tables are documented in their own domains (Audits, Tickets). This subgraph just shows how maps connect outward.

Tables in this domain

• maps — one row per map (site plan / floor plan).
• map_groups — folders that group related maps.
• audits_maps — bridge linking audits to the maps they cover.

Where the spatial coordinates live

The maps table itself does not store pixel or geo coordinates — it carries metadata about the map file. Spatial positioning happens on tickets:

• tickets.map references maps.id for the map a ticket is placed on.
• tickets.x / tickets.y carry the pixel position on the map.
• tickets.latitude / tickets.longitude carry the geo position (when available).

Project-level geographic coordinates (the project's location pin) live on projects.latitude and projects.longitude, not on maps.

See the Tickets domain for the ticket side and Projects domain for the project pin.

Lifecycle

• A maps or map_groups row is upserted when its source document is observed.
• A map can be archived (maps.archived is set to a DATETIME). Archived maps remain in the table; tickets and audits that reference them keep working.
• map_groups has no archived column — if a group is removed upstream, the row is dropped on the next reindex.
• audits_maps is rebuilt per audit document: on each observation of an audit, every existing audits_maps row for that audit is deleted and the current set is re-inserted from the audit's maps array.
• A reindex with type=map (or a full reindex) drops and rebuilds maps and map_groups. audits_maps is rebuilt as part of the audit reindex (type=audit).

Cross-table queries

The following queries combine maps with audits_maps, audits, or tickets. Per-table queries live on each leaf page.

Audits per map

SELECT
  m.id          AS map_id,
  m.name        AS map_name,
  COUNT(am.audit) AS audits_linked
FROM maps m
LEFT JOIN audits_maps am ON am.mapid = m.id
WHERE m.projectid = 'your_project_id'
  AND m.archived IS NULL
GROUP BY m.id, m.name
ORDER BY audits_linked DESC, m.name;

Tickets per map

SELECT
  m.id            AS map_id,
  m.name          AS map_name,
  COUNT(t.id)     AS tickets_on_map
FROM maps m
LEFT JOIN tickets t ON t.map = m.id
WHERE m.projectid = 'your_project_id'
  AND m.archived IS NULL
GROUP BY m.id, m.name
ORDER BY tickets_on_map DESC, m.name;

Maps grouped by their map group

SELECT
  mg.name      AS group_name,
  m.name       AS map_name,
  m.projectid
FROM maps m
LEFT JOIN map_groups mg ON mg.id = m.groupid
WHERE m.archived IS NULL
ORDER BY mg.name, m.name;

Audits and the maps they cover

SELECT
  a.id                                 AS audit_id,
  a.name                               AS audit_name,
  GROUP_CONCAT(m.name SEPARATOR ', ')  AS maps_covered
FROM audits a
LEFT JOIN audits_maps am ON am.audit = a.id
LEFT JOIN maps m         ON m.id     = am.mapid
WHERE a.projectid = 'your_project_id'
  AND a.archived IS NULL
GROUP BY a.id, a.name
ORDER BY a.creationdate DESC;