SKTHON-Hackathon-Oide

Tables (17)

administrative_area

  • id — BIGINT — Primary key — NOT NULL
  • area_type — VARCHAR(16) — NOT NULL
  • code — VARCHAR(20) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • parent_id — BIGINT — Foreign key → administrative_area.id
  • geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

area_business_feature

  • id — BIGINT — Primary key — NOT NULL
  • commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
  • business_category_code — VARCHAR(30) — NOT NULL
  • base_period — DATE — NOT NULL
  • store_count — INTEGER
  • estimated_sales_amount — NUMERIC(18,2)
  • closure_rate — NUMERIC(6,4)
  • competition_score — NUMERIC(8,4)
  • demand_score — NUMERIC(8,4)
  • updated_at — TIMESTAMP — NOT NULL

building

  • id — BIGINT — Primary key — NOT NULL
  • source_building_id — VARCHAR(100) — NOT NULL — Legacy source key; progressively map to gis_building.gis_building_id. GIS attributes are authoritative in gis_building.
  • pnu — VARCHAR(30)
  • legal_dong_code — VARCHAR(20)
  • legal_dong_id — BIGINT — Foreign key → administrative_area.id
  • road_address — VARCHAR(300)
  • jibun_address — VARCHAR(300)
  • building_name — VARCHAR(200)
  • main_use_code — VARCHAR(30)
  • main_use_name — VARCHAR(100)
  • land_area — NUMERIC(14,2)
  • building_area — NUMERIC(14,2)
  • total_floor_area — NUMERIC(14,2)
  • building_coverage_ratio — NUMERIC(6,2)
  • floor_area_ratio — NUMERIC(6,2)
  • ground_floor_count — INTEGER
  • underground_floor_count — INTEGER
  • approval_date — DATE
  • geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

commercial_area

  • id — BIGINT — Primary key — NOT NULL
  • commercial_area_code — VARCHAR(30) — NOT NULL
  • commercial_area_name — VARCHAR(150) — NOT NULL
  • commercial_area_type — VARCHAR(50)
  • geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

commercial_area_building

  • commercial_area_id — BIGINT — Primary key — NOT NULL
  • gis_building_id — BIGINT — Foreign key → gis_building.id — NOT NULL
  • overlap_ratio — NUMERIC(6,5)
  • calculated_at — TIMESTAMP — NOT NULL

commercial_area_feature

  • id — BIGINT — Primary key — NOT NULL
  • commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
  • base_period — DATE — NOT NULL
  • total_floating_population — INTEGER
  • age_20_ratio — NUMERIC(6,4)
  • age_30_ratio — NUMERIC(6,4)
  • avg_rent_per_sqm — NUMERIC(14,2)
  • vacancy_rate — NUMERIC(5,2)
  • residential_population — INTEGER
  • worker_population — INTEGER
  • updated_at — TIMESTAMP — NOT NULL

floating_population_grid

  • id — BIGINT — Primary key — NOT NULL
  • grid_code — VARCHAR(50) — NOT NULL
  • geom — GEOMETRY(POLYGON,5186) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

floating_population_statistic

  • id — BIGINT — Primary key — NOT NULL
  • grid_id — BIGINT — Foreign key → floating_population_grid.id — NOT NULL
  • base_period — DATE — NOT NULL
  • time_slot — VARCHAR(20) — NOT NULL
  • total_population — INTEGER
  • male_population — INTEGER
  • female_population — INTEGER
  • age_10s — INTEGER
  • age_20s — INTEGER
  • age_30s — INTEGER
  • age_40s — INTEGER
  • age_50s — INTEGER
  • age_60_plus — INTEGER
  • residential_population — INTEGER
  • worker_population — INTEGER
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

gis_building

  • id — BIGINT — Primary key — NOT NULL
  • import_id — BIGINT — Foreign key → gis_dataset_import.id — NOT NULL
  • gis_building_id — VARCHAR(50) — NOT NULL
  • ufid — VARCHAR(50)
  • pnu — VARCHAR(19)
  • legal_dong_code — VARCHAR(10)
  • legal_dong_name — VARCHAR(100)
  • sigungu_code — VARCHAR(5)
  • lot_main_no — VARCHAR(10)
  • lot_sub_no — VARCHAR(10)
  • building_category — VARCHAR(50)
  • building_use_code — VARCHAR(20)
  • building_use_name — VARCHAR(100)
  • structure_code — VARCHAR(20)
  • structure_name — VARCHAR(100)
  • approval_date — DATE
  • building_height_m — NUMERIC(10,2)
  • building_area_sqm — NUMERIC(14,2)
  • total_floor_area_sqm — NUMERIC(14,2)
  • roof_height_m — NUMERIC(10,2)
  • above_ground_floor_count — INTEGER
  • below_ground_floor_count — INTEGER
  • geometry — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
  • centroid — GEOMETRY(POINT,5186)
  • source_updated_at — DATE
  • is_current — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • building_id — BIGINT — Foreign key → building.id — NOT NULL — Optional service-domain building; one building can retain multiple GIS import records.

gis_dataset_import

  • id — BIGINT — Primary key — NOT NULL
  • dataset_code — VARCHAR(50) — NOT NULL
  • source_file_name — VARCHAR(255) — NOT NULL
  • source_published_at — DATE
  • imported_at — TIMESTAMP — NOT NULL
  • source_crs — VARCHAR(30) — NOT NULL
  • record_count — INTEGER
  • checksum — VARCHAR(128)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

land_use_zone

  • id — BIGINT — Primary key — NOT NULL
  • zone_code — VARCHAR(30) — NOT NULL
  • zone_name — VARCHAR(100) — NOT NULL
  • category — VARCHAR(50)
  • geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL

recommendation_request

  • id — BIGINT — Primary key — NOT NULL
  • natural_language_query — TEXT — NOT NULL
  • parsed_condition — JSONB — NOT NULL
  • weights — JSONB
  • created_at — TIMESTAMP — NOT NULL
  • user_id — BIGINT — Foreign key → users.ID — NOT NULL

recommendation_result

  • id — BIGINT — Primary key — NOT NULL
  • request_id — BIGINT — Foreign key → recommendation_request.id — NOT NULL
  • building_id — BIGINT — Foreign key → building.id — NOT NULL
  • rank — INTEGER — NOT NULL
  • total_score — NUMERIC(10,4) — NOT NULL
  • score_breakdown — JSONB — NOT NULL
  • explanation — TEXT
  • created_at — TIMESTAMP — NOT NULL

rent_statistic

  • id — BIGINT — Primary key — NOT NULL
  • commercial_area_id — BIGINT — Foreign key → commercial_area.id
  • administrative_area_id — BIGINT — Foreign key → administrative_area.id
  • base_period — DATE — NOT NULL
  • avg_rent — NUMERIC(14,2)
  • avg_rent_per_sqm — NUMERIC(14,2)
  • average_deposit — NUMERIC(18,2)
  • vacancy_rate — NUMERIC(5,2)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

sales_statistic

  • id — BIGINT — Primary key — NOT NULL
  • commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
  • business_category_code — VARCHAR(30) — NOT NULL
  • base_period — DATE — NOT NULL
  • estimated_sales_amount — NUMERIC(18,2)
  • transaction_count — BIGINT
  • average_transaction_amount — NUMERIC(18,2)
  • store_count — INTEGER
  • closed_store_count — INTEGER
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

store

  • id — BIGINT — Primary key — NOT NULL
  • source_store_id — VARCHAR(100) — NOT NULL
  • business_category_code — VARCHAR(30) — NOT NULL
  • business_category_name — VARCHAR(100) — NOT NULL
  • business_subcategory_code — VARCHAR(30)
  • business_subcategory_name — VARCHAR(100)
  • store_name — VARCHAR(200)
  • road_address — VARCHAR(300)
  • legal_dong_code — VARCHAR(20)
  • geom — GEOMETRY(POINT,5186) — NOT NULL
  • opened_at — DATE
  • closed_at — DATE
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users (유저)

  • ID (id) — BIGINT — Primary key — NOT NULL — @auto_increment
  • CLERK ID (clerk_id) — VARCHAR(300) — NOT NULL — UNIQUE
  • 이름 (name) — VARCHAR(20) — NOT NULL
  • 가입 일자 (created_at) — TIMESTAMP — NOT NULL — NOW()
  • 탈퇴 일자 (deleted_at) — TIMESTAMP