# Generating ISO 19115 Records from PostGIS Table Comments

Adopt a structured comment convention — a short free-text abstract followed by key: value lines — so a table comment carries the fields an ISO record needs, and generate the record from the comment rather than from a spreadsheet that has to be kept in step with the database.

Table comments are the only metadata store that lives in the same place as the data, moves with it through a dump and restore, and is editable by the person who changed the schema. Their weakness is that they are unstructured by default, so a generator can either treat the whole comment as an abstract and produce records with nothing else, or impose a convention. The convention is worth the small imposition. This guide sits under ISO 19115 metadata template generation, within Automated Metadata Generation & Schema Mapping.

# Automated Python Implementation

The parser accepts a comment whose first paragraph is prose and whose remaining lines are key: value pairs, and is deliberately tolerant about everything except the keys it recognises.

#!/usr/bin/env python3
"""Generate ISO 19115-3 records from structured PostGIS table comments."""
import re

import psycopg

KEY_LINE = re.compile(r"^\s*([a-z][a-z0-9_]*)\s*:\s*(.+?)\s*$", re.I)

RECOGNISED = {
    "title", "contact", "contact_role", "licence", "license",
    "update_frequency", "keywords", "lineage", "restrictions",
}


def parse_comment(comment):
    """Split a table comment into an abstract and typed key-value fields."""
    if not comment:
        return {"abstract": None, "fields": {}, "unrecognised": []}

    lines = comment.strip().splitlines()
    abstract_lines, fields, unrecognised = [], {}, []
    in_fields = False

    for line in lines:
        match = KEY_LINE.match(line)
        if match and match.group(1).lower() in RECOGNISED:
            in_fields = True
            key = match.group(1).lower().replace("license", "licence")
            fields[key] = match.group(2)
        elif match and in_fields:
            unrecognised.append(match.group(1))
        elif not in_fields:
            abstract_lines.append(line)

    return {
        "abstract": " ".join(l.strip() for l in abstract_lines).strip() or None,
        "fields": fields,
        "unrecognised": unrecognised,
    }


CATALOGUE_SQL = """
SELECT c.relname AS table_name,
       obj_description(c.oid) AS table_comment,
       g.srid,
       g.type AS geometry_type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN geometry_columns g
  ON g.f_table_schema = n.nspname AND g.f_table_name = c.relname
WHERE n.nspname = %s
ORDER BY c.relname
"""


def collect(dsn, schema="public"):
    """One record per spatial table, merging comment fields with database facts."""
    records = []
    with psycopg.connect(dsn) as con:
        for row in con.execute(CATALOGUE_SQL, (schema,)):
            name, comment, srid, geom_type = row
            parsed = parse_comment(comment)
            extent = con.execute(
                f'SELECT ST_Extent(geom)::text FROM "{schema}"."{name}"'
            ).fetchone()[0]
            records.append({
                "table": name,
                "abstract": parsed["abstract"],
                "fields": parsed["fields"],
                "unrecognised_keys": parsed["unrecognised"],
                "srid": srid,
                "geometry_type": geom_type,
                "extent": extent,
                "completeness": completeness(parsed),
            })
    return records


def completeness(parsed):
    """Which ISO-mandatory inputs the comment failed to supply."""
    missing = []
    if not parsed["abstract"]:
        missing.append("abstract")
    for key in ("title", "contact"):
        if key not in parsed["fields"]:
            missing.append(key)
    return {"missing": missing, "score": 1 - len(missing) / 3}

The unrecognised_keys list is the part that keeps the convention working. Somebody will write owner: where the convention says contact:, and a parser that silently ignores it produces a record missing a contact while the author believes they supplied one. Reporting the unrecognised key turns a silent omission into a one-word correction.

# Validation and Pipeline Integration

Two assertions cover the parser, and a third covers the convention’s adoption.

def test_abstract_and_fields_are_separated():
    comment = "Parcel boundaries for the district.\ntitle: Parcels\ncontact: gis@example.gov"
    parsed = parse_comment(comment)
    assert parsed["abstract"] == "Parcel boundaries for the district."
    assert parsed["fields"]["title"] == "Parcels"


def test_unrecognised_key_is_reported():
    parsed = parse_comment("Roads.\nowner: transport team")
    assert "owner" in parsed["unrecognised"]
    assert "contact" not in parsed["fields"]


def test_completeness_flags_a_bare_comment():
    parsed = parse_comment("Some roads.")
    assert set(completeness(parsed)["missing"]) == {"title", "contact"}

Run the collector on a schedule and publish the completeness scores rather than gating on them. A missing comment is a documentation gap, not a build failure, and failing a deployment because somebody added a table without an abstract produces a team that adds tables with the word “table” as the abstract.

# Making the Convention Stick

A comment convention nobody follows is worse than no convention, because the generator will produce confidently incomplete records rather than obviously empty ones. Three things make adoption likely.

Put the template where the schema is written. A migration template containing the comment skeleton, with the keys pre-filled and empty, means the convention is followed by default rather than remembered. Most tables acquire their comment at creation or never.

Report by owner, not by table. A list of forty undocumented tables is nobody’s problem; five tables owned by a named team is that team’s problem. The mapping from table to owner usually exists in a schema naming rule or a deployment manifest, and using it changes the response rate dramatically.

Show what the comment produced. A generated record next to its source comment is the most persuasive documentation the convention can have, because it makes the payoff concrete: two more lines in a comment yields a catalogue entry somebody can find. Abstract exhortations about metadata quality do not.

The convention should also stay small. Four recognised keys that are always filled beat twelve that are filled a third of the time, and the marginal value of the fifth key is almost always lower than the cost of the convention being seen as bureaucratic. Start with title, contact and licence, and add only when a specific consumer needs something specific.

# Comments on Views and Materialised Views

Spatial schemas rarely expose base tables directly. Most publishing happens through views, and views have comments of their own that behave differently in three ways worth knowing before the generator meets one.

A view’s comment does not inherit from its base tables, so a well-documented table exposed through an undocumented view produces an undocumented published dataset. The generator sees only what the consumer sees, which is correct behaviour and frequently surprising.

geometry_columns reports views whose geometry column is unambiguous and silently omits those where it cannot resolve one — typically a view selecting from two spatial tables. Such a view is invisible to the collector, so a completeness report built from geometry_columns alone will not mention it at all. Enumerating views separately and reconciling the two lists is the only way to notice.

Extent computation over a view executes the view. On a materialised view this is cheap; on a complex view over large tables it can take minutes, which turns a metadata run into a load event. Reading the extent from the underlying table where the view is a simple filter, and accepting a slightly generous extent, is usually the right trade.

# Long-Term Compliance Best Practices

• Keep the comment authoritative and the record derived. Editing a generated record rather than the comment means the next run silently reverts the fix.
• Version the convention in the same repository as the migrations. A key added to the parser and not to the template will be filled by nobody.
• Never fail a deployment on comment completeness. The predictable response is a comment that satisfies the check and informs nobody.
• Treat an unrecognised key as a defect in the convention, not the author. Three people writing owner: means the convention chose the wrong word.
• Re-run after every migration. A schema change that alters a geometry column changes the record, and the comment usually will not mention it.

# Related

• ISO 19115 Metadata Template Generation — assembling the record this generator fills
• Filling ISO 19115 Contact and Responsibility Blocks — turning a contact string into a conformant responsibility element
• Automating Metadata Extraction from PostGIS Tables — the broader extraction pass across a schema
• Mapping DCAT-AP Fields from PostGIS Columns — the same source, a different target profile