Common data tables

Deprecated, please, check https://github.com/CartoDB/data-services/tree/master/ instead

New 'common data' tables will be 'stored' in 'http://common-data.cartodb.com'.

Format standards

• Table description must end with a point. Geometries for building footprints.
• tab_name should be self descriptive. melbourne_footprints instead of buildings

Questions?

Why reported table size and number of rows is wrong?

We use estimations based on pg_class.reltuples and pg_relation_size so we don't have to do query scans, it is way faster however the results are not always consistent and these numbers are as of the last VACUUM or ANALYZE on the table.

How to fix it

It's not possible to create a function that takes care of this since you cannot VACUUM cannot be executed from a function. So if you need to fix the number of rows or the table size you have to run:

VACUUM FULL table_name

CartoDB editor should use a POST request when doing a VACUUM query but when in doubt use API V1 just in case we are caching the request/query because we are not able to invalidate this kind of queries.

Script to run VACUUM FULL in all common data tables/datasets

#!/bin/sh
API_KEY=COMMON_DATA_USER_API_KEY
CSV_FILENAME=/tmp/cdb_common_datasets.csv
curl -s "https://common-data.cartodb.com/api/v1/sql?api_key=${API_KEY}&q=select%20tabname%20from%20meta_dataset&format=csv" > ${CSV_FILENAME}

CSV_LINES_NUMBER=`cat ${CSV_FILENAME} | wc -l`
DATASETS_NUMBER=`echo "${CSV_LINES_NUMBER} - 1" | bc`

for tablename in `tail -n ${DATASETS_NUMBER} ${CSV_FILENAME}`;
do
    DATASET=`echo ${tablename} | sed 's/\r//'`
    QUERY="VACUUM%20FULL%20${DATASET}"
    VACUUM_URL="https://common-data.cartodb.com/api/v1/sql?api_key=${API_KEY}&q=${QUERY}"

    RESPONSE=`curl -s ${VACUUM_URL}`
    echo "${DATASET}: ${RESPONSE}"
done

Is there any check about categories or table names in meta_dataset table?

There is a constraint in meta_category_id so it's not possible to add an invalid category.

CONSTRAINT meta_dataset_meta_category_id_fkey FOREIGN KEY (meta_category_id)
      REFERENCES public.meta_category (cartodb_id)

There is NO check about the table name right now. WIP.