地點查詢 API

[sudoghut]

以下是 地點查詢 API 中 peoplePlace 與 placeType 的設計細節。拜託建安了！

https://github.com/cbdb-project/cbdb-online-main-server/blob/develop/API.md#%E5%8D%81%E5%9B%9B%E9%80%9A%E9%81%8E%E5%9C%B0%E5%8D%80%E6%9F%A5%E8%A9%A2

peoplePlace：這個 list 是地址 ID，它由 placeType 決定到底要查哪些表中的地址。

individual人 ：用於來自某地（傳記地址、籍貫）的人物。用 peoplePlace 的 ID 查 c_index_addr_id@BIOG_MAIN 的人物即可。有兩個小細節 1）c_index_addr_id 的地名透過關聯 c_addr_id@ADDR_CODES 可以查到 c_name_chn@ADDR_CODES；2）陳諾博士設計的輸出格式是通用性的，其中那些 BIOG_MAIN 無法提供的資訊，譬如 data[i].AssocName 輸出空值即可，下同。

作為參考，這是 Access 版本中 individual 查詢的 VBA 程式碼：（VBA 程式碼基於許多預先構建的查詢表，所以這裡一定僅供邏輯上的參考，下同）

' For the individual

If Me.ChkIndividual.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_addr_chn, x_coord, y_coord, " + _
        "c_rel_code, c_rel_desc, c_rel_chn, c_firstyear, c_lastyear, c_assoc_name, c_assoc_chn, c_assoc_id, c_rel_type ) " + _
        "SELECT ZZZ_BIOG_ADDR_DATA.c_personid, ZZZ_BIOG_MAIN.c_name, ZZZ_BIOG_MAIN.c_name_chn, ZZZ_BIOG_MAIN.c_index_year, " + _
        "ZZZ_BIOG_MAIN.c_dy, ZZZ_BIOG_MAIN.c_dynasty, ZZZ_BIOG_MAIN.c_dynasty_chn, " + _
        "ZZZ_BIOG_ADDR_DATA.c_addr_id, ZZZ_BIOG_ADDR_DATA.c_addr_name, ZZZ_BIOG_ADDR_DATA.c_addr_chn, ZZZ_BIOG_ADDR_DATA.x_coord, ZZZ_BIOG_ADDR_DATA.y_coord, " + _
        "ZZZ_BIOG_ADDR_DATA.c_addr_type, ZZZ_BIOG_ADDR_DATA.c_addr_desc, ZZZ_BIOG_ADDR_DATA.c_addr_desc_chn, ZZZ_BIOG_ADDR_DATA.c_firstyear, " + _
        "ZZZ_BIOG_ADDR_DATA.c_lastyear, '[N/A]' AS c_assoc_name, '[N/A]' AS c_assoc_chn, 0 AS c_assoc_id, 'Biography' as c_rel_type "
    tQueryFromStr = "FROM (ZZZ_BIOG_ADDR_DATA INNER JOIN ZZ_SCRATCH_ADDR ON ZZZ_BIOG_ADDR_DATA.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
        "INNER JOIN ZZZ_BIOG_MAIN ON ZZZ_BIOG_ADDR_DATA.c_personid = ZZZ_BIOG_MAIN.c_personid "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = "WHERE (((ZZZ_BIOG_MAIN.c_index_year)>= " + tFirstYearStr + " And (ZZZ_BIOG_MAIN.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = "WHERE (((ZZZ_BIOG_MAIN.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = "WHERE (((ZZZ_BIOG_MAIN.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_BIOG_MAIN.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN ((ZZZ_BIOG_ADDR_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) INNER JOIN ZZZ_BIOG_MAIN " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_personid = ZZZ_BIOG_MAIN.c_personid) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_MAIN.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN ((ZZZ_BIOG_ADDR_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) INNER JOIN ZZZ_BIOG_MAIN " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_personid = ZZZ_BIOG_MAIN.c_personid) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_MAIN.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_BIOG_MAIN.c_dy) = " + Str(gFromDynasty) + " ) "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN ((ZZZ_BIOG_ADDR_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) INNER JOIN ZZZ_BIOG_MAIN " + _
                "ON ZZZ_BIOG_ADDR_DATA.c_personid = ZZZ_BIOG_MAIN.c_personid) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_MAIN.c_dy "
        Else
            tQueryWhereStr = ""
        End If

    End If

entry入仕：用於查詢某地的入仕人物。用 peoplePlace 的 ID 查 c_entry_addr_id@ENTRY_DATA

作為參考，這是 Access 版本中 entry 查詢的 VBA 程式碼：

' For entry

If Me.ChkEntry.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_addr_chn, x_coord, y_coord, c_firstyear, " + _
        "c_rel_type, c_rel_code, c_rel_desc, c_rel_chn ) " + _
        "SELECT ZZZ_ENTRY_DATA.c_personid, ZZZ_ENTRY_DATA.c_name, ZZZ_ENTRY_DATA.c_index_year, ZZZ_ENTRY_DATA.c_dy, ZZZ_ENTRY_DATA.c_dynasty, ZZZ_ENTRY_DATA.c_dynasty_chn, " + _
        "ZZZ_ENTRY_DATA.c_entry_addr_id, ZZZ_ENTRY_DATA.c_entry_addr_name, " + _
        "ZZZ_ENTRY_DATA.c_entry_addr_chn, ZZZ_ENTRY_DATA.c_entry_xcoord, ZZZ_ENTRY_DATA.c_entry_ycoord, ZZZ_ENTRY_DATA.c_year, " + _
        "'Entry' AS c_rel_type, ZZZ_ENTRY_DATA.c_entry_code, ZZZ_ENTRY_DATA.c_entry_desc, ZZZ_ENTRY_DATA.c_entry_desc_chn "
    tQueryFromStr = "FROM ZZ_SCRATCH_ADDR INNER JOIN ZZZ_ENTRY_DATA ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_ENTRY_DATA.c_entry_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_ENTRY_DATA.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_ENTRY_DATA.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_ENTRY_DATA.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_ENTRY_DATA.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_ENTRY_DATA.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_ENTRY_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_ENTRY_DATA.c_entry_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_ENTRY_DATA.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_ENTRY_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_ENTRY_DATA.c_entry_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_ENTRY_DATA.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_ENTRY_DATA.c_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_ENTRY_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_ENTRY_DATA.c_entry_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_ENTRY_DATA.c_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

    cmdSQL.CommandText = tQueryInsertStr + tQueryFromStr + tQueryWhereStr
    cmdSQL.Execute tRecDeleted
End If

association 社會關係: 查社會關係發生地的人物，用 peoplePlace 的 ID 查 c_addr_id@ASSOC_DATA

作為參考，這是 Access 版本中 association 查詢的 VBA 程式碼：

' For the association

If Me.ChkAssocPlace.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_addr_chn, c_assoc_id, c_assoc_name, " + _
        "c_assoc_chn, c_rel_type, c_rel_code, c_rel_desc, c_rel_chn, x_coord, y_coord ) " + _
        "SELECT ZZZ_NONKIN_BIOG_ADDR.c_personid, ZZZ_NONKIN_BIOG_ADDR.c_person_name, ZZZ_NONKIN_BIOG_ADDR.c_person_name_chn, ZZZ_NONKIN_BIOG_ADDR.c_index_year, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_dy, ZZZ_NONKIN_BIOG_ADDR.c_dynasty, ZZZ_NONKIN_BIOG_ADDR.c_dynasty_chn, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_id, ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_name, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_chn, ZZZ_NONKIN_BIOG_ADDR.c_node_id, ZZZ_NONKIN_BIOG_ADDR.c_node_name, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_node_chn, 'Place of Association' AS c_rel_type, ZZZ_NONKIN_BIOG_ADDR.c_link_code, ZZZ_NONKIN_BIOG_ADDR.c_link_desc, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_link_chn, ADDR_CODES.x_coord, ADDR_CODES.y_coord "
    tQueryFromStr = "FROM ADDR_CODES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_NONKIN_BIOG_ADDR ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_id) " + _
        "ON ADDR_CODES.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_NONKIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_NONKIN_BIOG_ADDR.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ADDR_CODES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_NONKIN_BIOG_ADDR " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_id) " + _
                "ON ADDR_CODES.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ADDR_CODES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_NONKIN_BIOG_ADDR " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_id) " + _
                "ON ADDR_CODES.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_NONKIN_BIOG_ADDR.c_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ADDR_CODES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_NONKIN_BIOG_ADDR " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_NONKIN_BIOG_ADDR.c_assoc_addr_id) " + _
                "ON ADDR_CODES.c_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

officePosting職官: 查詢在某地任職的人物，用 peoplePlace 的 ID 查 c_addr_id@POSTED_TO_ADDR_DATA 即可. 有兩個小細節：首先，任官者的詳細資訊可以透過 c_personid@POSTED_TO_ADDR_DATA 關聯到 c_personid@BIOG_MAIN 進行查詢。其次，關於任官的詳細資訊可以通過 c_posting_id@POSTED_TO_ADDR_DATA 關聯到 c_posting_id@POSTED_TO_OFFICE_DATA 進行查詢。

作為參考，這是 Access 版本中 office 查詢的 VBA 程式碼：

'  for office postings

If Me.ChkOffice.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
            "c_addr_id, c_addr_name, c_addr_chn, x_coord, y_coord, c_rel_type, c_rel_code, c_rel_desc, c_rel_chn, c_firstyear, c_lastyear ) " + _
            "SELECT ZZZ_POSTED_TO_ADDR_DATA.c_personid, ZZZ_POSTED_TO_ADDR_DATA.c_person_name, ZZZ_POSTED_TO_ADDR_DATA.c_person_name_chn, " + _
            "ZZZ_POSTED_TO_ADDR_DATA.c_index_year, ZZZ_POSTED_TO_ADDR_DATA.c_person_dy, ZZZ_POSTED_TO_ADDR_DATA.c_person_dynasty, ZZZ_POSTED_TO_ADDR_DATA.c_person_dy_chn, " + _
            "ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_id, ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_name, " + _
            "ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_chn, ZZZ_POSTED_TO_ADDR_DATA.office_x_coord, " + _
            "ZZZ_POSTED_TO_ADDR_DATA.office_y_coord, 'Office Place' AS c_rel_type, ZZZ_POSTED_TO_ADDR_DATA.c_office_id, " + _
            "ZZZ_POSTED_TO_ADDR_DATA.c_office_trans, ZZZ_POSTED_TO_ADDR_DATA.c_office_chn, ZZZ_POSTED_TO_ADDR_DATA.c_firstyear, ZZZ_POSTED_TO_ADDR_DATA.c_lastyear "
    tQueryFromStr = "FROM ZZ_SCRATCH_ADDR INNER JOIN ZZZ_POSTED_TO_ADDR_DATA ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = "WHERE (((ZZZ_POSTED_TO_ADDR_DATA.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_POSTED_TO_ADDR_DATA.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = "WHERE (((ZZZ_POSTED_TO_ADDR_DATA.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = "WHERE (((ZZZ_POSTED_TO_ADDR_DATA.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_POSTED_TO_ADDR_DATA.c_person_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_POSTED_TO_ADDR_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_POSTED_TO_ADDR_DATA.c_person_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_POSTED_TO_ADDR_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_POSTED_TO_ADDR_DATA.c_person_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_POSTED_TO_ADDR_DATA.c_person_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZ_SCRATCH_ADDR INNER JOIN ZZZ_POSTED_TO_ADDR_DATA " + _
                "ON ZZ_SCRATCH_ADDR.c_addr_id = ZZZ_POSTED_TO_ADDR_DATA.c_office_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_POSTED_TO_ADDR_DATA.c_person_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

    cmdSQL.CommandText = tQueryInsertStr + tQueryFromStr + tQueryWhereStr
    cmdSQL.Execute tRecDeleted
End If

institutional社交機構: 查與某地的社交機構有關的人物， 用 peoplePlace 的 ID 查c_inst_addr_id@SOCIAL_INSTITUTION_ADDR. 之後用 c_inst_code@SOCIAL_INSTITUTION_ADDR 關聯到 c_inst_code@BIOG_INST_DATA 查到人物和時間資訊。有一個細節，地名可以透過 c_inst_addr_id@SOCIAL_INSTITUTION_ADDR 查 c_addr_id@ADDR_CODES 獲得。

作為參考，這是 Access 版本中 institution 查詢的 VBA 程式碼：

' For the institutions

If Me.ChkInstitution.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_assoc_chn, c_assoc_name, " + _
        "c_addr_chn, c_assoc_id, c_firstyear, c_lastyear, c_rel_type, c_rel_code, c_rel_desc, c_rel_chn, x_coord, y_coord ) " + _
        "SELECT ZZZ_BIOG_INST_DATA.c_personid, ZZZ_BIOG_INST_DATA.c_name, ZZZ_BIOG_INST_DATA.c_name_chn, ZZZ_BIOG_INST_DATA.c_index_year, " + _
        "ZZZ_BIOG_INST_DATA.c_dy, ZZZ_BIOG_INST_DATA.c_dynasty, ZZZ_BIOG_INST_DATA.c_dynasty_chn, " + _
        "ZZ_SCRATCH_ADDR.c_addr_id, ZZZ_BIOG_INST_DATA.c_inst_addr_pinyin, ZZZ_BIOG_INST_DATA.c_inst_name_hz, ZZZ_BIOG_INST_DATA.c_inst_name_py, " + _
        "ZZZ_BIOG_INST_DATA.c_inst_addr_chn, 0 AS c_assoc_id, ZZZ_BIOG_INST_DATA.c_bi_begin_year, ZZZ_BIOG_INST_DATA.c_bi_end_year, " + _
        "'Institution' AS c_rel_type, ZZZ_BIOG_INST_DATA.c_bi_role_code, ZZZ_BIOG_INST_DATA.c_bi_role_desc, " + _
        "ZZZ_BIOG_INST_DATA.c_bi_role_chn, ZZZ_BIOG_INST_DATA.inst_xcoord, ZZZ_BIOG_INST_DATA.inst_ycoord "
    tQueryFromStr = "FROM ZZZ_BIOG_INST_DATA INNER JOIN ZZ_SCRATCH_ADDR ON ZZZ_BIOG_INST_DATA.c_inst_addr_id = ZZ_SCRATCH_ADDR.c_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_BIOG_INST_DATA.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_BIOG_INST_DATA.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_BIOG_INST_DATA.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_BIOG_INST_DATA.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_BIOG_INST_DATA.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_BIOG_INST_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_INST_DATA.c_inst_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_INST_DATA.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_BIOG_INST_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_INST_DATA.c_inst_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_INST_DATA.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_BIOG_INST_DATA.c_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_BIOG_INST_DATA INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_BIOG_INST_DATA.c_inst_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_BIOG_INST_DATA.c_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

kinship親屬：查詢某地的親屬關係網絡。用 peoplePlace 的 ID 查 c_index_addr_id@BIOG_MAIN，用 c_personid@BIOG_MAIN 查到 c_kin_id@KIN_DATA 即可。關係類別用 c_kin_code@KIN_DATA 關聯到 c_kincode@KINSHIP_CODES 查c_kinrel_chn@KINSHIP_CODES 和 c_kinrel@KINSHIP_CODES.

作為參考，這是 Access 版本中 kinship 查詢的 VBA 程式碼：

' For kinship

If Me.ChkKin.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_addr_chn, c_assoc_id, c_assoc_name, " + _
        "c_assoc_chn, c_rel_type, c_rel_code, c_rel_desc, c_rel_chn, x_coord, y_coord, c_assoc_index_year, assoc_x_coord, assoc_y_coord ) " + _
        "SELECT ZZZ_KIN_BIOG_ADDR.c_personid, ZZZ_KIN_BIOG_ADDR.c_person_name, ZZZ_KIN_BIOG_ADDR.c_person_name_chn, ZZZ_KIN_BIOG_ADDR.c_index_year, " + _
        "ZZZ_KIN_BIOG_ADDR.c_dy, ZZZ_KIN_BIOG_ADDR.c_dynasty, ZZZ_KIN_BIOG_ADDR.c_dynasty_chn, " + _
        "ZZZ_KIN_BIOG_ADDR.c_node_addr_id, ZZZ_KIN_BIOG_ADDR.c_node_addr_name, ZZZ_KIN_BIOG_ADDR.c_node_addr_chn, ZZZ_KIN_BIOG_ADDR.c_node_id, " + _
        "ZZZ_KIN_BIOG_ADDR.c_node_name, ZZZ_KIN_BIOG_ADDR.c_node_chn, 'Kinship' AS c_rel_type, ZZZ_KIN_BIOG_ADDR.c_link_code, ZZZ_KIN_BIOG_ADDR.c_link_desc, " + _
        "ZZZ_KIN_BIOG_ADDR.c_link_chn, ZZZ_KIN_BIOG_ADDR.x_coord, ZZZ_KIN_BIOG_ADDR.y_coord, ZZZ_KIN_BIOG_ADDR.c_node_index_year, " + _
        "ZZZ_KIN_BIOG_ADDR.node_xcoord, ZZZ_KIN_BIOG_ADDR.node_ycoord "
    tQueryFromStr = "FROM ZZZ_KIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR ON ZZZ_KIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_KIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_KIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_KIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_KIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_KIN_BIOG_ADDR.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_KIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_KIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_KIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_KIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_KIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_KIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_KIN_BIOG_ADDR.c_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_KIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_KIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_KIN_BIOG_ADDR.c_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

associate 社會關係的人：查詢傳記地址（籍貫）為某地人物的社會關係網絡。用 peoplePlace 的 ID 查 c_addr_id@BIOG_ADDR_DATA，用 c_personid@BIOG_ADDR_DATA 查到 c_assoc_id@ASSOC_DATA 表中的人物即可。社會關係可以透過 c_assoc_code@ASSOC_DATA 關聯 c_assoc_code@ASSOC_CODES 查詢 c_assoc_desc@ASSOC_CODES 和 c_assoc_desc_chn@ASSOC_CODES.

作為參考，這是 Access 版本中 associate 查詢的 VBA 程式碼： ' For associates

If Me.ChkAssocPerson.Value Then
    tQueryInsertStr = "INSERT INTO ZZ_PLACE ( c_personid, c_name, c_name_chn, c_index_year, c_dy, c_dynasty, c_dynasty_chn, " + _
        "c_addr_id, c_addr_name, c_addr_chn, c_assoc_id, c_assoc_name, " + _
        "c_assoc_chn, c_rel_type, c_rel_code, c_rel_desc, c_rel_chn, x_coord, y_coord, c_assoc_index_year, assoc_x_coord, assoc_y_coord ) " + _
        "SELECT ZZZ_NONKIN_BIOG_ADDR.c_personid, ZZZ_NONKIN_BIOG_ADDR.c_person_name, ZZZ_NONKIN_BIOG_ADDR.c_person_name_chn, ZZZ_NONKIN_BIOG_ADDR.c_index_year, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_dy, ZZZ_NONKIN_BIOG_ADDR.c_dynasty, ZZZ_NONKIN_BIOG_ADDR.c_dynasty_chn, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_node_addr_id, ZZZ_NONKIN_BIOG_ADDR.c_node_addr_name, ZZZ_NONKIN_BIOG_ADDR.c_node_addr_chn, ZZZ_NONKIN_BIOG_ADDR.c_node_id, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_node_name, ZZZ_NONKIN_BIOG_ADDR.c_node_chn, 'Associate Place' AS c_rel_type, ZZZ_NONKIN_BIOG_ADDR.c_link_code, ZZZ_NONKIN_BIOG_ADDR.c_link_desc, " + _
        "ZZZ_NONKIN_BIOG_ADDR.c_link_chn, ZZZ_NONKIN_BIOG_ADDR.x_coord, ZZZ_NONKIN_BIOG_ADDR.y_coord, ZZZ_NONKIN_BIOG_ADDR.c_node_index_year, " + _
        "ZZZ_NONKIN_BIOG_ADDR.node_xcoord, ZZZ_NONKIN_BIOG_ADDR.node_ycoord "
    tQueryFromStr = "FROM ZZZ_NONKIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR ON ZZZ_NONKIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id "

    tQueryWhereStr = ""

    If tUseFirstYear Or tUseLastYear Then
        If tUseFirstYear And tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + _
                    " And (ZZZ_NONKIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        ElseIf tUseFirstYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)>= " + tFirstYearStr + "))"
        ElseIf tUseLastYear Then
            tQueryWhereStr = tQueryWhereStr + "WHERE (((ZZZ_NONKIN_BIOG_ADDR.c_index_year)<= " + tLastYearStr + "))"
        End If
    ElseIf gUseDynasties Then
            '
        '  five possibilities (all, just from, just to, both from and to, and a cluelessly unset parameter)
        '
        If gFromDynasty = -2 Then
            tQueryWhereStr = "Where ((ZZZ_NONKIN_BIOG_ADDR.c_dy) > 0 ) "
        ElseIf gFromDynasty = -1 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_start)<" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_NONKIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_NONKIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty > 0 And gToDynasty = -1 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_NONKIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_NONKIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        ElseIf gFromDynasty = gToDynasty And gFromDynasty > 0 Then
            tQueryWhereStr = "WHERE ((ZZZ_NONKIN_BIOG_ADDR.c_dy) = " + Str(gFromDynasty) + ") "
        ElseIf gFromDynasty > 0 And gToDynasty > 0 Then
            tQueryWhereStr = "WHERE ((DYNASTIES.c_end)>" + Str(gFromDynastyBegin) + ") AND " + _
                "((DYNASTIES.c_start)<=" + Str(gToDynastyEnd) + ") "
            tQueryFromStr = "FROM DYNASTIES INNER JOIN (ZZZ_NONKIN_BIOG_ADDR INNER JOIN ZZ_SCRATCH_ADDR " + _
                "ON ZZZ_NONKIN_BIOG_ADDR.c_node_addr_id = ZZ_SCRATCH_ADDR.c_addr_id) " + _
                "ON DYNASTIES.c_dy = ZZZ_NONKIN_BIOG_ADDR.c_dy "
        Else
            tQueryWhereStr = ""
        End If
    End If

[s8714081]

20220419新增地點查詢的API，初版完成。 (https://github.com/cbdb-project/cbdb-online-main-server/pull/196)