Images and maps¶

Including photos in report grids¶

As well as output of textual information into the grid columns, report output can include photos which the report grid control will automatically display as thumbnails with links to the full size image. Simply include a column containing the image file name (relative to the upload path on the warehouse) - you can include multiple images in the column by comma separating the file names. Handily the cache_occurrences_nonfunctional table includes a column called media which uses exactly that format. Also, you must declare the column with an attribute called img set to true for the column to behave as an image. Try adding the following to the <columns> section of your report:

<column name="images" display="Images" sql="onf.media" img="true" />

You also need to add a join to cache_occurrences_nonfunctional in the <query> element. Your report should now look like the following:

<?xml version="1.0" encoding="UTF-8"?>
<report title="Tutorial"
        description="Display some records for the report writing tutorial">
  <query website_filter_field="o.website_id" standard_params="occurrences">
    select #columns#
    from cache_occurrences_functional o
    join cache_occurrences_nonfunctional onf on onf.id=o.id
    join cache_samples_nonfunctional snf on snf.id=o.sample_Id
    join cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id
    #agreements_join#
    #joins#
    where #sharing_filter#
    #idlist#
  </query>
  <params>
    <param name="smpattrs" display="Sample attribute list" default=""
           description="Comma separated list of sample attribute IDs to include"
           datatype="smpattrs" />
    <param name="occattrs" display="Occurrence attribute list" default=""
           description="Comma separated list of occurrence attribute IDs to include"
           datatype="occattrs" />
  </params>
  <columns>
    <column name="id" sql="o.id" visible="false" datatype="integer"/>
    <column name="public_entered_sref" sql="snf.public_entered_sref"
            display="Grid Ref" datatype="text"/>
    <column name="preferred_taxon" sql="cttl.preferred_taxon"
            display="Species" datatype="text"/>
    <column name="default_common_name" sql="cttl.default_common_name"
            display="Common Name" datatype="text"/>
    <column name="date_start" sql="o.date_start" visible="false"/>
    <column name="date_end" sql="o.date_end" visible="false"/>
    <column name="date_type" sql="o.date_type" visible="false"/>
    <column name="date" display="Date" datatype="date"/>
    <column name="images" display="Images" sql="onf.media" img="true" />
  </columns>
</report>

Tip

When you add a new column to a report definition, it may not appear immediately in the reporting pages you’ve created in Drupal, because the code caches metadata about the report which only changes infrequently rather than request it fresh everytime you load the page. To ensure your page loads the new column clear the Indicia cache by visiting Configuration > Iform Admin Tasks on the admin menu then click the Clear Indicia cache button.

Outputting your records onto a map¶

If you use the Report Map prebuilt form (also in the Reporting category) you can output a map of the records in your report. So, set up a new prebuilt form for a Report Map, give it a title and add it to the primary links menu as before. Configure it to use the report you have been creating by setting the Report Name option under Report Settings. Now, go to the Base Map Layers section and select the Google Satellite layer. There are other options, in particular those under Report Map Settings but the defaults are fine for now. Save the page and view the results. You should see the map, but with a message output at the top:

The report's configuration does not output any mappable data

Ok, so we need to extend our report to include a column which includes mappable information. PostGIS (the spatial database extension for PostgreSQL which we use to store the locality of the records) stores grid references or other boundary and point data internally using a binary format which Indicia cannot use directly, so we need to pass the geometry binary data for each record through PostGIS’s st_astext function. Don’t worry if this does not make sense you can just copy this technique to make other reports mappable! So, add the following column definition to the report, noting the use of the mappable attribute to tell Indicia that it contains mapping data.

<column name="geom" visible="false" mappable="true" sql="st_astext(o.public_geom)" />

Save that and try reloading the Drupal Report Map page you created. You should see the dots now appear on the map. For reference, here is the entire report XML document you should have created:

<?xml version="1.0" encoding="UTF-8"?>
  <report title="Tutorial"
        description="Display some records for the report writing tutorial">
  <query website_filter_field="o.website_id" standard_params="occurrences">
    select #columns#
    from cache_occurrences_functional o
    join cache_occurrences_nonfunctional onf on onf.id=o.id
    join cache_samples_nonfunctional snf on snf.id=o.sample_Id
    join cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id
    #agreements_join#
    #joins#
    where #sharing_filter#
    #idlist#
  </query>
  <params>
    <param name="smpattrs" display="Sample attribute list" default=""
           description="Comma separated list of sample attribute IDs to include"
           datatype="smpattrs" />
    <param name="occattrs" display="Occurrence attribute list" default=""
           description="Comma separated list of occurrence attribute IDs to include"
           datatype="occattrs" />
  </params>
  <columns>
    <column name="id" sql="o.id" visible="false" datatype="integer"/>
    <column name="public_entered_sref" sql="snf.public_entered_sref"
            display="Grid Ref" datatype="text"/>
    <column name="preferred_taxon" sql="cttl.preferred_taxon"
            display="Species" datatype="text"/>
    <column name="default_common_name" sql="cttl.default_common_name"
            display="Common Name" datatype="text"/>
    <column name="date_start" sql="o.date_start" visible="false"/>
    <column name="date_end" sql="o.date_end" visible="false"/>
    <column name="date_type" sql="o.date_type" visible="false"/>
    <column name="date" display="Date" datatype="date"/>
    <column name="images" display="Images" sql="onf.media" img="true" />
    <column name="geom" visible="false" mappable="true" sql="st_astext(o.public_geom)" />
  </columns>
</report>

Tip

If you are coding using PHP directly instead of prebuilt forms in Drupal, you can achieve the same mapping output from your report data using the report_helper::report_map control.

Rather than have a separate report grid page and map page you can combine the two on the same page. Edit the page you created earlier which has the filter bar and report grid on it then change the following settings:

Base Map Layers - tick OpenStreetMap under Preset Base Layers.
User Interface - under Form Structure, add the following to the existing content inbetween the [standard params] and [reportgrid] controls:
[map]
@dataSource=<path to my report, excluding .xml suffix>
You can also add the option @rowId=id to the [reportgrid] control if you want to enable clicking on a grid row to highlight the correct point on the map.

The options you’ve covered for building this report file are not exhaustive but hopefully you now have a good taste for what can be achieved.

Images and maps¶

Including photos in report grids¶

Outputting your records onto a map¶

Table of Contents

Previous topic

Next topic

This Page