---
# System prepended metadata

title: OMOP PostgREST

---

# OMOP PostgREST
## 連結
- Swagger: http://10.221.252.51:3080/
- PostgREST API: http://10.221.252.51:3000
- Github: https://github.com/PostgREST/postgrest
- 文檔: https://postgrest.org/en/stable/references/api/tables_views.html#operators

## OMOP 原生 Table 端點
### 範例1 (對照csv)
- 拿`person`全部
    - `curl http://10.221.252.51:3000/person > person.json`
        - ![2025-04-22_15-10](https://hackmd.io/_uploads/SyOZb6Eyxg.png)
- 拿`obseravtion`全部
    - `curl http://10.221.252.51:3000/observation > observation.json`
### 範例2 (person_id篩)
- 取得`person_id = 1`所有`procedure_occurrence`
    - http://10.221.252.51:3000/procedure_occurrence?person_id=eq.1
    - ![2025-04-18_15-19](https://hackmd.io/_uploads/ryWQp_ykee.png)
    - ![2025-04-18_15-21](https://hackmd.io/_uploads/B12d6ukJxl.png)
- 確認`person_id = 1`所有`procedure_occurrenc`回覆`11`筆對照`OMOP`的`11`筆數量正確
    - ![2025-04-18_15-21_1](https://hackmd.io/_uploads/ryqCadkJge.png)
    - ![2025-04-18_15-23](https://hackmd.io/_uploads/Sy51Rdkkgg.png)

### 範例3 (person_id, concept_id篩，日期排序)
- 取得`person_id = 5`中`measurement`最新身高
    - http://10.221.252.51:3000/measurement?person_id=eq.5&measurement_concept_id=eq.903133&order=measurement_date.desc&limit=1
    - ![2025-04-18_15-37](https://hackmd.io/_uploads/SJu4ZY1yge.png)
- 確認`person_id = 5`最新身高與`OMOP`相符
    - ![2025-04-18_15-40](https://hackmd.io/_uploads/B1hkMKk1lx.png)
    - ![2025-04-18_15-34](https://hackmd.io/_uploads/Sy6oeYJJee.png)

## OMOP Stored Procedure
- 輸入病歷號到`patient_id`，拿`PAS`資料表需要欄位
- ![2025-04-29_15-32](https://hackmd.io/_uploads/HJgK2ebA1xg.png)


### 範例1 (申請資料)
- http://10.221.252.51:3000/rpc/get_claim?patient_id=10669860
- ![2025-04-29_14-26](https://hackmd.io/_uploads/r15GZgRJgl.png)
```json=
{
  "applType": 1,
  "funcType": null,
  "medicalLicense": "DOC3511A",
  "docIdCard": "T100163189",
  "docName": "趙灌中",
  "applDate": null,
  "tmhbType": null,
  "oldAcptNo": null,
  "patId": "10669860",
  "patIdSystem": "https://www.vghtpe.gov.tw/",
  "idCard": "L201297732",
  "name": "劉芸伶",
  "birthday": "1954-11-24",
  "gender": "2",
  "weight": "70.200",
  "height": "161.800",
  "pregnant": null
}
```

### 範例2 (影像報告，文字)
- http://10.221.252.51:3000/rpc/get_imaging_report?patient_id=10669860
- ![2025-04-29_14-25](https://hackmd.io/_uploads/SJXJbl01xg.png)
```json=
[
  {
    "idCard": null,
    "medicalLicense": null,
    "docName": null,
    "imgItem": "26072B",
    "imgCategory": "Photographic image",
    "imgResult": "hypodense lesion with hypermetabolism at S5/8 showing \nincreased uptake, r/o recurrent/ residual metastasis.\n> No hypermetabolic lesions are noted elsewhere in the whole \nbody.\n\n\n[ Conclusion ]\n> Right breast cancer s/p treatment.\n> Endometrial cancer s/p hysterectomy + BSO.\n> A lobulated hypodense lesion with hypermetabolism at S5/8 \nshowing increased uptake, r/o recurrent/ residual metastasis.\n\nscanner (since 20191001). Serum glucose level at the time of \nat 60 min post-injection on GE Discovery MI DR PET/CT hybrid \nposition was performed after injection of 8.18 mCi of F-18 FDG \ninjection (mg/dl): 97\n \n\n[ Findings ]\n> Correlated with: 20240904 CT, 20240608 CT, 20231207 PET\n> History of right breast cancer s/p wide excision.\n> Endometrial cancer s/p hysterectomy + BSO.\n> Liver metastases in right lobe s/p treatment; a lobulated \n5301B\nWhole body PET imaging from vertex to upper thigh with arm-up \n[ Procedure ]\n130904\n20240905\n5301B\n \n64\n17:33\n2024/09/05\n[RPTARTICLE] CLOB DATA SAVE TO OTHER TABLE",
    "imgDate": "2024-09-05"
  }
]
```

### 範例3 (放射治療)
- http://10.221.252.51:3000/rpc/get_radiotherapy?patient_id=10669860
- ![2025-04-29_14-33](https://hackmd.io/_uploads/B1rkXgA1xl.png)
```json=
[
  {
    "rt": "DM0",
    "realInspectTime": "2022-08-22",
    "rtDose": 6020,
    "rtUnit": null
  }
]
```

## OMOP Stored Procedure 修正
- 已加入起始與結束日期參數，可供日期區間查詢
- ![image](https://hackmd.io/_uploads/ByZp7Gylxl.png)


### 2 ClaimItem (申請項目)
- 醫師自填，已移除

### 3 Diagnosis
- 簡要病摘: 醫師自填

### 4 CancerStage
- assessItem: TNM 筆誤已更正
- assessScoreString: TNM 個別T, N, M標注
- ![2025-04-30_10-55](https://hackmd.io/_uploads/S1sXZG1gll.png)

### 5 ExaminationReport
- speType: 已更正
- ![2025-04-30_11-28](https://hackmd.io/_uploads/HyaWFG1gee.png)


### 6. ImagingReport (影像報告)
- drugCode: 為健保碼
- 加入`reqNo`
- 移除`get_imaging_report_req`
- ![2025-04-30_11-31](https://hackmd.io/_uploads/Sk03tGJlex.png)
- ![image](https://hackmd.io/_uploads/H1ncLzJexg.png)

### 8. MedicationRequestTreat (用藥品項)
- dose: 非數字部分已排除
- ![image](https://hackmd.io/_uploads/HJdyvzJxxg.png)
- doseUnit: 只留下單位，並排除NULL之狀況
- ![image](https://hackmd.io/_uploads/S1mHLf1egx.png)

### 11 Test
- inspect: inspect拆開成inspectCode(健保碼)和inspectName(項目名)
- consultValueLower: 原始資料非結構，有試圖拆，留下min
- consultValueMax: 原始資料非結構，有試圖拆，留下max
- consultValueCat: x
- consultValueDesc: x
- isGrouped: 已帶入，目前邏輯是reqNo對上>1的檢驗項目歸類為`Y`套組
- ![2025-04-30_11-06](https://hackmd.io/_uploads/ryQ3QGJegl.png)


### 14 Patient
- 有新增
- ![2025-04-30_11-07](https://hackmd.io/_uploads/HyUb4zJxxe.png)

### 15 Practitioner
- 有新增
- ![2025-04-30_11-08](https://hackmd.io/_uploads/BkVIVzylgl.png)
