Background: What are markers?

Timelapse Counter controls allow you to add markers over the image: a cirle that indicates the locaton of whatevery it is you are counting. Internally, markers are saved as x,y coordinates that indicate where that marker is located relative to the image. For example, (“0.25, 0.5”) indicates a marker 1/4 from the left of the image and halfway-down from the top. If there are multiple markers, they would appear something like (“0.25, 0.5”, “0.7,0.3”). The coordinates are saved in the database within a MarkersTable. Each row represents an image containing one or more markers. The table columns include an Id identifying the image (corresponding to the Id in the main DataTable), and one or more datalabels representing each Counter.

Why would I want to access Markers data?

Most people don’t need to access markers data. However, there are rare cases when people do need to know the spatial location of something in an image, where markers can serve that purpose.

How can I get Markers data?

Timelapse does not (currently) have a facility to export markers data. But there are workarounds, where you can get that information directly from the timelapse database (.ddb) file using either an SQLite database browser such as DB Browser for SQLite, or a programming language such as R.

Using R

1. The Timelapse database quide includes a section at its end that explains how you can open a database within R. You can then use R to submit an SQLite query to the database where the result is returned as an R data structure that you can manipulate.
2. The simplest query looks like this:
   SELECT 
   d.File,
   d.RelativePath,
   m.*
   FROM MarkersTable m
   INNER JOIN DataTable d ON m.Id = d.Id;
3. This will return a table where each row will represent an image, where the column names will be the File / RelativePath / <Counter1> / <Counter2> etc. The counter columns will be the datalabels of the counter controls, and will contain the marker coordinates associated with each counter.
4. If you want to include other relevant columns, you could just add them to the select list. For example, if you had a data field whose datalabel was ‘species’, you could include that by adding d.species to the above. However, you should NOT add any counter fields as that would result in duplicate names.

Using DB Browser for SQLite to get a CSV file

SQLite browsers are simple ways to read in a database file and submit queries to them. We recommend DB Browser for SQLite.

1. Download and install DB Browser for SQLite, using the link https://timelapse.ucalgary.ca/utilities/
2. Use that app to open your Timelapse ddb file ( File | Open Database )
3. Click the Execute SQL tab
4. Paste in this query, and then select the Play button
   CREATE TEMP VIEW ExportView AS
     SELECT
   d.File,
   d.RelativePath,
   m.*
   FROM MarkersTable m
   INNER JOIN DataTable d ON m.Id = d.Id;
5. Select File | Export | Table as CSV files, and then select temp.ExportView to export the results as a csv file, which can then be opened and manipulated within Excel.
6. See step 4 in the R description above, as it applies here as well.