[온실가스 프로젝트] DB - 3 (테이블 및 데이터)

온실가스로 뭐할지 생각했던 테이블구조에서 크게 다를게 없기 때문에 이대로 구현하고자 한다.

 

# 사업정보
CREATE TABLE project (
    project_code INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL
);

# 지역정보
CREATE TABLE region (
    region_code INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

# 상세사업정보
CREATE TABLE project_detail (
    project_detail_code VARCHAR(50) PRIMARY KEY,
    project_code varchar(50) NOT NULL,
    region_code INT NOT NULL,
    name VARCHAR(100),
    x_coord DOUBLE,
    y_coord DOUBLE,
    FOREIGN KEY (project_code) REFERENCES project(project_code),
    FOREIGN KEY (region_code) REFERENCES region(region_code)
);

# 온실가스배출량
CREATE TABLE emission (
    project_detail_code VARCHAR(50) PRIMARY KEY,
    construction_co2 DECIMAL(10,2),
    construction_ch4 DECIMAL(10,2),
    construction_n2o DECIMAL(10,2),
    construction_etc DECIMAL(10,2),
    operation_co2 DECIMAL(10,2),
    operation_ch4 DECIMAL(10,2),
    operation_n2o DECIMAL(10,2),
    operation_etc DECIMAL(10,2),
    note VARCHAR(255),
    FOREIGN KEY (project_detail_code) REFERENCES project_detail(project_detail_code)
);

# 배출등급구간
CREATE TABLE emission_level (
    gas_type VARCHAR(20),
    level_name VARCHAR(20),
    min_value DECIMAL(10,2),
    max_value DECIMAL(10,2),
    level_label VARCHAR(20),
    description TEXT,
    PRIMARY KEY (gas_type, level_name)
);

 

쿼리 생성으로는 이렇게 하면 되는데, JPA를 사용해보고자 한다.

 

 

---

 

 

테이블 생성 with JPA

@NoArgsConstructor
@Getter
@Entity
public class Project {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer projectCode;

    @Column(length = 50, nullable = false)
    private String type;

    @Column(length = 100, nullable = false)
    private String name;

    @Builder
    public Project(Integer projectCode, String type, String name) {
        this.projectCode = projectCode;
        this.type = type;
        this.name = name;
    }
}

public interface ProjectRepository extends JpaRepository<Project, Integer> {
}

 

클래스를 추가하고 애플리케이션을 실행시켜보니 테이블이 생성됐다!

정상적으로 되는 걸 확인했으니 나머지도 만든다.

 

(자세한 코드 [여기] 참고)

 

 

 

 

테이블을 만들었으니 데이터를 넣어보자.

API 를 호출한 데이터를 가공해서 각 테이블에 맞게 넣어야한다.

 

 

---

 

 

데이터가공

def enhanced_extract_project_name(detail_name: str) -> str:
    mapping = [
        (r'송전선로', '송전선로건설사업'),
        (r'석산개발|채석단지|토석채취', '석산/채석개발사업'),
        (r'골프장|컨트리클럽|18홀|9홀|대중골프장', '골프장개발사업'),
        (r'택지개발|도시개발|공공주택지구|주택단지|국민임대주택단지', '택지/주택개발사업'),
        (r'복선전철|경전철|전철', '철도건설사업'),
        (r'조성사업', '개발조성사업'),
        (r'국도건설공사|도로.*확장|도로.*포장|도로.*건설|지방도|국가지원지방도', '도로확포장공사'),
        (r'도로개설|우회도로|도로 축조|도로.*개설|도로4차선|고속화도로', '도로개설공사'),
        (r'대교|교량|교-비', '교량건설공사'),
        (r'IC|인터체인지|진입도로', 'IC개설공사'),
        (r'터널', '터널공사'),
        (r'매립', '매립공사'),
        (r'하수|폐수|정화조|오수', '하수처리시설'),
        (r'폐기물|소각장', '폐기물처리시설'),
        (r'발전소|에너지', '발전소건설사업'),
        (r'항만|부두', '항만개발사업'),
        (r'댐|저수지|수자원', '수자원개발사업'),
    ]
    for pattern, label in mapping:
        if re.search(pattern, detail_name):
            return label
    return '기타'

먼저 챗지피티를 어르고 달래서 분류할 수 있는 패턴을 찾아낸다.

한번으로는 대강 알아보기 때문에, 기타로 분류된걸 점점 줄여나가는 방식으로 집요하게 가야한다.

 

 

 

 

from pyproj import Transformer
import requests

transformer = Transformer.from_crs("EPSG:5179", "EPSG:4326")
lon, lat = transformer.transform(1698307.346, 1117685.627) # y, x
print(f"변환된 위경도: 위도 {lat}, 경도 {lon}")

KAKAO_REST_API_KEY = "KAKAO_REST_API_KEY"
def convert_coordinates_to_address(lat, lon):
    y, x = str(lat), str(lon)
    url = 'https://dapi.kakao.com/v2/local/geo/coord2address.json?x={}&y={}'.format(x, y)
    header = {'Authorization': 'KakaoAK ' + KAKAO_REST_API_KEY}

    r = requests.get(url, headers=header)
    if r.status_code != 200:
        print("API 호출 실패")
        return None

    data = r.json()
    documents = data.get("documents", [])
    if not documents:
        print("좌표에 해당하는 주소 정보 없음")
        return None

    doc = documents[0]
    address = doc.get("address", {}).get('address_name', "")
    depth1 = doc.get("address", {}).get('region_1depth_name', "")
    depth2 = doc.get("address", {}).get('region_2depth_name', "")

    return address, depth1, depth2

주소를 알기 위해 카카오 API를 사용했다.

사실 정확한 주소까지 나올줄 모르고 region 테이블을 간단하게 만들었는데, 

상세주소가 나와서 project_deatil에 address 컬럼 추가하고, region에는 depth1~2를 만들었다. ([여기] 참고)

 

"https://www.google.com/maps?q={},{}".format(lon, lat)

해당 위도, 경도가 맞는지 확인하려면 주소창에 해당 포맷을 넣어보면 된다.

 

다만 이런식으로 바다에 찍혀있는 경우는 주소가 안나온다;;

 

 

 

 

 

import pandas as pd
from pyproj import Transformer
import requests
import time


# 🔹 설정: 파일 경로와 API 키
file_path = "./온실가스정보서비스_참고문서/최종재분류_환경영향평가_사업코드표.csv"
output_path = "./온실가스정보서비스_참고문서/좌표+주소추가_환경영향평가_사업코드표.csv"
KAKAO_REST_API_KEY = "KAKAO_REST_API_KEY"

# 🔹 1. 파일 불러오기
df = pd.read_csv(file_path, encoding='utf-8-sig')

# 🔹 2. 좌표 변환기 (EPSG:5179 → 4326 위도/경도)
transformer = Transformer.from_crs("EPSG:5179", "EPSG:4326")

def convert_coords(row):
    try:
        lon, lat = transformer.transform(row['y_coord'], row['x_coord'])
        return pd.Series([lat, lon])
    except:
        return pd.Series([None, None])

df[['latitude', 'longitude']] = df.apply(convert_coords, axis=1)

# 🔹 3. Kakao API로 주소 추출
def convert_coordinates_to_address(lat, lon):
    url = f'https://dapi.kakao.com/v2/local/geo/coord2address.json?x={lat}&y={lon}'
    headers = {'Authorization': f'KakaoAK {KAKAO_REST_API_KEY}'}

    try:
        r = requests.get(url, headers=headers)
        if r.status_code != 200:
            return "", "", ""

        data = r.json()
        doc = data.get("documents", [{}])[0]
        address = doc.get("address", {}).get("address_name", "")
        depth1 = doc.get("address", {}).get("region_1depth_name", "")
        depth2 = doc.get("address", {}).get("region_2depth_name", "")
        return address, depth1, depth2

    except Exception:
        return "", "", ""

# 🔹 4. 주소 추가 실행
addresses = []
for idx, row in df.iterrows():
    if pd.notna(row['latitude']) and pd.notna(row['longitude']):
        addr = convert_coordinates_to_address(row['latitude'], row['longitude'])
        addresses.append(addr)
    else:
        addresses.append(("", "", ""))
    time.sleep(0.2)  # ✅ 호출 간 딜레이 (Kakao API 제한 방지)

df[['address', 'depth1', 'depth2']] = pd.DataFrame(addresses, index=df.index)

# 🔹 5. 저장
df.to_csv(output_path, index=False, encoding='utf-8-sig')
print(f"✅ 변환 완료 → 저장 경로: {output_path}")

주소까지 CSV 에 저장해서 디비에 저장해보자.

 

 

 

 

생각보다 4775개 중 302개가 주소가 없다. 

도로나 도시 같은건 특정 주소지가 나와야하는데, 좌표가 잘못 취합된건가... 

 

아무튼 데이터 수가 적어서 API 한도를 넘지 않았다!

 

 

---

 

 

데이터 넣기

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://user:user@localhost:3306/carbon_scope?charset=utf8mb4")

# region insert
region_df.to_sql('region', con=engine, if_exists='append', index=False)

# project insert
project_df = df_merged[['project_type', 'project_name']].drop_duplicates().rename(columns={'project_name': 'name', 'project_type': 'type'}).sort_values(by=['type', 'name'])
project_df.to_sql('project', con=engine, if_exists='append', index=False)

# project_detail insert
detail_df = df_merged[['project_detail_code', 'project_code', 'region_code', 'raw_project_name', 'address']].rename(columns={'raw_project_name': 'name'}).drop_duplicates(subset='project_detail_code')
detail_df.to_sql('project_detail', con=engine, if_exists='append', index=False)

생각한대로 잘 넣었다.

이제 API 데이터만 넣으면 끝이다.

 

 

---

 

 

API 호출

테스트했더니 <OpenAPI_ServiceReponse HTTP ROUTING ERROR> 에러 발생...

문의 후 답장 오는대로 진행 예정.

API 중단된 것만은 아니길 ㅠㅠ..