DEV Community: Prashant 🗽

Making your content AI friendly in 2026

Prashant 🗽 — Mon, 19 Jan 2026 15:41:02 +0000

Your documentation has a new primary audience. While you've been optimizing for human readers, AI coding assistants have quietly become the biggest consumers of your content. Claude Desktop, Cursor, GitHub Copilot, and a dozen other tools are reading your docs, parsing your examples, and generating code based on what they find.

I've written about how this changes developer relations and how I use AI tools in my own marketing workflow. This post is the technical playbook. Everything you need to do to make your website, documentation, and blog content fully consumable by AI systems.

tl;dr A Priority checklist for making your content AI-friendly

High impact, proven techniques:

Write complete, runnable code examples (most important)
Show full request/response pairs for APIs
Document every error code with resolution steps
Publish OpenAPI specification at conventional URLs
Use semantic HTML with proper heading hierarchy
Add JSON-LD schema to all pages
Include language tags on all code blocks

Medium impact:

Configure robots.txt for AI crawlers (training vs. retrieval)
Maintain comprehensive sitemap with priorities
Implement IndexNow for content freshness
Provide RSS/Atom feeds with full content
Serve Markdown versions of documentation pages
Add copy buttons to code examples
Write scenario-based documentation

Emerging/speculative:

Create llms.txt with site guidance (adoption unproven)
Build MCP server for direct AI integration (requires security review)
Test documentation regularly with AI assistants

The first tier is table stakes. The second tier provides meaningful advantages. The third tier is worth experimenting with but has less proven ROI.

Read on for more descriptions of each item and why they matter.

Understanding GEO and AEO

Before diving into implementation, it helps to understand the terminology. Two concepts have emerged to describe optimization for AI systems:

Generative Engine Optimization (GEO) is the practice of optimizing content so that large language models like ChatGPT, Claude, Gemini, and Perplexity cite it as a trusted source in their responses. Instead of competing for blue links on a search results page, you're influencing what AI models generate when users ask questions.

Answer Engine Optimization (AEO) focuses specifically on AI-powered search features like Google's AI Overviews, Bing Copilot, and Perplexity's instant answers. These are answer-first surfaces that users increasingly rely on instead of clicking through to websites.

In practice, GEO and AEO overlap significantly. Both are built on the idea that AI engines need optimized content. The strategies in this guide serve both purposes.

The stakes are real: Ahrefs found that AI overviews reduced click-through rates for top-ranking Google content by 34.5% in one year. Meanwhile, AI referrals to top websites surged 357% year-over-year. Gartner predicts a 25% drop in traditional search engine volume by 2026. Being cited by AI systems is becoming as important as ranking in search results.

How AI systems consume your content

Different AI systems consume documentation in different ways. Understanding this helps prioritize your optimization efforts:

RAG (Retrieval-Augmented Generation): Tools like Perplexity and ChatGPT's browsing mode search the web, retrieve relevant pages, and use that content to generate responses. They rely on traditional crawling and indexing.

Web search: AI coding assistants like Cursor and GitHub Copilot can search documentation sites directly. They parse HTML, extract content, and use it for context.

MCP (Model Context Protocol): Some AI assistants can connect directly to your API through MCP servers, bypassing documentation entirely. This is the most direct integration.

Direct context loading: Tools like Claude Desktop and Cursor can load files directly into their context window. This is where llms-full.txt and markdown versions of documentation become valuable.

The techniques in this guide optimize for all of these consumption patterns.

The llms.txt standard

The llms.txt file is a proposed standard for helping AI systems understand your site. Created by Jeremy Howard of Answer.AI in 2024, it provides explicit guidance on how to navigate and prioritize your content.

Important caveat: As of early 2026, no major AI provider (OpenAI, Anthropic, Google) has confirmed they actually read llms.txt files during crawling. Server log analysis shows AI crawlers are not requesting these files. The standard remains aspirational. However, Anthropic has published an llms.txt on their own site, suggesting openness to the idea, and the format is useful for AI coding assistants that fetch documentation on demand.

Create a file at your domain root called llms.txt. The format uses Markdown with a specific structure:

# Your Product Name

> Brief summary of your product. This blockquote should contain key information
> that helps AI systems understand what your product does and who it's for.

Your Product is a [category] that helps developers [value prop]. This section
can contain additional context paragraphs.

## Documentation
- [Getting Started](https://example.com/docs/getting-started): Complete setup instructions with runnable examples
- [API Reference](https://example.com/docs/api-reference): Full API documentation with request/response pairs
- [Examples](https://example.com/docs/examples): Working code examples for common use cases
- [Changelog](https://example.com/docs/changelog): Recent changes and version history

## Guides
- [Authentication](https://example.com/docs/auth): API key and OAuth setup
- [Error Handling](https://example.com/docs/errors): Complete error code reference

## Optional
- [Blog](https://example.com/blog): Technical articles and tutorials
- [Community](https://example.com/community): Forums and discussions

The specification requires:

An H1 with the project name (the only required element)
A blockquote summary with key information
Zero or more body paragraphs (no headings)
H2 sections organizing links to documentation
Links formatted as [Title](URL): Description
An "Optional" section for content that can be skipped in shorter contexts

You can also create an llms-full.txt that includes complete documentation in Markdown. This is useful for AI coding assistants that can load full context:

# Your Product - Complete Documentation

> Complete documentation for Your Product, a [category] for developers.

## Getting Started

[Full markdown content of getting started guide]

## Authentication

[Full markdown content of authentication docs]

## API Reference

[Full markdown content of API reference]

Keep llms-full.txt to a reasonable size. One analysis found that Turborepo's llms.txt consumed 116,000 tokens, making it essentially unusable. Aim for under 100,000 tokens when possible.

Token optimization for AI consumption

AI systems have limited context windows. Even with models supporting 100k+ tokens, efficiency matters. Large context windows increase latency and cost.

Serve Markdown alongside HTML

Many documentation platforms (Mintlify, GitBook, Docusaurus) now serve .md versions of pages alongside HTML. This is often more valuable than llms.txt because it provides clean, parseable content for each page.

If your docs framework supports it, expose markdown versions at predictable URLs:

https://docs.company.com/getting-started        -> HTML for humans
https://docs.company.com/getting-started.md     -> Markdown for AI

For Next.js or custom documentation sites, add an API route:

// app/docs/[...slug]/markdown/route.ts
import { getDocContent } from '@/lib/docs';

export async function GET(
  request: Request,
  { params }: { params: { slug: string[] } }
) {
  const content = await getDocContent(params.slug.join('/'));

  if (!content) {
    return new Response('Not found', { status: 404 });
  }

  return new Response(content.markdown, {
    headers: {
      'Content-Type': 'text/markdown; charset=utf-8',
    },
  });
}

Markdown vs HTML token efficiency

Markdown can reduce token usage by up to 10x compared to HTML. A page that consumes 5,000 tokens as HTML might only need 500 tokens as Markdown.

<!-- HTML: ~150 tokens -->
<section class="content-section">
  <h2 id="authentication" class="section-heading">
    <a href="#authentication">Authentication</a>
  </h2>
  <p class="section-content">
    All API requests require authentication using an API key.
  </p>
</section>

<!-- Markdown: ~30 tokens -->
## Authentication

All API requests require authentication using an API key.

Content chunking for large documentation

For documentation sites with hundreds of pages, create tiered llms.txt files:

llms.txt - Overview with links to sections (under 10k tokens)
llms-quickstart.txt - Just getting started content (under 20k tokens)
llms-api.txt - API reference only (under 50k tokens)
llms-full.txt - Everything (keep under 100k tokens if possible)

This lets AI systems choose the appropriate level of detail for the task at hand.

Semantic HTML structure

AI systems parse HTML to understand content structure. Clean semantic HTML makes this parsing reliable.

Document structure

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Page Title - Site Name</title>
  <meta name="description" content="Clear description for both search engines and AI">
</head>
<body>
  <header role="banner">
    <nav role="navigation" aria-label="Main navigation">
      <!-- Navigation -->
    </nav>
  </header>

  <main role="main">
    <article>
      <header>
        <h1>Article Title</h1>
        <p class="excerpt">Brief summary of what this page covers</p>
      </header>

      <section aria-labelledby="section-1">
        <h2 id="section-1">Section Heading</h2>
        <!-- Content -->
      </section>

      <section aria-labelledby="section-2">
        <h2 id="section-2">Another Section</h2>
        <!-- Content -->
      </section>
    </article>

    <aside role="complementary" aria-label="Related content">
      <!-- Related links, table of contents -->
    </aside>
  </main>

  <footer role="contentinfo">
    <!-- Footer -->
  </footer>
</body>
</html>

Heading hierarchy

AI systems use heading structure to understand document organization. Never skip levels:

<!-- Good -->
<h1>API Reference</h1>
  <h2>Authentication</h2>
    <h3>API Keys</h3>
    <h3>OAuth 2.0</h3>
  <h2>Endpoints</h2>
    <h3>Users</h3>
    <h3>Projects</h3>

<!-- Bad - skipped h2 -->
<h1>API Reference</h1>
  <h3>Authentication</h3>
  <h3>Endpoints</h3>

Anchor links on headings

Make every heading linkable. This lets AI systems reference specific sections:

<h2 id="authentication">
  <a href="#authentication" aria-label="Link to this section">
    Authentication
  </a>
</h2>

JSON-LD structured data

JSON-LD tells AI systems exactly what type of content they're looking at. This is different from helping them parse the content itself. It's metadata about the content.

Article schema for blog posts

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "Article",
  "headline": "Article Title",
  "description": "Clear description of what this article covers",
  "datePublished": "2026-01-12T08:00:00Z",
  "dateModified": "2026-01-12T08:00:00Z",
  "author": {
    "@type": "Person",
    "name": "Author Name",
    "url": "https://example.com/about"
  },
  "publisher": {
    "@type": "Organization",
    "name": "Company Name",
    "logo": {
      "@type": "ImageObject",
      "url": "https://example.com/logo.png"
    }
  },
  "mainEntityOfPage": {
    "@type": "WebPage",
    "@id": "https://example.com/blog/article-slug"
  }
}
</script>

TechArticle schema for documentation

For technical documentation like getting started guides, use TechArticle instead of Article:

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "TechArticle",
  "headline": "Getting Started with Authentication",
  "description": "Complete guide to implementing authentication",
  "proficiencyLevel": "Beginner",
  "dependencies": "Node.js 18+, npm",
  "datePublished": "2026-01-12T08:00:00Z",
  "author": {
    "@type": "Organization",
    "name": "Company Name"
  }
}
</script>

HowTo schema for tutorials

Tutorials benefit from HowTo schema that breaks down the steps:

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "HowTo",
  "name": "How to Set Up API Authentication",
  "description": "Step-by-step guide to implementing API key authentication",
  "totalTime": "PT15M",
  "step": [
    {
      "@type": "HowToStep",
      "name": "Install the SDK",
      "text": "Run npm install @company/sdk",
      "url": "https://example.com/docs/setup#install"
    },
    {
      "@type": "HowToStep",
      "name": "Configure API Keys",
      "text": "Add your API key to the configuration file",
      "url": "https://example.com/docs/setup#configure"
    },
    {
      "@type": "HowToStep",
      "name": "Make Your First Request",
      "text": "Call the API using the authenticated client",
      "url": "https://example.com/docs/setup#first-request"
    }
  ]
}
</script>

FAQPage schema

FAQ schema helps AI systems understand question-answer pairs:

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "FAQPage",
  "mainEntity": [
    {
      "@type": "Question",
      "name": "What authentication methods do you support?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "We support API keys and OAuth 2.0. API keys are recommended for server-side applications. OAuth is required for applications that access user data."
      }
    },
    {
      "@type": "Question",
      "name": "What are the rate limits?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Free tier: 100 requests per minute. Pro tier: 1,000 requests per minute. Enterprise: custom limits."
      }
    }
  ]
}
</script>

SoftwareApplication schema for your product

Help AI systems understand your product itself:

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "SoftwareApplication",
  "name": "Product Name",
  "applicationCategory": "DeveloperApplication",
  "operatingSystem": "Cross-platform",
  "offers": {
    "@type": "Offer",
    "price": "0",
    "priceCurrency": "USD"
  },
  "aggregateRating": {
    "@type": "AggregateRating",
    "ratingValue": "4.8",
    "ratingCount": "1250"
  }
}
</script>

Code blocks

Code blocks are where AI assistance happens. Make them excellent. If you're scaling content production, ensure your content production process includes these standards.

Language identification

Always specify the language. This enables syntax highlighting for humans and accurate parsing for AI:

<pre><code class="language-typescript">
const client = new Client({
  apiKey: process.env.API_KEY
});

const response = await client.users.list();
</code></pre>

In Markdown:

(three backticks)markdown
const client = new Client({
  apiKey: process.env.API_KEY
});

const response = await client.users.list();
(three backticks)
[Sorry, the Markdown renderer in Dev.to is incomplete]

Copy buttons

Copy buttons reduce friction. When a developer (or AI) finds the right code, copying should be instant:

function CodeBlock({ code, language }: { code: string; language: string }) {
  const [copied, setCopied] = useState(false);

  const copyToClipboard = async () => {
    await navigator.clipboard.writeText(code);
    setCopied(true);
    setTimeout(() => setCopied(false), 2000);
  };

  return (
    <div className="relative">
      <button
        onClick={copyToClipboard}
        className="absolute top-2 right-2"
        aria-label="Copy code to clipboard"
      >
        {copied ? 'Copied!' : 'Copy'}
      </button>
      <pre>
        <code className={`language-${language}`}>{code}</code>
      </pre>
    </div>
  );
}

Complete examples

This is the most important principle. AI systems need complete, runnable code. They cannot fill in the gaps the way experienced developers can.

Bad (incomplete):

// Initialize the client
const client = new Client(/* config */);

// Make a request
const result = await client.query(/* params */);

Good (complete):

import { Client } from '@company/sdk';

// Initialize with your API key
const client = new Client({
  apiKey: process.env.COMPANY_API_KEY,
  baseUrl: 'https://api.company.com/v1'
});

// Query all users with pagination
async function getAllUsers() {
  const users = [];
  let cursor: string | undefined;

  do {
    const response = await client.users.list({
      limit: 100,
      cursor
    });
    users.push(...response.data);
    cursor = response.nextCursor;
  } while (cursor);

  return users;
}

// Usage
const users = await getAllUsers();
console.log(`Found ${users.length} users`);

Request and response pairs

For API documentation, always show the complete request AND response:

### Create a user

**Request:**

(three backticks)bash
curl -X POST https://api.company.com/v1/users \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "email": "user@example.com",
    "name": "Jane Developer"
  }'
(three backticks)

**Response (201 Created):**

(three backticks)json
{
  "id": "usr_abc123",
  "email": "user@example.com",
  "name": "Jane Developer",
  "created_at": "2026-01-12T08:00:00Z",
  "updated_at": "2026-01-12T08:00:00Z"
}
(three backticks)

**Error Response (400 Bad Request):**

(three backticks)json
{
  "error": {
    "code": "invalid_email",
    "message": "The email address format is invalid",
    "details": {
      "field": "email",
      "value": "not-an-email"
    }
  }
}
(three backticks)

Exhaustive error documentation

AI systems hallucinate when they encounter gaps in documentation. Error documentation is often the biggest gap.

Document every error code your API can return:

## Error Codes

| Code | HTTP Status | Description | Resolution |
|------|-------------|-------------|------------|
| `invalid_api_key` | 401 | API key is missing or invalid | Check your API key in the dashboard |
| `rate_limited` | 429 | Too many requests | Wait and retry with exponential backoff |
| `invalid_request` | 400 | Request body is malformed | Validate JSON syntax |
| `resource_not_found` | 404 | Requested resource doesn't exist | Check the resource ID |
| `permission_denied` | 403 | API key lacks required permissions | Update API key scopes in dashboard |
| `internal_error` | 500 | Server-side error | Retry or contact support |

### Error Response Format

All errors return a consistent JSON structure:

(three backticks)json
{
  "error": {
    "code": "error_code",
    "message": "Human-readable error description",
    "details": {
      // Additional context when available
    },
    "request_id": "req_xyz789"
  }
}
(three backticks)

Meta tags

Standard meta tags remain important for both search engines and AI systems.

<head>
  <!-- Basic meta -->
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Page Title - Site Name</title>
  <meta name="description" content="Clear, specific description under 160 characters">

  <!-- Canonical URL -->
  <link rel="canonical" href="https://example.com/current-page">

  <!-- Open Graph for social and some AI systems -->
  <meta property="og:title" content="Page Title">
  <meta property="og:description" content="Description for social sharing">
  <meta property="og:type" content="article">
  <meta property="og:url" content="https://example.com/current-page">
  <meta property="og:image" content="https://example.com/og-image.png">

  <!-- Twitter/X Cards -->
  <meta name="twitter:card" content="summary_large_image">
  <meta name="twitter:title" content="Page Title">
  <meta name="twitter:description" content="Description for Twitter">

  <!-- Article-specific meta -->
  <meta property="article:published_time" content="2026-01-12T08:00:00Z">
  <meta property="article:modified_time" content="2026-01-12T08:00:00Z">
  <meta property="article:author" content="Author Name">

  <!-- RSS/Atom feed -->
  <link rel="alternate" type="application/rss+xml" title="RSS Feed" href="/rss.xml">
  <link rel="alternate" type="application/atom+xml" title="Atom Feed" href="/atom.xml">
</head>

robots.txt for AI crawlers

The robots.txt file controls which crawlers can access your content. Understanding the AI crawler landscape is critical because there are two distinct types:

Training crawlers scrape content to train or fine-tune AI models:

GPTBot (OpenAI)
ClaudeBot (Anthropic)
Google-Extended (Google/Gemini)
Meta-ExternalAgent (Meta/Llama)
Bytespider (ByteDance)
CCBot (Common Crawl)

Retrieval crawlers fetch content in real-time to answer user queries:

OAI-SearchBot (OpenAI search indexing)
ChatGPT-User (ChatGPT browsing)
Claude-User (Claude browsing)
PerplexityBot (Perplexity indexing)
Perplexity-User (Perplexity browsing)

Many sites want to be cited in AI responses but don't want their content used for model training. Here's how to configure that:

# robots.txt

User-agent: *
Allow: /

Sitemap: https://example.com/sitemap.xml

# ===========================================
# ALLOW: AI search and retrieval crawlers
# These fetch content to answer user queries
# ===========================================

User-agent: OAI-SearchBot
Allow: /

User-agent: ChatGPT-User
Allow: /

User-agent: Claude-User
Allow: /

User-agent: PerplexityBot
Allow: /

User-agent: Perplexity-User
Allow: /

# ===========================================
# BLOCK: AI training crawlers
# These scrape content for model training
# ===========================================

User-agent: GPTBot
Disallow: /

User-agent: ClaudeBot
Disallow: /

User-agent: Google-Extended
Disallow: /

User-agent: Meta-ExternalAgent
Disallow: /

User-agent: Bytespider
Disallow: /

User-agent: CCBot
Disallow: /

# ===========================================
# Additional Anthropic crawlers
# ===========================================

User-agent: anthropic-ai
Disallow: /

User-agent: Claude-Web
Disallow: /

# ===========================================
# Block admin and private areas from all
# ===========================================

User-agent: *
Disallow: /admin/
Disallow: /api/internal/

Important caveats:

Compliance varies. ChatGPT-User and some retrieval crawlers don't always respect robots.txt directives. They're designed to fetch content users explicitly request.
Spoofing is common. Fake crawlers can impersonate legitimate user agents. The most reliable verification is checking request IPs against officially published ranges from OpenAI, Anthropic, and others.
The landscape shifts fast. GPTBot's share of AI crawler traffic surged from 5% to 30% between May 2024 and May 2025. Meta-ExternalAgent appeared at 19% in 2025. Monitor your server logs.
Cloudflare changed defaults. Since July 2025, Cloudflare blocks AI bots by default. Site owners must explicitly allow AI crawlers if desired.

Sitemap optimization

Your sitemap helps AI systems discover and prioritize content:

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <!-- High priority: Getting started and core docs -->
  <url>
    <loc>https://example.com/docs/getting-started</loc>
    <lastmod>2026-01-12</lastmod>
    <changefreq>weekly</changefreq>
    <priority>1.0</priority>
  </url>

  <url>
    <loc>https://example.com/docs/api-reference</loc>
    <lastmod>2026-01-10</lastmod>
    <changefreq>weekly</changefreq>
    <priority>0.9</priority>
  </url>

  <!-- Medium priority: Guides and tutorials -->
  <url>
    <loc>https://example.com/docs/guides/authentication</loc>
    <lastmod>2026-01-08</lastmod>
    <changefreq>monthly</changefreq>
    <priority>0.7</priority>
  </url>

  <!-- Lower priority: Blog posts -->
  <url>
    <loc>https://example.com/blog/some-article</loc>
    <lastmod>2026-01-05</lastmod>
    <changefreq>yearly</changefreq>
    <priority>0.5</priority>
  </url>
</urlset>

IndexNow for content freshness

IndexNow is an open protocol created by Microsoft and Yandex that notifies search engines immediately when content changes. In an AI-powered search landscape, real-time updates matter more than ever.

When ChatGPT browses the web, it relies on Bing's infrastructure. The faster your site is indexed in Bing, the more likely your content will be found and cited in ChatGPT responses.

Implement IndexNow to push updates instantly:

// lib/indexnow.ts
const INDEXNOW_KEY = process.env.INDEXNOW_KEY;
const SITE_HOST = 'example.com';

export async function notifyIndexNow(urls: string[]) {
  if (!INDEXNOW_KEY) return;

  const response = await fetch('https://api.indexnow.org/indexnow', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      host: SITE_HOST,
      key: INDEXNOW_KEY,
      urlList: urls,
    }),
  });

  return response.ok;
}

// Call after publishing or updating content
await notifyIndexNow([
  'https://example.com/docs/new-feature',
  'https://example.com/blog/announcement',
]);

Host your key file at https://example.com/{your-key}.txt containing just the key value.

IndexNow is supported by Bing, Yandex, Naver, Seznam.cz, and Yep. It's integrated into major SEO plugins (Yoast, Rank Math) and platforms (Cloudflare, Shopify). The Home Depot reported new pages indexed within hours instead of days after implementing IndexNow.

RSS and Atom feeds

Feeds provide a structured stream of your content. Include full content, not just excerpts:

<?xml version="1.0" encoding="UTF-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
  <title>Company Blog</title>
  <link href="https://example.com/blog" rel="alternate"/>
  <link href="https://example.com/atom.xml" rel="self"/>
  <updated>2026-01-12T08:00:00Z</updated>
  <id>https://example.com/blog</id>

  <entry>
    <title>Article Title</title>
    <link href="https://example.com/blog/article-slug"/>
    <id>https://example.com/blog/article-slug</id>
    <updated>2026-01-12T08:00:00Z</updated>
    <summary>Brief excerpt</summary>
    <content type="html">
      <!-- Full HTML content of the article -->
      <![CDATA[
        <p>Full article content here...</p>
      ]]>
    </content>
    <author>
      <name>Author Name</name>
    </author>
  </entry>
</feed>

OpenAPI specification

If you have an API, publish an OpenAPI (Swagger) specification. This is the single most valuable artifact for AI systems trying to help developers integrate with your product:

openapi: 3.0.3
info:
  title: Company API
  description: Complete API for interacting with Company services
  version: 1.0.0
  contact:
    email: api@company.com
servers:
  - url: https://api.company.com/v1
    description: Production

paths:
  /users:
    get:
      summary: List all users
      description: Returns a paginated list of users in your organization
      operationId: listUsers
      parameters:
        - name: limit
          in: query
          description: Maximum number of users to return (1-100)
          schema:
            type: integer
            minimum: 1
            maximum: 100
            default: 20
        - name: cursor
          in: query
          description: Pagination cursor from previous response
          schema:
            type: string
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: object
                properties:
                  data:
                    type: array
                    items:
                      $ref: '#/components/schemas/User'
                  nextCursor:
                    type: string
                    nullable: true
              example:
                data:
                  - id: "usr_abc123"
                    email: "jane@example.com"
                    name: "Jane Developer"
                nextCursor: "cursor_xyz"
        '401':
          description: Invalid API key
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

components:
  schemas:
    User:
      type: object
      required:
        - id
        - email
      properties:
        id:
          type: string
          description: Unique user identifier
          example: "usr_abc123"
        email:
          type: string
          format: email
          description: User's email address
          example: "jane@example.com"
        name:
          type: string
          description: User's display name
          example: "Jane Developer"

    Error:
      type: object
      required:
        - error
      properties:
        error:
          type: object
          required:
            - code
            - message
          properties:
            code:
              type: string
              description: Machine-readable error code
            message:
              type: string
              description: Human-readable error description

Host your OpenAPI spec at predictable, conventional URLs. AI tools look for these locations:

https://api.company.com/openapi.yaml (API subdomain)
https://docs.company.com/openapi.json (docs root)
https://company.com/.well-known/openapi.yaml (well-known path)
https://company.com/api/openapi.json (api path)

Consider hosting both YAML and JSON formats. Link to the spec from your documentation homepage and robots.txt.

MCP compatibility

The Model Context Protocol (MCP) allows AI assistants to interact with external data sources directly. If your product has an API, consider building an MCP server.

MCP has become the universal standard for connecting AI models to tools, data, and applications. In December 2025, Anthropic donated MCP to the Agentic AI Foundation under the Linux Foundation, with OpenAI, Google, Microsoft, and Amazon as founding members. OpenAI officially adopted MCP in March 2025. There are now over 10,000 published MCP servers.

An MCP server exposes your API to AI assistants in a standardized way. The AI can then make API calls on behalf of the developer without writing integration code.

Security warning

MCP was not originally released with security as a priority. Research from Knostic in 2025 found 1,862 exposed MCP servers, all allowing unauthenticated access. These servers revealed internal tool listings, API tokens, and cloud credentials to anyone who knew how to query them.

If you build an MCP server:

Require authentication. Never expose tools without verifying the caller.
Use principle of least privilege. Don't give MCP servers service_role or admin access.
Bind to localhost only unless you specifically need remote access.
Audit tool permissions. Limit what actions each tool can perform.
Monitor usage. Log all MCP requests for security review.

Documentation template

Document your MCP server clearly:

## MCP Server

We provide an official MCP server for AI assistants.

### Installation

\`\`\`bash
npm install -g @company/mcp-server
\`\`\`

### Configuration (stdio transport)

Add to your Claude Desktop config (`~/Library/Application Support/Claude/claude_desktop_config.json`):

\`\`\`json
{
  "mcpServers": {
    "company": {
      "command": "company-mcp-server",
      "env": {
        "COMPANY_API_KEY": "your-api-key"
      }
    }
  }
}
\`\`\`

### Configuration (Streamable HTTP transport)

For remote MCP servers, use the HTTP transport:

\`\`\`json
{
  "mcpServers": {
    "company": {
      "url": "https://mcp.company.com/v1",
      "headers": {
        "Authorization": "Bearer your-api-key"
      }
    }
  }
}
\`\`\`

### Available Tools

The MCP server exposes these tools to AI assistants:

- \`list_users\`: List all users in your organization
- \`get_user\`: Get details for a specific user
- \`create_user\`: Create a new user
- \`update_user\`: Update an existing user
- \`delete_user\`: Delete a user

### Security

This server requires API key authentication. Never share your API key or commit it to version control. The server uses read-only database access by default; write operations require explicit scope elevation.

### Example Usage

Once configured, you can ask Claude:
- "List all users in my Company account"
- "Create a new user with email test@example.com"
- "Show me the details for user usr_abc123"

Scenario-based documentation

AI systems need to understand not just how your product works, but what can be built with it. This aligns with the twelve types of content that work for developers and choosing what content to build. Document common scenarios:

## Common Scenarios

### Building a User Dashboard

This guide shows how to build a real-time user dashboard.

**What you'll learn:**
- Fetching user data with pagination
- Setting up webhooks for real-time updates
- Displaying user activity feeds

**Prerequisites:**
- Node.js 18+
- A Company API key
- Basic familiarity with React

**Time to complete:** 30 minutes

[Full implementation guide follows...]

### Implementing Role-Based Access Control

This guide demonstrates implementing RBAC using our permissions API.

**What you'll learn:**
- Creating roles and permissions
- Assigning roles to users
- Checking permissions in your application

**Use cases:**
- Multi-tenant SaaS applications
- Enterprise applications with complex permission hierarchies
- Applications with admin/user role separation

[Full implementation guide follows...]

Testing AI consumption

Validate that AI can actually use your documentation:

Test with Claude: Copy a section of your docs into Claude and ask it to generate integration code. Does the output work?
Test with Cursor: Point Cursor at your docs directory. Can it answer questions about your API correctly?
Monitor support tickets: Track tickets mentioning "AI generated code" or "Copilot suggested." These reveal documentation gaps.
Run integration tests: Take AI-generated code from your docs and run it against your actual API. Does it work on first try?

The measurement problem

Traditional documentation metrics (page views, time on page) become less relevant when AI consumes your docs. This fundamentally changes how we measure developer marketing ROI. New metrics to track:

Integration quality metrics

Error rates on new integrations: If AI-generated code fails frequently, your docs have gaps
Time-to-first-successful-call: This should decrease as AI-assisted integration improves
Support tickets by topic: Spikes in basic questions indicate AI isn't finding answers

Traffic and attribution metrics

AI referral traffic: Track visits from AI user agents in your analytics. Filter server logs for GPTBot, ClaudeBot, PerplexityBot, ChatGPT-User, and similar agents.
API adoption vs. docs traffic ratio: Rising adoption with flat docs traffic means AI is working
llms.txt access logs: Check if AI crawlers even request your llms.txt file. Many don't.

Citation tracking

Brand mentions in AI responses: Tools like Qwairy and Profound track when AI systems cite your content
Competitor mention ratio: How often are you cited vs. competitors for relevant queries?
Accuracy of AI-generated code: Test whether AI assistants generate working code from your docs

Validation process

Run monthly validation checks:

Ask Claude, ChatGPT, and Perplexity questions about your product
Test the generated code against your actual API
Track accuracy rates over time
Identify gaps where AI hallucinates or gives outdated information

Putting it together

AI-mediated discovery and AI-friendly documentation is now a primary channel for reaching developers. The same principles that help AI systems, clear structure, complete examples, exhaustive error handling, also help human developers. For templates to systematize this work, see my developer marketing frameworks and templates. The companies that optimize for it will win. The ones that don't will watch their documentation work hard for diminishing returns.

For more on how AI is changing developer relations, read my piece on your docs are for AI now. For a complete overview of developer marketing strategy in 2026, see my complete developer marketing guide. And for the complete picture of developer marketing in the AI era, check out Picks and Shovels.

Analyzing the politics of COVID-19 through public data

Prashant 🗽 — Thu, 02 Apr 2020 21:12:54 +0000

Earlier today, I saw an article about how the current US Presidential Administration plans to distribute COVID-19 tests. According to the article, the Administration is debating sending tests to rural areas with relatively few cases instead of urban hotspots where the COVID-19 infection is growing at a geometric rate. Most public health experts agree that widespread testing will help restart the economy, the dominant driver of which resides in America's urban centers. On the surface, this decision appears to be political in nature.

So, I set out to demonstrate, through data, what those political calculations could be. What if we could look at COVID-19 data alongside political affiliation of a region and economic impact of those same regions? We would be able to determine the consequences of planned actions and encourage our decision-makers to act accordingly.

Fortunately, this data exists and in public form. Bringing this information together can help public officials prioritize scarce resources to optimize for better health outcomes and stave off a greater economic calamity.

To complete this analysis, I needed to combine three public datasets:

Important to note: I am not an epidemiologist or expert in any way, shape, or form. The public data is available for all of us to use, and this is a tutorial that helps us use that public data to understand the world around us. I'll also add that there's no shame in wanting to steep yourself in data about this crisis, nor is there any shame in walling yourself off from the data. We all cope with anxiety and stress in different ways, and at this moment in our history, taking the time to appreciate our differences will go a long way.

Top-line insight

Through this analysis, I was able to conclude several things:

Northern California is seeing a lower rate of confirmed cases and deaths than Southern California (my hypothesis, which is not substantiated by the data in this post, is that our weekend weather here in Northern California has been consistently terrible, making it easier to comply with social distancing orders)
At the current growth rate of COVID-19, the counties that voted for President Donald Trump are approximately 5-7 days behind the counties that voted for Secretary Hillary Rodham Clinton in the rate of reported infections and deaths
Counties that voted for President Trump account for 1/3 of total Gross Domestic Product (GDP), while counties that voted for Secretary Clinton accounted for 2/3 of total GDP
Electing to deploy resources to counties that voted for President Trump at the expense of counties that voted for Secretary Clinton will deepen the economic catastrophe of the entire nation
According to the Brookings Institution, 31 million fewer people live in counties that voted for President Trump than in counties that voted for Secretary Clinton

This is no time to play politics, yet we run the risk of political considerations guiding decision-making. Deploying resources to rural areas at the expense of urban areas may be a wise political calculation, but it runs the significant risk of deepening the nationwide health and economic crisis caused by COVID-19.

What follows are step-by-step instructions on how to obtain the data and come to your own conclusions.

Obtaining our datasets

As mentioned, we will be using three different datasets. Two of these are on GitHub, while the other we can obtain freely via a US Government website.

First, let's clone the two GitHub repositories we will need:

git clone https://github.com/nytimes/covid-19-data.git
git clone https://github.com/MEDSL/county-returns.git

And for the GDP data:

Click on "Interactive Data" and select "GDP by County and Metropolitan Area".
In the resulting screen, click on "GROSS DOMESTIC PRODUCT (GDP) BY COUNTY AND METROPOLITAN AREA".
Click on "Gross Domestic Product (GDP) summary (CAGDP1)".
You want "County" data, for "All counties in the US", and for our purposes we just need "Real GDP".
For the purposes of this tutorial, you only need "2018" data.
Select "Download" and choose "Excel". We will need to do some finagling in Microsoft Excel to clean up this dataset.

I wrote a blog post on cleaning up public data that I recommend reading. In this case, you will need to delete the rows at the top and bottom of your spreadsheet, turn the FIPS and GDP columns into numbers, and search and replace the handful of instances of "(NA)" with zeroes. Save your file as a CSV.

If you'd prefer not to download and manipulate the dataset yourself, you can get the CSV files from my GitHub repo.

Setting up your database and ingesting data

We will need to: setup our database, create our tables, and ingest our data.

Set up the database

For this tutorial, I'm using TimescaleDB, an open-source time-series database (and also my employer). The easiest way to use TimescaleDB is by signing up for Timescale Cloud. You get $300 in free credits, which is more than enough to complete this tutorial. This installation guide will get you up and running with TimescaleDB.

Be sure to also install psql and test that you can connect to your database, per the TimescaleDB installation instructions.

Before proceeding, create your database, which we will call nyt_covid, and add the TimescaleDB extension:

CREATE DATABASE nyt_covid;
\c nyt_covid
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Create tables

We will create the following tables:

counties
states
elections
gdp

This script will create the tables with the proper schema, and create the appropriate hypertables and views on the data (which we will use later during our analysis):

CREATE TABLE "states" (
    date DATE,
    state TEXT,
    fips NUMERIC,
    cases NUMERIC,
    deaths NUMERIC
);
SELECT create_hypertable('states', 'date', 'state', 2, create_default_indexes=>FALSE);
CREATE INDEX ON states (date ASC, state);

CREATE TABLE "counties" (
    date DATE,
    county TEXT,
    state TEXT,
    fips NUMERIC,
    cases NUMERIC,
    deaths NUMERIC
);
SELECT create_hypertable('counties', 'date', 'county', 2, create_default_indexes=>FALSE);
CREATE INDEX ON counties (date ASC, county);

CREATE TABLE "elections" (
    year NUMERIC,
    state TEXT,
    state_abbreviation TEXT,
    county TEXT,
    fips NUMERIC,
    office TEXT,
    candidate TEXT,
    party TEXT,
    votes NUMERIC,
    total_votes NUMERIC,
    version TEXT    
);

CREATE VIEW northern_california AS
SELECT date, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM counties
WHERE county IN ('San Francisco', 'Santa Clara', 'Alameda', 'Marin', 'San Mateo', 'Contra Costa') AND state = 'California'
GROUP BY date
ORDER BY date DESC;

CREATE VIEW southern_california AS
SELECT date, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM counties
WHERE county IN ('Los Angeles', 'Ventura', 'Orange', 'San Bernardino', 'Riverside') AND state = 'California'
GROUP BY date
ORDER BY date DESC;

CREATE TABLE "gdp" (
    fips NUMERIC,
    county TEXT,
    dollars NUMERIC
);

Once completed, you can run the \d command in psql and you should get a result like this:

                List of relations
 Schema |        Name         | Type  |   Owner   
--------+---------------------+-------+-----------
 public | clinton_counties    | view  | tsdbadmin
 public | counties            | table | tsdbadmin
 public | elections           | table | tsdbadmin
 public | gdp                 | table | tsdbadmin
 public | northern_california | view  | tsdbadmin
 public | southern_california | view  | tsdbadmin
 public | states              | table | tsdbadmin
 public | trump_counties      | view  | tsdbadmin
(8 rows)

Ingest the data

Now, let's ingest our data. We have three datasets, across four files:

us-counties.csv: county-by-county COVID-19 data from the New York Times
us-states.csv: state-by-state COVID-19 data from the New York Times
countypres_2000-2016.csv: county-by-county election results from MIT
county-gdp.csv: the file you saved via Excel containing county-by-county GDP data from the US Department of Commerce

The New York Times COVID-19 data is ready to go as-is, so we don't need to clean up that file. And you've already cleaned up the GDP data using Excel.

The election data requires a little bit of clean-up to replace instances of "NA" with zeroes. The following awk script will perform this substitution for us:

awk -F, '{if($5 == "NA") $5="0"; if($9 == "NA") $9="0"; if($10 == "NA") $10="0";}1' OFS=,  countypres_2000-2016.csv > countyresults.csv

Finally, let's use psql to load our data so we can get to the analysis:

\COPY counties FROM us-counties.csv CSV HEADER;
\COPY states FROM us-states.csv CSV HEADER;
\COPY elections FROM countyresults.csv CSV HEADER;
\COPY gdp FROM county-gdp.csv CSV HEADER;

You can test your ingestion with a simple SQL query, like this one:

SELECT * 
FROM counties 
ORDER BY date desc 
LIMIT 25;

And you should get a result like this:

    date    |     county      |     state      | fips  | cases | deaths 
------------+-----------------+----------------+-------+-------+--------
 2020-04-01 | Yuma            | Arizona        |  4027 |    12 |      0
 2020-04-01 | Yuma            | Colorado       |  8125 |     2 |      0
 2020-04-01 | Yolo            | California     |  6113 |    28 |      1
 2020-04-01 | Yellow Medicine | Minnesota      | 27173 |     1 |      0
 2020-04-01 | Yazoo           | Mississippi    | 28163 |     9 |      0
 2020-04-01 | Yankton         | South Dakota   | 46135 |     8 |      0
 2020-04-01 | Yadkin          | North Carolina | 37197 |     3 |      0
 2020-04-01 | Wyoming         | New York       | 36121 |    10 |      1
 2020-04-01 | Wyandot         | Ohio           | 39175 |     2 |      0
 2020-04-01 | Wright          | Iowa           | 19197 |     1 |      0
 2020-04-01 | Wright          | Minnesota      | 27171 |     6 |      0
 2020-04-01 | Wright          | Missouri       | 29229 |     4 |      0
 2020-04-01 | Woodson         | Kansas         | 20207 |     3 |      0
 2020-04-01 | Woodruff        | Arkansas       |  5147 |     1 |      0
 2020-04-01 | Woodbury        | Iowa           | 19193 |     4 |      0
 2020-04-01 | Wood            | Ohio           | 39173 |    15 |      0
 2020-04-01 | Wood            | Texas          | 48499 |     1 |      0
 2020-04-01 | Wood            | West Virginia  | 54107 |     2 |      0
 2020-04-01 | Wood            | Wisconsin      | 55141 |     2 |      0
 2020-04-01 | Winona          | Minnesota      | 27169 |    10 |      0
 2020-04-01 | Winneshiek      | Iowa           | 19191 |     3 |      0
 2020-04-01 | Winchester city | Virginia       | 51840 |     5 |      0
 2020-04-01 | Wilson          | North Carolina | 37195 |    15 |      0
 2020-04-01 | Wilson          | Tennessee      | 47189 |    45 |      0
 2020-04-01 | Wilson          | Texas          | 48493 |     5 |      0
(25 rows)

Analysis

Let's use this data to answer a few questions.

What is the national trend in reverse chronological order?

Our SQL query would look like this:

SELECT date, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM states
GROUP BY date
ORDER BY date DESC;

And the result would look like this (clipped for space):

    date    | total_cases | total_deaths 
------------+-------------+--------------
 2020-04-01 |      214461 |         4841
 2020-03-31 |      187834 |         3910
 2020-03-30 |      163796 |         3073
 2020-03-29 |      142161 |         2486
 2020-03-28 |      123628 |         2134
 2020-03-27 |      102648 |         1649
 2020-03-26 |       85533 |         1275
 2020-03-25 |       68515 |          990
 2020-03-24 |       53938 |          731

What is the state-by-state trend in reverse chronological order?

Now we will need to adjust our SQL query to GROUP BY the state, and we will order the results in reverse chronological and alphabetical order also:

SELECT date, state, cases, deaths
FROM states
GROUP BY date, state, cases, deaths
ORDER BY date DESC, state ASC;

And the result should look like this (clipped for space):

    date    |          state           | cases | deaths 
------------+--------------------------+-------+--------
 2020-04-01 | Alabama                  |  1106 |     28
 2020-04-01 | Alaska                   |   143 |      2
 2020-04-01 | Arizona                  |  1413 |     29
 2020-04-01 | Arkansas                 |   624 |     10
 2020-04-01 | California               |  9816 |    212
 2020-04-01 | Colorado                 |  3346 |     80

How is each part of California (or my state) doing?

In this case, we will adjust our query to search by county. This should give us a (rough) geographic approximation of where COVID-19 is spreading in each state we are interested in. So, we will search the counties table and we want to filter using the SQL WHERE clause, providing the name of the state we're interested in:

SELECT date, county, cases, deaths
FROM counties
WHERE state = 'California'
GROUP BY date, county, cases, deaths
ORDER BY date DESC, county ASC;

The result should look like this (clipped for space):

    date    |     county      | cases | deaths 
------------+-----------------+-------+--------
 2020-04-01 | Alameda         |   380 |      8
 2020-04-01 | Alpine          |     1 |      0
 2020-04-01 | Amador          |     3 |      1
 2020-04-01 | Butte           |     8 |      0
 2020-04-01 | Calaveras       |     3 |      0
 2020-04-01 | Colusa          |     1 |      0
 2020-04-01 | Contra Costa    |   250 |      3

What about Northern California vs. Southern California?

Earlier we created two views, northern_california and southern_california. To recap, here's the CREATE VIEW statement for Northern California from our script. It queries all counties in Northern California. In this case, we have to structure the WHERE clause so that it searches for specific counties in a specified state. You'd be surprised how many duplicate county names there are across the United States:

CREATE VIEW northern_california AS
SELECT date, sum (cases) as total_cases, sum (deaths) as total_deaths
FROM counties
WHERE county IN ('San Francisco', 'Santa Clara', 'Alameda', 'Marin', 'San Mateo', 'Contra Costa') AND state = 'California'
GROUP BY date
ORDER BY date DESC;

What we'd like to do is see the date-over-date comparison between these two regions. We could run two queries, like these:

SELECT * FROM northern_california;
SELECT * FROM southern_california;

But it would assist our analysis to see them alongside one another. For this, we will use the UNION ALL function in SQL to merge the two queries, and the crosstabview function in PostgreSQL (which TimescaleDB is based on) to arrange the results side-by-side:

SELECT *, 'NorCal' AS region FROM northern_california
WHERE date >= current_date - interval '10' day
UNION ALL
SELECT *, 'SoCal' AS region FROM southern_california
WHERE date >= current_date - interval '10' day
GROUP BY date, region, total_cases, total_deaths
ORDER BY date DESC, region DESC \crosstabview region date total_cases;

Our result should look like this:

 region | 2020-04-01 | 2020-03-31 | 2020-03-30 | 2020-03-29 | 2020-03-28 | 2020-03-27 | 2020-03-26 | 2020-03-25 | 2020-03-24 | 2020-03-23 
--------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
 SoCal  |       4909 |       4216 |       3466 |       2982 |       2472 |       2118 |       1695 |       1197 |        950 |        761
 NorCal |       2519 |       2257 |       2121 |       1825 |       1692 |       1556 |       1358 |       1122 |        978 |        854
(2 rows)

Graphing data using Grafana

Grafana is an open source visualization tool for time-series data. You can install Grafana by following this tutorial. You'll want to setup a new datasource that connects to your TimescaleDB instance. If you're using Timescale Cloud, this information can be found in the "Overview" tab of your Timescale Cloud Portal:

Once Grafana is setup, you can create a new dashboard and a new visualization. For visualization, we will use a simple line graph.

In the "General" tab, set the "Title" to "Northern and Southern California".
In the "Visualization" tab, set the "Draw Mode" to "Bars" and uncheck "Lines". In the "Stacking & Null value" section, turn "Stack" on.
In the "Queries" tab, click on the "Query" dropdown to select your datsource. Now, click on "Edit SQL" and enter the following:

SELECT date as "time", 'NorCal' AS region, total_cases
FROM northern_california
GROUP BY date, region, total_cases
ORDER BY date

Click the "Add Query" button to add a second query to your visualization and add the following query:

SELECT date as "time", 'SoCal' AS region, total_cases
FROM southern_california
GROUP BY date, region, total_cases
ORDER BY date

Your query and graph should now look like this:

(and here's a view of just the graph)

What is the rate of change in cases?

The rate of change day-over-day gives us a good idea of the velocity with which events are changing. Combined with the raw numbers, we can develop understanding of whether or not we are making progress in the fight against COVID-19. To calculate the rate of change, we will use the time_bucket function in TimescaleDB. Time bucket, as the name suggests, enables us to bucket our results in a pre-defined period of time. For example, we could look at the rate of change every day, or every few days. In this case, let's query for the rate of change in cases due to COVID-19 day-over-day:

SELECT time_bucket('1 day', date) AS day,
       state,
       cases,
       lag(cases, 1) OVER (
           PARTITION BY state
           ORDER BY date
       ) previous_day,
       round (100 * (cases - lag(cases, 1) OVER (PARTITION BY state ORDER BY date)) / lag(cases, 1) OVER (PARTITION BY state ORDER BY date)) AS rate_of_change
FROM states
WHERE date >= current_date - interval '10' day
GROUP BY date, state, cases
ORDER BY date DESC, rate_of_change DESC;

Our results should look like this (clipped for space):

    day     |          state           | cases | previous_day | rate_of_change 
------------+--------------------------+-------+--------------+----------------
 2020-04-01 | Northern Mariana Islands |     6 |            2 |            200
 2020-04-01 | Tennessee                |  2440 |         1834 |             33
 2020-04-01 | Nebraska                 |   249 |          193 |             29
 2020-04-01 | Oklahoma                 |   719 |          566 |             27
 2020-04-01 | Idaho                    |   669 |          526 |             27
 2020-04-01 | Louisiana                |  6424 |         5237 |             23
 2020-04-01 | Michigan                 |  9293 |         7630 |             22
 2020-04-01 | Virginia                 |  1511 |         1250 |             21
 2020-04-01 | Puerto Rico              |   286 |          239 |             20
 2020-04-01 | South Carolina           |  1293 |         1083 |             19

And, if you'd prefer, you can arrange it as cross-tabs ordered by state:

SELECT time_bucket('1 day', date) AS day,
       state,
       cases,
       lag(cases, 1) OVER (
           PARTITION BY state
           ORDER BY date
       ) previous_day,
       round (100 * (cases - lag(cases, 1) OVER (PARTITION BY state ORDER BY date)) / lag(cases, 1) OVER (PARTITION BY state ORDER BY date)) AS rate_of_change
FROM states
WHERE date >= current_date - interval '10' day
GROUP BY date, state, cases
ORDER BY date DESC, state ASC \crosstabview state day rate_of_change;

Your result should look like this (clipped for space):

          state           | 2020-04-01 | 2020-03-31 | 2020-03-30 | 2020-03-29 | 2020-03-28 | 2020-03-27 | 2020-03-26 | 2020-03-25 | 2020-03-24 | 2020-03-23 
--------------------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
 Alabama                  |         11 |          5 |         14 |         15 |         13 |         19 |         39 |         60 |         23 |     [null]
 Alaska                   |          8 |         12 |          4 |         12 |         20 |         23 |         17 |         40 |         17 |     [null]
 Arizona                  |          9 |         11 |         26 |         20 |         16 |         31 |         26 |         23 |         39 |     [null]
 Arkansas                 |         11 |         11 |         13 |         10 |          6 |         10 |         14 |         33 |         15 |     [null]
 California               |         14 |         16 |         18 |         13 |         13 |         21 |         28 |         20 |         18 |     [null]
 Colorado                 |         12 |         14 |         14 |         12 |         19 |         21 |         32 |         19 |         26 |     [null]

How does the spread of COVID-19 relate to election data?

Given that our political leadership has transformed what should be purely a public health discussion into a political and partisan one, it may be important to factor in political considerations when understanding the spread and impact of COVID-19.

Our election data from the MIT Election Data Science Lab is organized as follows, which is reflected in the schema we setup earlier for the elections table:

The year of the election (data in this dataset goes back to 2000)
The state and county (with corresponding fips code, a standard numeric designation used by the United States Government)
The candidate
The votes the candidate received, and the total_votes in the election

There are other fields in the dataset, but they're not relevant to our analysis here.

We can start by segmenting our election data into counties that voted for President Trump and counties that voted for Secretary Clinton. To do this, we will create two SQL views, each of which include a subquery:

-- find all Trump counties
CREATE VIEW trump_counties AS
SELECT * FROM (
    SELECT year, state, county, fips, last(candidate, votes) as winner, max(votes) as winning_votes
    FROM elections 
    WHERE year = 2016
    GROUP BY year, state, county, fips 
    ORDER BY year, state, county
) all_winners 
WHERE winner = 'Donald Trump';

-- find all Hillary Clinton counties
CREATE VIEW clinton_counties AS
SELECT * FROM (
    SELECT year, state, county, fips, last(candidate, votes) as winner, max(votes) as winning_votes
    FROM elections 
    WHERE year = 2016
    GROUP BY year, state, county, fips 
    ORDER BY year, state, county
) all_winners 
WHERE winner = 'Hillary Clinton';

Now, let's look at all cases and deaths in each of the counties that voted for President Trump. To execute this query, we will use a subquery matched on the county "FIPS" code. Because we're using public datasets from three different sources, we want to account for possible discrepancies in county names, be they from spelling errors, use of special characters, and so forth. Standardizing on a numeric FIPS code enables us to match queries across tables:

SELECT counties.date, sum (counties.cases) as total_cases, sum (counties.deaths) as total_deaths
FROM counties
WHERE counties.fips IN (SELECT fips FROM trump_counties) AND date >= current_date - interval '10' day
GROUP BY date
ORDER BY date DESC;

And our result should look like this:

    date    | total_cases | total_deaths 
------------+-------------+--------------
 2020-04-01 |       43339 |          903
 2020-03-31 |       37101 |          702
 2020-03-30 |       31466 |          553
 2020-03-29 |       26802 |          455
 2020-03-28 |       22692 |          404
 2020-03-27 |       18704 |          317
 2020-03-26 |       14997 |          230
 2020-03-25 |       11726 |          174
 2020-03-24 |        9353 |          126
 2020-03-23 |        7363 |           96
(10 rows)

We can run a similar query for counties that voted for Secretary Clinton by substituting the clinton_counties view and obtain the following results:

    date    | total_cases | total_deaths 
------------+-------------+--------------
 2020-04-01 |      116490 |         2178
 2020-03-31 |      101935 |         1769
 2020-03-30 |       88163 |         1393
 2020-03-29 |       76400 |         1129
 2020-03-28 |       66035 |         1029
 2020-03-27 |       55193 |          854
 2020-03-26 |       44653 |          641
 2020-03-25 |       35046 |          512
 2020-03-24 |       28361 |          400
 2020-03-23 |       22702 |          314
(10 rows)

Graphing the spread of COVID-19 alongside election data

Let's view the results of our analysis into COVID-19 cases and election data in a Grafana visualization.

In Grafana, add a new panel and choose the "Graph" visualization. This time, we will create a simple line chart with all the default settings. Make sure the correct dataset is selected in the "Query" drop-down, add the following query by clicking "Edit SQL":

SELECT counties.date as "time", sum (counties.cases) as trump_cases
FROM counties
WHERE counties.fips IN (SELECT fips FROM trump_counties)
GROUP BY date
ORDER BY date DESC;

Add another query, click "Edit SQL", and enter the following:

SELECT counties.date as "time", sum (counties.cases) as clinton_cases
FROM counties
WHERE counties.fips IN (SELECT fips FROM clinton_counties)
GROUP BY date
ORDER BY date DESC;

The resulting visualization should look like this:

(and zoomed in on the graph itself)

And what about the economic impact?

We can use the county-by-county Gross Domestic Product (GDP) data to look at GDP across the country and within each county itself. First, let's look at how much total GDP is represented in our dataset using the simplest query we've run in this tutorial:

SELECT sum(dollars)
FROM gdp;

Our result is $18,452,822,315.00, or close to $18.5T. The actual GDP of the country is a bit higher, but our dataset accounts for $18.5T.

Now, we can compare the GDP of the counties where there are greater than 100 cases:

SELECT sum(dollars) AS total_gdp
FROM gdp
WHERE gdp.fips IN (SELECT fips FROM counties WHERE cases > 100 AND date = current_date - 1);

The resulting answer ($11,229,517,359.00) is roughly 61% of the total GDP in our dataset. (of course, these results will change depending on when you choose to run these queries)

Put it all together

We know that so far, the counties that voted for Secretary Clinton are harder hit in terms of total COVID-19 cases and deaths than the counties that voted for President Trump.

Using this query, we can see the total GDP of counties that voted for President Trump:

SELECT sum(dollars) AS total_gdp
FROM gdp
WHERE gdp.fips IN (SELECT fips FROM trump_counties);

It amounts to about $6.3T, or 1/3 of total GDP of the United States.

A similar query can be run for counties that voted for Secretary Clinton:

SELECT sum(dollars) AS total_gdp
FROM gdp
WHERE gdp.fips IN (SELECT fips FROM clinton_counties);

Those counties amount to $11.9T, or 2/3 of total GDP of the United States.

In order to stave off even greater economic catastrophe, it would behoove the United States Government to quickly stabilize counties that did not vote for President Trump, because these account for 2/3 of total US GDP, before those that did vote for him. While correlation isn't causation, and we'd like to give everyone the benefit of the doubt, one fact remains true: playing politics in the middle of a health and economic crisis hurts all Americans, everywhere.

Summary

Data gives us insight into the world around us. By using data, we are able to make better decisions for our physical, emotional, and financial health. This post gives you much of the mechanics of extracting and querying data. Conducting analysis and making inferences based on that data is an art form, and always subject to interpretation. I'd love to see your interepretation and further analysis.

As you can see, bringing multiple public datasets together can be fascinating. I’ve also started a virtual meetup (with the help of my colleagues at Timescale) so I can meet people with similar interests and continue to learn new things. If you’re a data enthusiast, you’re welcome (and encouraged) to join us at any time - the more the merrier.

Finally, please follow all guidelines from your local public health authorities. Let's all look out for one another, be kind, and do our part to get through this time as safely as possible.

How to weave together public datasets to make sense of the world

Prashant 🗽 — Thu, 26 Mar 2020 18:10:24 +0000

(This post originally appeared on the Timescale blog on March 19, 2020)

Public datasets can help us gain insight into our business and our world. Combining public datasets, either together or with our own data, often requires a series of steps to cleanup (or “normalize”) the data. This blog post walks you through some data normalization techniques.

I enjoy using public datasets, combining them with metrics and business data, and seeing if there are any correlations. It’s kind of a personal hobby. I recently wrote a blog post about analyzing COVID-19 data along that vein, and I’m starting a virtual meetup (with the help of my colleagues at Timescale) so I can meet people with similar interests and continue to learn new things. If you’re a data enthusiast, you’re welcome (and encouraged) to join us at any time - the more the merrier.

My projects’ goals are usually to take data from one source and combine it with data from another source in order to learn something interesting. One of the benefits of using a PostgreSQL time-series database like TimescaleDB is the ability to combine analytics and metrics (this is inherently time-series data, as we care about the specific time we collected each value) with other relational data.

For example, you may want to combine historic weather data with business performance to identify correlations that could predict future business results. You’d need a high-performance time-series database that also supports the traditional relational data for your business (for example, products, customers, and orders). Voila Timescale! Combining your external metadata with your time-series data is just a SQL statement.

One problem you are likely to encounter is normalizing your data. This is especially true in the realm of publicly available, open datasets. In my latest project, I wanted to analyze two public datasets: the San Francisco police incident database and the National Oceanic and Atmospheric Administration (NOAA) historic weather database (for the San Francisco area). There were two challenges:

The format for a “date” in each dataset was different, making it difficult to join the information for analysis. For example, if I wanted to know how many car prowls took place on occasions with more than 1cm of rain, I’d need to join both databases on their date columns: incident_date and weather_date respectively. In the police database, dates are formatted as yyyy-mm-dd, while in the weather database, it’s mm/dd/yyyy.
The weather data had numerous gaps, which made time-series graphs difficult to chart.

Let’s walk through how to solve both (all too common) issues.

Cleaning up your data format

The technique you use to clean data depends on how frequently you’ll want to update your dataset and run your analysis.

If you’re looking to download your data once before importing and using it, then you can use anything from Excel (for smaller datasets) to Unix tools like awk.

First, download your data in CSV format (step 0). In Excel, open your CSV file, highlight a column, and in the Ribbon (top-nav menu), select the ‘Number Formats’ drop-down (it usually starts by saying “General”), and choose the option. Select ‘Custom’ and in the ‘Type:’ field, enter your preferred date format.

In this case, we want to reformat the weather data to match the police dataset. Thus, we want to reformat the date field to yyyy-mm-dd, which we can enter directly. Press ‘OK’ and your column is now formatted however you’d like.

Option 1: Use Excel "Number Formats" settings to apply changes to your dataset files.

While Excel works for moderately sized datasets, if you have a large dataset, datasets that you want to update frequently, or data that you retrieve programmatically at periodic intervals, you need something that you can run on the command line and/or automate.

For this, we’ll use awk, a programming language designed for manipulating text-based data. awk is ideal for formatting dates in CSV files. This is the awk command for reformatting a date with mm/dd/yyyy into yyyy-mm-dd format:

awk -F, '{split($7,a,"/"); $7=a[3]"-"a[1]"-"a[2]}1' OFS=,  Weather_SFO.csv

Let’s dissect this command:

-F is the field separator. In this case we have a comma-separated file, so the parameter for this argument is a comma (i.e., a comma separates each of our fields).
split: In our original comma-separated file, the 7th item in each row is the date we are trying to reformat. Therefore, we’ll split the 7th ($7) field into strings, based on the forward slash character and store each part in an array called a.
We will then rearrange the date format to be the third (a[3]), first (a[1]), and second (a[2]) elements in the array, separated by dashes We’ll continue processing the file and, in the output (OFS), use commas to separate them
And the file we will work on is Weather_SFO.csv

Once you run the awk command, you’ll see the output in your Terminal, which you can then pipe into another file.

Filling in data gaps (NULL values)

When we use public data, we often see “gaps,” or times where the dataset doesn’t show any reported values for a given area, time, or other variable. This can be due to faulty sensors, systems maintenance or outage, human error in creating the tables, or a slew of other reasons.

For example, in the San Francisco weather data we’ve been using as an example in this blog post, we see a NULL in the second row (mid temperature on January 6, 2018):

station    |              name              | latitude | longitude | elevation | weather_date | precipitation | temp_max | temp_min | temp_mid
------------+--------------------------------+----------+-----------+-----------+--------------+---------------+----------+----------+----------
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-05   |           7.9 |     18.3 |      6.1 |     11.1
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-06   |             0 |     13.9 |      7.8 |   [null]
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-07   |             0 |     11.7 |      8.3 |     10.6
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-08   |          93.5 |     11.1 |      8.9 |     11.1
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-09   |           9.9 |     11.7 |       10 |       10
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-10   |           0.3 |       10 |      8.3 |       10
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-11   |           0.3 |     13.3 |      8.3 |     11.7
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-12   |             0 |     16.7 |      3.9 |      8.9
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-13   |             0 |     17.2 |      4.4 |      8.3
USC00047767 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |       2.4 | 2018-01-14   |             0 |     18.3 |      4.4 |     12.8
(10 rows)

If you’re trying to visualize data, this presents a problem: we need values for each row and can’t have instances where there isn’t any data to visualize. We see this with monitoring metrics as well; to view systems performance, we need to plot values at each desired interval, or our trend lines and graphs look faulty.

Fortunately, this is an area where TimescaleDB excels (no pun intended), with capabilities like gap filling and time_bucket - a more powerful version of SQL date_trunc. By gap filling the information in our weather dataset, we fill in every NULL value with the last (typically most recent) value for that sensor, by date. We call this “last observation carried forward” or locf.

To do so, we use the Timescale time_bucket_gapfill function:

SELECT
    time_bucket_gapfill('1 day', weather_date) AS date,
    name, latitude, longitude,
    locf(avg(temp_mid), treat_null_as_missing:=true)
FROM weather
WHERE weather_date >= '2018-01-05' AND 
      weather_date <= '2018-01-30' AND 
      name = 'SAN FRANCISCO OCEANSIDE, CA US'
GROUP BY date, name, latitude, longitude
ORDER BY date
LIMIT 10;

You can read more about time_bucket_gapfill in our documentation and blog post.

Now, when we run the query, we can see that our NULL value in the second row is replaced with the temperature value from the previous day:

date       |              name              | latitude | longitude |        locf
-----------+--------------------------------+----------+-----------+---------------------
2018-01-05 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 11.1000000000000000
2018-01-06 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 11.1000000000000000
2018-01-07 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 10.6000000000000000
2018-01-08 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 11.1000000000000000
2018-01-09 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 10.0000000000000000
2018-01-10 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 10.0000000000000000
2018-01-11 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 11.7000000000000000
2018-01-12 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |  8.9000000000000000
2018-01-13 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 |  8.3000000000000000
2018-01-14 | SAN FRANCISCO OCEANSIDE, CA US |   37.728 | -122.5052 | 12.8000000000000000
(10 rows)

You can also use averages or your own computation to perform your gap filling operation. Consult the TimescaleDB documentation for more ideas based on your use case.

Conclusion

Public datasets are amazing. Whether you combine them with other public datasets or with your own proprietary data, they give you a wealth of resources to query, analyze, and use to better understand your world (whatever that means to you).

If you love public datasets or are intrigued by ways you can better use them, be sure to join our new meetup: DataPub: A Virtual Meetup for Public Data Enthusiasts. We'll meet online every month, and it's a great chance to hear from people using, creating, or building tools for public datasets.

Hopefully this post has given you some ideas and ways to overcome a few obstacles, so you can power through your data preparation and get to the best part: analysis, visualization, and sharing your findings with others.

Charting the spread of COVID-19 using data

Prashant 🗽 — Mon, 09 Mar 2020 18:58:46 +0000

Data gives us insight into the world around us – and allows us to visualize and prepare for the spread of the COVID-19 pandemic.

(This post was originally published on the Timescale Blog on March 5, 2020.)

In recent months, we’ve seen the spread of the COVID-19 virus (commonly referred to as "coronavirus"). Not only are we a globally distributed team, some Timescale team family members see fears and concerns first-hand while working in our emergency rooms.

First and foremost, our thoughts are with those who've been affected in any way. We join the global community in hoping for the best in the coming weeks and months, especially in containing transmission and reducing the virus' spread.

We were recently made aware of a time-series dataset from Johns Hopkins University, containing daily case reports from around the world. Timescale community member Joel Natividad created this repo of time-series utilities that enables people to use the Johns Hopkins University dataset with TimescaleDB to better monitor the COVID-19 situation.

In this post, we'll walkthrough how to load the dataset into TimescaleDB and use Grafana to visualize queries. Our hope is that you’ll be able to use this dataset and tutorial as a starting point for your own analysis (extending upon the queries we include), or to share information with colleagues, family, and friends.

You can get TimescaleDB in two forms: hosted Timescale Cloud (with free credits) or TimescaleDB for download. You can follow these instructions to choose your installation method. If you haven’t done so, you’ll also want to install psql to access the database from your command line.

Ingesting the dataset

Once you’ve installed TimescaleDB, you will need to load the dataset. First, you will want to clone the GitHub repository Joel created:

git clone https://github.com/dathere/covid19-time-series-utilities.git

This repository contains several files, which are explained in the README. Setup your database and ingest the data according to the instructions in the GitHub repository.

Once the data is fully ingested, you should be able to login to your database from psql:

psql -x "postgres://[YOUR USERNAME]:[YOUR PASSWORD]@[YOUR HOST]:[YOUR PORT]/[DB_NAME]?sslmode=require"

Once you’re logged in, you will be able to run the following query:

SELECT * FROM covid19_ts LIMIT 5;

Your output should look something like this:

province_state | country_region |    observation_date    | confirmed | deaths | recovered
---------------+----------------+------------------------+-----------+--------+-----------
Hubei          | Mainland China | 2020-01-31 23:59:00+00 |      5806 |    204 |       141
Zhejiang       | Mainland China | 2020-01-31 23:59:00+00 |       538 | [null] |        14
Guangdong      | Mainland China | 2020-01-31 23:59:00+00 |       436 | [null] |        11
Henan          | Mainland China | 2020-01-31 23:59:00+00 |       352 |      2 |         3
Hunan          | Mainland China | 2020-01-31 23:59:00+00 |       332 | [null] |         2
(5 rows)

At this point, your time-series database is properly configured and you've loaded the COVID-19 dataset.

Note: the Johns Hopkins University data is a running total, not a per-day total. So, the data for February 23rd for any given location represents a cumulative tally of all cases in that location as of February 23rd.

Before we continue, we need to clean up our dataset.

In some cases, a given row in the covid19_ts or covid19_locations table may not have a province_state entered properly. Cleaning public datasets is fairly common, and fortunately, this dataset is easy to prepare for the rest of our tutorial. In psql enter the following commands:

UPDATE covid19_locations 
SET province_state = 
    CASE WHEN province_state = '' 
    THEN country_region 
    ELSE province_state 
    END;

At this point, all our rows have either a specific Province or State in the province_state column, or, failing that, we default to the country_region.

Now, let’s start running queries that will give us some insight into the pandemic.

How many confirmed cases were reported worldwide each day?

Let’s start by identifying how many cumulative cases have been identified each day; the dataset starts on January 22nd, so that’s where we'll start for our query.

SELECT time_bucket('1 day', observation_date) AS day,
       SUM(confirmed)
FROM covid19_ts
GROUP BY day
ORDER BY day;

Your output should look something like this:

         day           |  sum
-----------------------+-------
2020-01-22 00:00:00+00 |   555
2020-01-23 00:00:00+00 |   653
2020-01-24 00:00:00+00 |   941
2020-01-25 00:00:00+00 |  1438
2020-01-26 00:00:00+00 |  2118
2020-01-27 00:00:00+00 |  2927
2020-01-28 00:00:00+00 |  5578
2020-01-29 00:00:00+00 |  6165
2020-01-30 00:00:00+00 |  8235
2020-01-31 00:00:00+00 | 10037
2020-02-01 00:00:00+00 | 12009
2020-02-02 00:00:00+00 | 16685
2020-02-03 00:00:00+00 | 19719
2020-02-04 00:00:00+00 | 23821
2020-02-05 00:00:00+00 | 27457
(edited for length)

How many confirmed cases have been reported each day near Milan, Italy?

As of the publication date of this post, northern Italy has the highest concentration of COVID-19 cases, outside of China, and it continues to intensify.

What if we wanted to see the growth rate of COVID-19 in Italy?

To do this, we query on the country_region field in our database:

SELECT * FROM covid19_ts WHERE country_region = 'Italy';

Our output will look something like this:

province_state | country_region | observation_date       | confirmed | deaths | recovered
---------------+----------------+------------------------+-----------+--------+-----------
               | Italy          | 2020-01-31 23:59:00+00 |         2 | [null] |    [null]
               | Italy          | 2020-01-31 08:15:00+00 |         2 |      0 |         0
               | Italy          | 2020-01-31 08:15:53+00 |         2 |      0 |         0
               | Italy          | 2020-02-07 17:53:02+00 |         3 |      0 |         0
               | Italy          | 2020-02-21 23:33:06+00 |        20 |      1 |         0
               | Italy          | 2020-02-22 23:43:02+00 |        62 |      2 |         1
               | Italy          | 2020-02-23 23:43:02+00 |       155 |      3 |         2
               | Italy          | 2020-02-24 23:43:01+00 |       229 |      7 |         1
               | Italy          | 2020-02-25 18:55:32+00 |       322 |     10 |         1
               | Italy          | 2020-02-26 23:43:03+00 |       453 |     12 |         3
               | Italy          | 2020-02-27 23:23:02+00 |       655 |     17 |        45
               | Italy          | 2020-02-28 20:13:09+00 |       888 |     21 |        46
               | Italy          | 2020-02-29 18:03:05+00 |      1128 |     29 |        46
               | Italy          | 2020-03-01 23:23:02+00 |      1694 |     34 |        83
               | Italy          | 2020-03-02 20:23:16+00 |      2036 |     52 |       149
(15 rows)

How many confirmed cases have been reported each week near me?

What if we wanted to look at cases near a specific location?

This requires that we make use of the latitude and longitude information stored in our database. To use the location, we’ll need to get our tables ready for geospatial queries.

One caveat: The Johns Hopkins University dataset has limited information about specific locations. All cases in Italy are marked as “Italy,” for example, while cases in China and the United States may be more specific with regional or city locations.

We can use the PostGIS PostgreSQL extension to help analyze geospatial data. (This is available out-of-the-box on Timescale Cloud, or you can manually install for self-hosted versions.) PostGIS allows us to slice data by time and location within TimescaleDB.

CREATE EXTENSION postgis;

Then, run the \dx command in psql to verify that PostGIS was installed properly. You should see the PostGIS extension in your extension list, as noted below:

List of installed extensions
Name        | Version |   Schema   |                             Description
------------+---------+------------+---------------------------------------------------------------------
plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis     | 2.5.3   | public     | PostGIS geometry, geography, and raster spatial types and functions
timescaledb | 1.6.0   | public     | Enables scalable inserts and complex queries for time-series data
(3 rows)

The covid19_locations table in our database includes the locations in the Johns Hopkins University dataset, as well as their latitude and longitude coordinates.

For example, we can see all the locations in the United States like so:

SELECT * FROM covid19_locations WHERE country_region = 'US' LIMIT 10;

The output for this query looks something like this:

               province_state                | country_region | latitude | longitude 
---------------------------------------------+----------------+----------+-----------
 Unassigned Location (From Diamond Princess) | US             |  35.4437 |  139.6380
 King County, WA                             | US             |  47.5480 | -121.9836
 Santa Clara, CA                             | US             |  37.3541 | -121.9552
 Snohomish County, WA                        | US             |  48.0330 | -121.8339
 Cook County, IL                             | US             |  41.7377 |  -87.6976
 Fulton County, GA                           | US             |  33.8034 |  -84.3963
 Grafton County, NH                          | US             |  43.9088 |  -71.8260
 Hillsborough, FL                            | US             |  27.9904 |  -82.3018
 Providence, RI                              | US             |  41.8240 |  -71.4128
 Sacramento County, CA                       | US             |  38.4747 | -121.3542
(10 rows)

We need to alter the covid19_locations table to work with PostGIS.
To start, we’ll add geometry columns for case locations:

ALTER TABLE covid19_locations
    ADD COLUMN location_geom geometry(POINT, 2163);

Next we’ll need to convert the latitude and longitude points into geometry coordinates (so they play well with PostGIS).

UPDATE covid19_locations
    SET location_geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 2163);

Lastly, let’s say we want to examine cases near Seattle, in the United States. Seattle is located at (lat, long) (47.6062,-122.3321).

Our database is setup, so we're ready to run a geospatial query that returns the number of confirmed COVID-19 cases within 75km of Seattle, each day, since the start of our dataset. (TimescaleDB is based on PostgreSQL, so we can use all our favorite SQL functions.)

In this case, we need to use a subquery for the information in our covid19_locations table and a broader query for all confirmed cases that match the locations in our subquery.

First, we write the subquery by querying the covid19_locations table for all locations within 75km of Seattle:

SELECT *
FROM covid19_locations
WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000;

We should get output that looks like this:

province_state       | country_region | latitude | longitude |                   location_geom
---------------------+----------------+----------+-----------+----------------------------------------------------
King County, WA      | US             |  47.5480 | -121.9836 | 010100002073080000A2B8C609FEC838C1004A6D25430F1F41
Snohomish County, WA | US             |  48.0330 | -121.8339 | 010100002073080000344A7104C26438C1768752D481082141
(2 rows)

Now, we need to find all confirmed cases in our covid19_ts dataset whose province_state matches the result of our first query.

SELECT time_bucket('1 day', observation_date) as day,
       SUM(confirmed) AS near_sea
FROM covid19_ts
WHERE province_state IN (
    SELECT province_state
    FROM covid19_locations
    WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000
)
GROUP BY day
ORDER BY day;

Your output should look something like this:

          day           | near_sea 
------------------------+----------
 2020-02-29 00:00:00+00 |        1
 2020-03-01 00:00:00+00 |        2
 2020-03-02 00:00:00+00 |       18
 2020-03-03 00:00:00+00 |       27
 2020-03-04 00:00:00+00 |       39
(5 rows)

Use Grafana to visualize confirmed cases by geographic location

We have the dataset, we have our queries, and, now, let's take the COVID-19 dataset and visualize the total number of confirmed cases by geographic location.

To create our visualization, we’ll use Grafana. Follow our short tutorial on getting started with Grafana to setup Grafana in Timescale Cloud, your desktop, or using Grafana Cloud, using the dataset you’ve been using in this post.

In Grafana, create a new dashboard and add a new visualization. In the visualization menu, search for and select the WorldMap panel type.
To build our visualization, we will need our information organized properly in our query:

For each day
A sum of all confirmed cases
Grouped by the province_state where they happened
Plotted on the map using the latitude and longitude for the province_state
Ordered by time

To build this query, we use a SQL INNER JOIN statement to combine information from the covid19_ts dataset and the covid19_locations tables:

SELECT time_bucket('1 day', covid19_ts.observation_date) AS day,
       SUM(covid19_ts.confirmed) AS confirmed, 
       covid19_ts.province_state as location, 
       covid19_locations.latitude AS latitude, 
       covid19_locations.longitude AS longitude
FROM covid19_ts
INNER JOIN covid19_locations ON covid19_locations.province_state = covid19_ts.province_state 
WHERE $__timeFilter(covid19_ts.observation_date)
GROUP BY day,
         confirmed,
         location,
         latitude,
         longitude 
ORDER BY day;

To enter this query in Grafana, click “Edit SQL” and type it in manually (or copy-paste from the above):

And then in the WorldMap configuration, we set the field mapping to match our query results:

And, once completed, we see the impact of the COVID-19 pandemic on the Grafana WorldMap panel:

How rapidly is COVID-19 spreading near me?

As mentioned earlier, the Johns Hopkins dataset includes cumulative data. For example, an entry for March 2, 2020 contains all cases that have been confirmed at that point in time. Suppose we wanted to learn the rate at which cases have been identified near a specific location.

TimescaleDB includes a feature called continuous aggregates. A continuous aggregate recomputes a query automatically at user-specified time intervals and maintains the results into a table. Thus, instead of everyone running an aggregation query each time, the database can run a common aggregation periodically in the background, and users can query the results of the aggregation. Continuous aggregates should improve database performance and query speed for common calculations.

In our case, we want to maintain a continuous aggregation for the daily change in confirmed cases. Let's look at this continuous aggregation query:

CREATE VIEW daily_change
WITH (timescaledb.continuous, timescaledb.refresh_lag = '-2 days')
AS
SELECT
  country_region,
  province_state,
  time_bucket('2 days', observation_date) as bucket,
  first(confirmed, observation_date) as yesterday,
  last(confirmed, observation_date) as today,
  last(confirmed, observation_date) - first(confirmed, observation_date) as change
FROM
  covid19_ts
GROUP BY country_region, province_state, bucket;

The first line of this query creates a continuous aggregation named daily_change. We then select a time_bucket with an interval of two days calculated using the covid19_ts.observation_date field.

In our table, we'll create a yesterday and today column, as well as a change column that represents the delta between the two.

Under normal circumstances with large amounts of data, TimescaleDB calculates continuous aggregates in the background, but if you want to see the result immediately, you can force it:

ALTER VIEW daily_change SET (timescaledb.refresh_interval = '1 hour');
ALTER VIEW daily_change SET (timescaledb.refresh_lag = '-2 days');
REFRESH MATERIALIZED VIEW daily_change;

We can run this continuous aggregation with a simple SQL query:

SELECT * FROM daily_change;

The result of that query should look something like this:

          day           | yesterday | today  | change |                  location                   
------------------------+-----------+--------+--------+---------------------------------------------
 2020-01-22 00:00:00+00 |         2 | [null] | [null] | 
 2020-01-22 00:00:00+00 |         1 |      9 |      8 | Anhui
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Australia
 2020-01-22 00:00:00+00 |        14 |     22 |      8 | Beijing
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Brazil
 2020-01-22 00:00:00+00 |         6 |      9 |      3 | Chongqing
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Colombia
 2020-01-22 00:00:00+00 |         1 |      5 |      4 | Fujian
 2020-01-22 00:00:00+00 |    [null] |      2 | [null] | Gansu
 2020-01-22 00:00:00+00 |        26 |     32 |      6 | Guangdong

We can also incorporate the continuous aggregation in other queries, such as the one we used earlier to see the rate of change in confirmed cases near Seattle, Washington.

SELECT bucket, 
       yesterday, 
       today, 
       change, 
       province_state, 
       country_region
FROM daily_change
WHERE province_state IN (
    SELECT province_state
    FROM covid19_locations
    WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000
)
GROUP BY bucket, 
         yesterday, 
         today,  
         change, 
         province_state, 
         country_region
ORDER BY bucket;

Our output should look like this:

         bucket         | yesterday | today | change |    province_state    | country_region 
------------------------+-----------+-------+--------+----------------------+----------------
 2020-02-29 00:00:00+00 |         1 |     2 |      1 | Snohomish County, WA | US
 2020-03-02 00:00:00+00 |         4 |     6 |      2 | Snohomish County, WA | US
 2020-03-02 00:00:00+00 |        14 |    21 |      7 | King County, WA      | US
 2020-03-04 00:00:00+00 |         8 |     8 |      0 | Snohomish County, WA | US
 2020-03-04 00:00:00+00 |        31 |    31 |      0 | King County, WA      | US
(5 rows)

Conclusion

Data gives us insight into the world around us and understanding data enables us to communicate these insights to many people. We encourage you to explore the dataset and share your dashboards and insights on Twitter using #Covid19Data (@-mention @TimescaleDB if you have questions or comments).

If you need help using TimescaleDB or writing visualizations of your own, follow the Hello, Timescale! tutorial. And, join our Slack to ask questions and get help from the global community of Timescale users (our engineering team is also active on all channels).

Finally, no matter where you are, take prudent actions to prepare yourself and your family. Please follow the guidelines and instructions from the World Health Organization.