Sites: pharma sites | Pharma Observatory

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

Sites 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

Sites

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)

Columns 19

Sites columns
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

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)

Columns 7

Site beds by year columns
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

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)

Columns 12

Site CRO trials columns
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

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)

Columns 13

Site hospitals columns
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

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)

Columns 7

Site investigators columns
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

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)

Columns 6

Site trial conditions columns
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

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)

Columns 6

Site trial drugs columns
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

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)

Columns 16

Site trials columns
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.

Markdown copy of this page