Bedtools Migration Guide
========================

This guide maps bedtools commands to their GIQL equivalents. If you're familiar
with bedtools and want to replicate specific operations in GIQL, use this
reference to find the corresponding query patterns.

Quick Reference Table
---------------------

.. list-table::
   :header-rows: 1
   :widths: 35 45 20

   * - Bedtools Command
     - GIQL Equivalent
     - Recipe
   * - ``intersect -a A -b B``
     - ``SELECT DISTINCT a.* FROM a, b WHERE a.interval INTERSECTS b.interval``
     - :ref:`intersect-basic`
   * - ``intersect -a A -b B -wa``
     - ``SELECT a.* FROM a, b WHERE a.interval INTERSECTS b.interval``
     - :ref:`intersect-wa`
   * - ``intersect -a A -b B -wb``
     - ``SELECT b.* FROM a, b WHERE a.interval INTERSECTS b.interval``
     - :ref:`intersect-wb`
   * - ``intersect -a A -b B -wa -wb``
     - ``SELECT a.*, b.* FROM a, b WHERE a.interval INTERSECTS b.interval``
     - :ref:`intersect-wawb`
   * - ``intersect -a A -b B -v``
     - ``SELECT a.* FROM a LEFT JOIN b ... WHERE b.chrom IS NULL``
     - :ref:`intersect-v`
   * - ``intersect -a A -b B -u``
     - ``SELECT DISTINCT a.* FROM a JOIN b ...``
     - :ref:`intersect-u`
   * - ``intersect -a A -b B -c``
     - ``SELECT a.*, COUNT(b.name) ... GROUP BY ...``
     - :ref:`intersect-c`
   * - ``intersect -a A -b B -wo``
     - ``SELECT a.*, b.*, (overlap calculation) ...``
     - :ref:`intersect-wo`
   * - ``intersect -a A -b B -loj``
     - ``SELECT a.*, b.* FROM a LEFT JOIN b ...``
     - :ref:`intersect-loj`
   * - ``closest -a A -b B -k N``
     - ``CROSS JOIN LATERAL NEAREST(b, reference := a.interval, k := N)``
     - :ref:`closest-k`
   * - ``closest -a A -b B -d``
     - ``SELECT ..., DISTANCE(a.interval, b.interval) ...``
     - :ref:`closest-d`
   * - ``cluster -i A``
     - ``SELECT *, CLUSTER(interval) AS cluster_id FROM a``
     - :ref:`cluster-basic`
   * - ``cluster -i A -d N``
     - ``SELECT *, CLUSTER(interval, N) AS cluster_id FROM a``
     - :ref:`cluster-d`
   * - ``merge -i A``
     - ``SELECT MERGE(interval) FROM a``
     - :ref:`merge-basic`
   * - ``merge -i A -d N``
     - ``SELECT MERGE(interval, N) FROM a``
     - :ref:`merge-d`
   * - ``merge -i A -c 1 -o count``
     - ``SELECT MERGE(interval), COUNT(*) FROM a``
     - :ref:`merge-count`

bedtools intersect
------------------

.. _intersect-basic:

Default: Report overlaps between A and B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed

**GIQL:**

.. code-block:: sql

   SELECT DISTINCT a.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval

.. _intersect-wa:

``-wa``: Write original A entry for each overlap
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -wa

**GIQL:**

.. code-block:: sql

   SELECT a.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval

.. _intersect-wb:

``-wb``: Write original B entry for each overlap
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -wb

**GIQL:**

.. code-block:: sql

   SELECT b.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval

.. _intersect-wawb:

``-wa -wb``: Write both A and B entries
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -wa -wb

**GIQL:**

.. code-block:: sql

   SELECT a.*, b.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval

.. _intersect-v:

``-v``: Report A entries with NO overlap in B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -v

**GIQL:**

.. code-block:: sql

   SELECT a.*
   FROM features_a a
   LEFT JOIN features_b b ON a.interval INTERSECTS b.interval
   WHERE b.chrom IS NULL

.. _intersect-u:

``-u``: Report A entries with ANY overlap (unique)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -u

**GIQL:**

.. code-block:: sql

   SELECT DISTINCT a.*
   FROM features_a a
   INNER JOIN features_b b ON a.interval INTERSECTS b.interval

.. _intersect-c:

``-c``: Count B overlaps for each A feature
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -c

**GIQL:**

.. code-block:: sql

   SELECT a.*, COUNT(b.name) AS overlap_count
   FROM features_a a
   LEFT JOIN features_b b ON a.interval INTERSECTS b.interval
   GROUP BY a.chrom, a.start, a.end, a.name, a.score, a.strand

.. _intersect-wo:

``-wo``: Write overlap amount in base pairs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -wo

**GIQL:**

.. code-block:: sql

   SELECT
       a.*,
       b.*,
       (LEAST(a.end, b.end) - GREATEST(a.start, b.start)) AS overlap_bp
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval

.. _intersect-wao:

``-wao``: Write overlap amount for ALL A features
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -wao

**GIQL:**

.. code-block:: sql

   SELECT
       a.*,
       b.*,
       CASE
           WHEN b.chrom IS NULL THEN 0
           ELSE LEAST(a.end, b.end) - GREATEST(a.start, b.start)
       END AS overlap_bp
   FROM features_a a
   LEFT JOIN features_b b ON a.interval INTERSECTS b.interval

.. _intersect-loj:

``-loj``: Left outer join
~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -loj

**GIQL:**

.. code-block:: sql

   SELECT a.*, b.*
   FROM features_a a
   LEFT JOIN features_b b ON a.interval INTERSECTS b.interval

``-s``: Same strand overlaps only
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -s

**GIQL:**

.. code-block:: sql

   SELECT a.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval
     AND a.strand = b.strand

``-S``: Opposite strand overlaps only
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -S

**GIQL:**

.. code-block:: sql

   SELECT a.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval
     AND a.strand != b.strand
     AND a.strand IN ('+', '-')
     AND b.strand IN ('+', '-')

``-f``: Minimum overlap fraction of A
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -f 0.5

**GIQL:**

.. code-block:: sql

   SELECT a.*
   FROM features_a a, features_b b
   WHERE a.interval INTERSECTS b.interval
     AND (
         LEAST(a.end, b.end) - GREATEST(a.start, b.start)
     ) >= 0.5 * (a.end - a.start)

``-r``: Reciprocal overlap
~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools intersect -a file_a.bed -b file_b.bed -f 0.5 -r

**GIQL:**

.. code-block:: sql

   WITH overlap_calcs AS (
       SELECT
           a.*,
           (LEAST(a.end, b.end) - GREATEST(a.start, b.start)) AS overlap_bp,
           (a.end - a.start) AS a_length,
           (b.end - b.start) AS b_length
       FROM features_a a, features_b b
       WHERE a.interval INTERSECTS b.interval
   )
   SELECT chrom, start, end, name, score, strand
   FROM overlap_calcs
   WHERE overlap_bp >= 0.5 * a_length
     AND overlap_bp >= 0.5 * b_length

bedtools closest
----------------

.. _closest-k:

``-k``: Find k nearest features
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools closest -a peaks.bed -b genes.bed -k 3

**GIQL:**

.. code-block:: sql

   SELECT
       peaks.name AS peak,
       nearest.name AS gene,
       nearest.distance
   FROM peaks
   CROSS JOIN LATERAL NEAREST(genes, reference := peaks.interval, k := 3) AS nearest
   ORDER BY peaks.name, nearest.distance

.. _closest-d:

``-d``: Report distance
~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools closest -a peaks.bed -b genes.bed -d

**GIQL:**

.. code-block:: sql

   SELECT
       a.name AS peak,
       b.name AS gene,
       DISTANCE(a.interval, b.interval) AS distance
   FROM peaks a
   CROSS JOIN genes b
   WHERE a.chrom = b.chrom
   ORDER BY a.name, distance

Or using NEAREST for just the closest:

.. code-block:: sql

   SELECT
       peaks.name AS peak,
       nearest.name AS gene,
       nearest.distance
   FROM peaks
   CROSS JOIN LATERAL NEAREST(genes, reference := peaks.interval, k := 1) AS nearest

``-s``: Same strand only
~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools closest -a peaks.bed -b genes.bed -s -k 3

**GIQL:**

.. code-block:: sql

   SELECT
       peaks.name,
       nearest.name AS gene,
       nearest.distance
   FROM peaks
   CROSS JOIN LATERAL NEAREST(
       genes,
       reference := peaks.interval,
       k := 3,
       stranded := true
   ) AS nearest
   ORDER BY peaks.name, nearest.distance

bedtools cluster
----------------

.. _cluster-basic:

Basic clustering
~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools cluster -i features.bed

**GIQL:**

.. code-block:: sql

   SELECT
       *,
       CLUSTER(interval) AS cluster_id
   FROM features
   ORDER BY chrom, start

.. _cluster-d:

``-d``: Cluster with distance parameter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools cluster -i features.bed -d 1000

**GIQL:**

.. code-block:: sql

   SELECT
       *,
       CLUSTER(interval, 1000) AS cluster_id
   FROM features
   ORDER BY chrom, start

``-s``: Strand-specific clustering
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools cluster -i features.bed -s

**GIQL:**

.. code-block:: sql

   SELECT
       *,
       CLUSTER(interval, stranded := true) AS cluster_id
   FROM features
   ORDER BY chrom, strand, start

bedtools merge
--------------

.. _merge-basic:

Basic merge
~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed

**GIQL:**

.. code-block:: sql

   SELECT MERGE(interval)
   FROM features

.. _merge-d:

``-d``: Merge with distance parameter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed -d 1000

**GIQL:**

.. code-block:: sql

   SELECT MERGE(interval, 1000)
   FROM features

``-s``: Strand-specific merge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed -s

**GIQL:**

.. code-block:: sql

   SELECT MERGE(interval, stranded := true)
   FROM features

.. _merge-count:

``-c -o count``: Count merged features
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed -c 1 -o count

**GIQL:**

.. code-block:: sql

   SELECT
       MERGE(interval),
       COUNT(*) AS feature_count
   FROM features

``-c -o mean``: Average score
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed -c 5 -o mean

**GIQL:**

.. code-block:: sql

   SELECT
       MERGE(interval),
       AVG(score) AS avg_score
   FROM features

``-c -o collapse``: Collect names
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Bedtools:**

.. code-block:: bash

   bedtools merge -i features.bed -c 4 -o collapse

**GIQL:**

.. code-block:: sql

   SELECT
       MERGE(interval),
       STRING_AGG(name, ',') AS feature_names
   FROM features

Key Differences from Bedtools
-----------------------------

1. **SQL-based syntax**: GIQL uses SQL syntax, which may be more familiar to
   users with database experience and allows integration with other SQL features.

2. **Explicit joins**: Instead of implicit A/B file relationships, GIQL uses
   explicit JOIN syntax, making the relationship between tables clearer.

3. **Flexible output**: SQL's SELECT clause gives you full control over which
   columns to return and how to format them.

4. **Built-in aggregation**: SQL's GROUP BY and aggregate functions (COUNT, AVG,
   SUM, etc.) are available directly, without needing separate post-processing.

5. **Database integration**: GIQL queries can be run against database tables, 
   enabling integration with other data and persistence of results.

6. **Multi-backend support**: The same GIQL query can run on DuckDB, SQLite,
   or other supported backends without modification.