layer-stats

#!/usr/bin/env python
"""
Show per zoom statistics for some column (field) in a single layer.

Usage:
  layer-stats frequency <tileset> <layer> <column>...
              ([--zoom=<zoom>]... | [--minzoom=<min>] [--maxzoom=<max>])
              [--pghost=<host>] [--pgport=<port>] [--dbname=<db>]
              [--user=<user>] [--password=<password>] [--verbose]
  layer-stats toplength <tileset> <layer> <column> [--max-count=<c>]
              ([--zoom=<zoom>]... | [--minzoom=<min>] [--maxzoom=<max>])
              [--pghost=<host>] [--pgport=<port>] [--dbname=<db>]
              [--user=<user>] [--password=<password>] [--verbose]
  layer-stats variance <tileset> <layer> <column>
              ([--zoom=<zoom>]... | [--minzoom=<min>] [--maxzoom=<max>])
              [--pghost=<host>] [--pgport=<port>] [--dbname=<db>]
              [--user=<user>] [--password=<password>] [--verbose]
  layer-stats --help
  layer-stats --version

Methods:
  frequency             Shows how often each unique value occurs in a layer's column.
                        If more than one column is given, shows unique combinations.
  toplength             Shows the longest N values for a given layer's column.
  variance              Shows a few statistical metrics for a column's numeric value.

Options:
  <tileset>             Tileset definition yaml file
  <column>              Columns to analyze, could be more than one.
  <layer>               Which layer to examine
  -z --zoom=<zoom>      Limit testing to a specific zoom. If set, ignores min/max.
  -m --minzoom=<min>    Test tiles in zooms more or equal to this value  [default: 0]
  -n --maxzoom=<max>    Test tiles in zooms less or equal to this value  [default: 14]
  --max-count=<max>     For toplength, how many longest values to show [default: 30]
  -v --verbose          Print additional debugging information
  --help                Show this screen.
  --version             Show version.

PostgreSQL Options:
  -h --pghost=<host>    Postgres hostname. By default uses PGHOST env or "localhost" if not set.
  -P --pgport=<port>    Postgres port. By default uses PGPORT env or "5432" if not set.
  -d --dbname=<db>      Postgres db name. By default uses PGDATABASE env or "openmaptiles" if not set.
  -U --user=<user>      Postgres user. By default uses PGUSER env or "openmaptiles" if not set.
  --password=<password> Postgres password. By default uses PGPASSWORD env or "openmaptiles" if not set.

These legacy environment variables should not be used, but they are still supported:
  POSTGRES_HOST, POSTGRES_PORT, POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD
"""
import asyncio
import re
from collections import defaultdict

import asyncpg
# noinspection PyProtectedMember
from docopt import docopt, DocoptExit
from tabulate import tabulate

import openmaptiles
from openmaptiles.pgutils import parse_pg_args, get_postgis_version, PgWarnings, \
    print_query_error
from openmaptiles.sqltomvt import MvtGenerator
from openmaptiles.tileset import Tileset


async def main(args):
    tileset_path = args['<tileset>']
    pghost, pgport, dbname, user, password = parse_pg_args(args)
    layer = args.layer
    columns = args.column
    max_count = args['--max-count']
    for c in columns:
        if not re.match(r'^[a-z_][a-z0-9_]*$', c):
            raise DocoptExit(f"Column '{c}' is invalid")
    zooms = [int(v) for v in args['--zoom']]
    if not zooms:
        zooms = list(range(int(args['--minzoom']), int(args['--maxzoom']) + 1))
    verbose = args['--verbose']

    tileset = Tileset.parse(tileset_path)
    conn = await asyncpg.connect(
        database=dbname, host=pghost, port=pgport, user=user, password=password,
    )
    pg_warnings = PgWarnings(conn, delay_printing=True)
    mvt = MvtGenerator(
        tileset,
        zoom=None, x=None, y=None,
        layer_ids=[layer],
        postgis_ver=await get_postgis_version(conn),
    )

    layer_def = tileset.layers_by_id[layer]
    if layer_def.has_localized_names:
        # Alter stored query to hide localized names
        layer_def.definition['layer']['datasource']['query'] = \
            layer_def.raw_query.format(name_languages='NULL as _hidden_names_')
    raw_query = mvt.layer_to_query(layer_def, to_mvt_geometry=False)
    query = mvt.substitute_sql(raw_query, 'CAST($1 as int)',
                               mvt.bbox(0, 0, 0)).strip()
    if len(columns) > 1 and not args.frequency:
        raise DocoptExit(f"The 'toplength' and 'variance' methods require "
                         f"a single column, but {len(columns)} was given")
    if args.frequency:
        method = "value occurrence"
        fields = ','.join(columns)
        query = f"""
SELECT {fields}, count(*) AS _result
FROM {query}
GROUP BY {fields}
ORDER BY {fields};
            """.strip()
    elif args.toplength:
        method = "longest field values"
        query = f"""
SELECT DISTINCT {columns[0]}, length( {columns[0]} ) AS _result
FROM {query}
WHERE length( {columns[0]} ) > 0
ORDER BY length( {columns[0]} ) DESC NULLS LAST
LIMIT {max_count};
            """.strip()
    elif args.variance:
        method = "field value variance"
        query = f"""
SELECT
  'z' || $1                as zoom,
  COUNT( {columns[0]} )    as count,
  MIN( {columns[0]} )      as min,
  MAX( {columns[0]} )      as max,
  AVG( {columns[0]} )      as avg,
  STDDEV( {columns[0]} )   as stddev,
  VARIANCE( {columns[0]} ) as variance
FROM {query};
            """.strip()
    else:
        raise DocoptExit("Unknown method used")

    if verbose:
        print(f"\n======= Querying layer {layer} (#{layer_def.index}) =======\n"
              f"{query}")

    if args.variance:
        results = []
    else:
        results = defaultdict(dict)
    for zoom in zooms:
        try:
            query_result = await conn.fetch(query, zoom)
        except asyncpg.PostgresError as err:
            print_query_error(f"ERROR in {layer} layer",
                              err, pg_warnings, verbose, query)
            return
        for row in query_result:
            if args.variance:
                if row['count']:
                    results.append(dict(row))
            else:
                key = tuple(row[v] for v in columns)
                results[key][zoom] = row['_result']
        if pg_warnings.messages:
            print(f"======= Layer {layer} has warnings at zoom {zoom} =======")
            pg_warnings.print()

    print(f"======= Analyzing {method} in layer '{layer}' "
          f"for field{'s' if len(columns) > 1 else ''} [{', '.join(columns)}] =======")
    if results:
        if args.variance:
            values = results
            headers = "keys"
        else:
            existing_zooms = set()
            values = []
            for key, zoom_dict in results.items():
                vals = {v: fmt_val(key[i]) for i, v in enumerate(columns)}
                for z, v in zoom_dict.items():
                    vals[f"z{z}"] = v
                    existing_zooms.add(z)
                values.append(vals)
            headers = {v: v for v in
                       (columns + [f"z{z}" for z in sorted(existing_zooms)])}
        print(tabulate(values, headers=headers))
    else:
        print("No results were found")


def fmt_val(val):
    return '<null>' if val is None else '<blank>' if val == '' else val


if __name__ == '__main__':
    asyncio.run(main(docopt(__doc__, version=openmaptiles.__version__)))