# Sites: pharma sites

The pharma sites commands, with each dataset's columns, units and source credit.

A clinical trial site as registrants spelled it: a facility with its location, the trials registered there and the institution's company where the name matches an organization.

Id: A site key, the 32-character id in the web page address (/sites/<key>). A key names one facility spelling from the registry; every site dataset takes this same key.

## Commands

| Command | Reads |
| --- | --- |
| pharma sites list | Sites |
| pharma sites get <id> | One site and the datasets reachable from it |
| pharma sites get <id> site-beds-yearly | Site beds by year |
| pharma sites get <id> site-cro-trials | Site CRO trials |
| pharma sites get <id> site-hospitals | Site hospitals |
| pharma sites get <id> site-investigators | Site investigators |
| pharma sites get <id> site-trial-conditions | Site trial conditions |
| pharma sites get <id> site-trial-drugs | Site trial drugs |
| pharma sites get <id> site-trials | Site trials |

Every command takes the paging, sorting and output flags described on the CLI page; the CLI prints the source credit with every result.

- [Pharma Observatory CLI](https://pharmaobservatory.com/cli)

## Sites

**Command**

```sh
pharma sites list
```

The sites object type's primary dataset: one row per site record, a facility as registrants spelled it, with its location, trial counts and the company of the same name where one exists. Rows carry latitude and longitude for the map tool.

One row per: site record (a facility as registered, not a physical building). Key columns: `site_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY); GeoNames (CC BY 4.0); Amazon Location Service (AWS Service Terms)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry; the id the site page address carries. |
| site_name | text |  | The facility name as filed in the registry. |
| city | text |  | City as filed. |
| state | text |  | State or province as filed. |
| country | text |  | Country name as filed. |
| latitude | numeric | degrees | Latitude of the finest coordinate the record has. |
| longitude | numeric | degrees | Longitude of the finest coordinate the record has. |
| geo_precision | text |  | How finely the coordinate places the site (city, postal and so on). |
| coord_source | text |  | The source of the coordinate: aact (the registry's own), geonames (GeoNames) or als (Amazon Location Service). |
| is_location_source_asserted_only | boolean |  | True when the city the registry gave is a location that only the registry asserts for the institution. |
| site_org_id | text |  | Legal-entity level: the organization record the site's institution resolved to (null when unresolved). |
| site_company_id | text |  | Company level: the company the site's institution belongs to (null when unresolved). |
| site_company_name | text |  | Display name of the site's institution's company. |
| site_group_id | text |  | Group level: the site's institution's current ultimate parent company; a root company's group is itself. |
| site_group_name | text |  | Display name of the site's institution's group. |
| n_trials | bigint | trials | Registered trials that list the site. |
| n_active | bigint | trials | Those trials whose status is ongoing. |
| first_year | integer | calendar year | Year of the earliest trial start at the site. |
| last_year | integer | calendar year | Year of the latest trial start at the site. |

## Site beds by year

**Command**

```sh
pharma sites get <id> site-beds-yearly
```

The certified bed count of the hospital a site matches in the hospital registry of the Centers for Medicare & Medicaid Services (CMS), one row per site and registry year.

One row per: site and registry year. Key columns: `site_id`, `year`.

Source: CMS hospital registry, US Centers for Medicare & Medicaid Services (Public domain)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| year | integer | calendar year | The registry year. |
| ccn | text |  | CMS Certification Number of the matched hospital. |
| bed_count | integer | beds | Certified bed count that year. |
| is_teaching | boolean |  | Whether the hospital was a teaching hospital that year. |
| pos_vintage | text |  | The Provider of Services file vintage the row comes from. |
| as_of | date |  | The as-of date of that file. |

## Site CRO trials

**Command**

```sh
pharma sites get <id> site-cro-trials
```

The contract research organization (CRO) inferred for each trial at a site, one row per site, trial, CRO and attribution method. Filter by CRO to list the sites it has trials at.

One row per: site, trial, CRO and attribution method. Key columns: `site_id`, `trial_id`, `cro_org_id`, `cro_attribution_method`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| trial_id | text |  | The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number). |
| cro_org_id | text |  | Legal-entity level: the CRO's organization record. |
| cro_name | text |  | The CRO organization's name. |
| cro_company_id | text |  | Company level: the company the CRO belongs to (null when unresolved). |
| cro_company_name | text |  | Display name of the CRO's company. |
| cro_group_id | text |  | Group level: the CRO's current ultimate parent company; a root company's group is itself. |
| cro_group_name | text |  | Display name of the CRO's group. |
| cro_attribution_method | text |  | How the CRO was inferred (sponsor_match, collaborator_match or llm_extraction). |
| cro_confidence | numeric |  | Confidence of the inference, from 0 to 1. |
| overall_status | text |  | The registry's overall status of the trial. |
| start_year | integer | calendar year | Year of the trial start. |

## Site hospitals

**Command**

```sh
pharma sites get <id> site-hospitals
```

The US hospital a site record matches in the hospital registry of the Centers for Medicare & Medicaid Services (CMS), one row per matched site, with the hospital's type, ownership and beds.

One row per: site matched to a CMS hospital. Key columns: `site_id`.

Source: CMS hospital registry, US Centers for Medicare & Medicaid Services (Public domain); Amazon Location Service (AWS Service Terms)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| ccn | text |  | CMS Certification Number of the matched hospital. |
| hospital_name | text |  | The hospital's name in the CMS registry. |
| hospital_type | text |  | The CMS hospital type. |
| ownership | text |  | The CMS ownership category. |
| bed_count | integer | beds | Certified bed count in the latest registry file. |
| is_teaching | boolean |  | Whether the hospital is a teaching hospital. |
| hospital_latitude | numeric | degrees | Latitude of the hospital. |
| hospital_longitude | numeric | degrees | Longitude of the hospital. |
| geocode_method | text |  | How the hospital's coordinate was placed. |
| ccn_n_trials | bigint | trials | Registered trials across the hospital's matched site records. |
| ccn_n_site_variants | bigint | site records | Site records matched to the same hospital. |
| is_primary_site | boolean |  | True for the matched site record with the most trials for this hospital. |

## Site investigators

**Command**

```sh
pharma sites get <id> site-investigators
```

The investigators named on the trials at a site, one row per site and person, ranked with principal investigators first and then by trial count.

One row per: site and investigator. Key columns: `site_id`, `investigator_person_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| investigator_person_name | text |  | The investigator's name as filed. |
| investigator_person_id | text |  | The investigator's person key. |
| investigator_affiliation | text |  | The affiliation as filed. |
| n_trials | bigint | trials | Trials at the site that name the investigator. |
| is_principal_investigator | boolean |  | True when the investigator is named principal investigator on a trial at the site. |
| site_rank | bigint |  | Rank within the site: principal investigators first, then trial count. |

## Site trial conditions

**Command**

```sh
pharma sites get <id> site-trial-conditions
```

The indications studied in the trials at a site, one row per site, trial and condition. Group by condition with a distinct trial count for which indications a site has trialed.

One row per: site, trial and condition. Key columns: `site_id`, `trial_id`, `condition_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| trial_id | text |  | The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number). |
| condition_id | text |  | The condition key (a MeSH, Medical Subject Headings, descriptor id). |
| condition_name | text |  | The condition's name. |
| overall_status | text |  | The registry's overall status of the trial. |
| start_year | integer | calendar year | Year of the trial start. |

## Site trial drugs

**Command**

```sh
pharma sites get <id> site-trial-drugs
```

The drugs studied in the trials at a site, one row per site, trial and drug. Group by drug with a distinct trial count for which drugs a site has trialed.

One row per: site, trial and drug. Key columns: `site_id`, `trial_id`, `drug_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| trial_id | text |  | The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number). |
| drug_id | text |  | The drug key. |
| drug_name | text |  | The drug's name. |
| overall_status | text |  | The registry's overall status of the trial. |
| start_year | integer | calendar year | Year of the trial start. |

## Site trials

**Command**

```sh
pharma sites get <id> site-trials
```

Which trials are registered at a site, one row per site and trial, with the trial's lead sponsor. Group by phase, status or start year for what a site has run, or by lead sponsor for who runs trials there.

One row per: site and trial. Key columns: `site_id`, `trial_id`.

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

| Column | Type | Unit | Description |
| --- | --- | --- | --- |
| site_id | text |  | The site key: one facility spelling from the registry. |
| trial_id | text |  | The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number). |
| trial_title | text |  | The trial's brief title. |
| study_type | text |  | INTERVENTIONAL or OBSERVATIONAL. |
| phase | text |  | The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those. |
| phase_label | text |  | The trial phase as a display label (Phase 2, Phase 3, Observational, Not applicable, Expanded access, Unknown). The same trial's phase column holds the registry code. |
| overall_status | text |  | The registry's overall status. |
| start_date | date |  | Trial start date. |
| start_year | integer | calendar year | Year of the trial start. |
| enrollment | integer | participants | Enrollment as registered for the whole trial. |
| lead_sponsor_name | text |  | The lead sponsor's name as filed. |
| lead_sponsor_org_id | text |  | Legal-entity level: the organization record the lead sponsor resolved to (null when unresolved). |
| lead_sponsor_company_id | text |  | Company level: the company the lead sponsor belongs to (null when unresolved). |
| lead_sponsor_company_name | text |  | Display name of the lead sponsor's company. |
| lead_sponsor_group_id | text |  | Group level: the lead sponsor's current ultimate parent company; a root company's group is itself. |
| lead_sponsor_group_name | text |  | Display name of the lead sponsor's group. |