# How to create burst detection files from MySQL

Here are the steps to generate a burst.in file that can be processed by the burst detection code in Java.

### PowerPoint

D:\Research\Papers\Journals\2014\PNAS-PLOS-JASIST-ISSI\Structural Variation of Transformative Patents [Autosaved].pptx

## Create two auxiliary tables in MySQL

Create two tables before using the sql script below.

### TABLE 1: patent_citations_per_year

patent_id | year | citations
--- | --- | ---
3670344 | 1976 | 1
3583659 | 1976 | 3

#### SQL script:

    CREATE TABLE patent_citations_per_year SELECT cite_to_patent_id as patent_id, cite_from_patent_gyr as year, count(*) as citations FROM patent_citations GROUP BY cited_patent_id, year ORDER BY year;

It took 18min on my W530 laptop.

#### TABLE 2: patent_total_citations

patent_id | total_citations
--- | ---
4683202 | 3139
4683195 | 2735

- Generate burst.in with SQL script (1976-2013)
- Merge header+body burst.in; Remove last line SUB (1 min; 325MB) copy to CiteSpaceIII
- edu.drexel.citespace.terms.Burst.main (9 min 20 sec) 616k patents with citation bursts 34.8MB
- edu.drexel.citespace.util.timeseries.ViewBurstHistory main
- Citespace.io.data.uspto.googlebulk.mysql.USPTO.main Export 1,610 to WoS Format (4 min 42 sec)

## generate burst.header and burst.body with sql

### generate burst.in from mysql

#### Output Format

    year uniq yearID

#### SQL Script

    # need to adjust the beginning year, e.g. 1976
    # need to adjust the ending year, e.g. 2014
    use comets;
    select concat_ws(' ', '#', year, count(*), year-1976) from patent_citations_per_year where year < 2014 group by year order by year
    into outfile 'D:/research/papers/journals/2014/PNAS-PLOS-JASIST-ISSI/sql/burst_in_header' lines terminated by '\n';
    select concat_ws(' ', year-1976, patent_id, citations) from patent_citations_per_year where year < 2014
    into outfile 'D:/research/papers/journals/2014/PNAS-PLOS-JASIST-ISSI/sql/burst_in_body' lines terminated by '\n';

    copy burst_in_header+burst_in_body burst.in

    copy burst.in to D://CiteSpaceIII

    run java with edu.drexel.citespace.terms.Burst as the main class (BurstDetection)