데이터 베이스 작업하기

onyoo·2022년 9월 16일

MariaDB

관광데이터_공모전

목록 보기

2/3

데이터베이스 작업

오늘의 작업

sequel pro 연결하기
데이터베이스 테이블 만들기
데이터 삽입하기

sequel pro 연결하기

일단, 로컬에 mariadb를 설치하고 비밀번호 세팅등의 잡다한 작업을 해주었다. 다만, cli 환경에서의 접속은 되었지만 gui 툴에서는 잡히지 않았다.

해당 오류는 mysql에 접속해서 해당 명령어를 나의 상황에 맞게 변경하여 작성해주면 해결된다.

데이터베이스 테이블만들기

데이터들 중 처음 다루어보는 데이터가 있어서 조금 당황한 부분이 많았다. 일단 완성된 테이블은 이렇다.

create table information(
	id INT NOT NULL AUTO_INCREMENT COMMENT '음식점 ID',
	name VARCHAR(100) NOT NULL COMMENT '음식점명',
	gubun VARCHAR(50) NOT NULL COMMENT '음식점 분류',
	gugun VARCHAR(50) NOT NULL COMMENT '구명' ,
	location POINT NOT NULL COMMENT '좌표',
	cntct VARCHAR(100) NOT NULL COMMENT '전화번호',
	homepage_url VARCHAR(255) COMMENT '식당 홈페이지 주소',
	usage_of_week_and_time VARCHAR(100) COMMENT '식당영업시간',
	address VARCHAR(100) NOT NULL COMMENT '식당주소',

	PRIMARY KEY (id)
)

이중에서 좀 신경써서 찾아본 데이터는 좌표값과 URL 전화번호 부분이다.

MySQL 최적의 데이터 타입 선택 방법 - Useful Guide

좌표

좌표의 경우 우리가 위도,경도 값을 가지고 있는데 그것을 나누어 저장하게 되면 데이터가 잘리기 때문에 POINT 로 저장해주어야 한다.

위도와 경도를 별도의 DECIMAL (또는 FLOAT) 컬럼에 저장하지 않고, POINT 유형의 컬럼을 사용하여 저장 → 좌표는 POINT(경도, 위도) 의 형식으로 저장해야 합니다.

도서관의 좌표를 저장하는 fclty_loc 컬럼에 Spatial Index를 생성 → R-Tree 인덱스이며, MBR (Minimum Bounding Rectangle) 을 사용한 검색에서 성능 최적화 가능

[MySQL] 가장 가까운 시설물 조회 - 두 좌표 (위도, 경도) 사이 거리 계산

전화번호

전화번호의 경우 검색결과 다양한 방법으로 저장하는 것 같아보였는데 나의 경우에는 VARCHAR로 저장했다.

두가지 경우가 있을 것 같은데 - 가 필요한 경우와 필요없을 경우로 - 을 넣어 저장하느냐 아니면 빼고 저장하느냐이다.

이런경우 국가코드, 지역코드로 처리해야하는 상황이 있다면 - 를 넣어 저장하는것이 더 좋을 것 같다.

MySQL 검색속도 : int 와 varchar 누가 더 빠르냐?

WWW.PHPSCHOOL.COM
URL

약간 나의 개인적인 생각으로는 URL 주소가 얼마나 길지, 예상이 불가능하기 때문에 최대한의 크기를 제공해야한다고 생각하여 VARCHAR(255)로 할당하여주었다.

데이터베이스에 데이터 넣기

아,,진짜 Point 넣는게 조금 많이 독특해서 이것저것 시도해봤다.

insert into information
(name, gubun, gugun_nm, location, cntct, 
homepage_url, usage_of_week_and_time, address)
values('부탄츄','일식','마포구',ST_GeomFromText('POINT(37.5564036476463 126.926735502823)'),
'02-3144-3304','https://www.instagram.com/butanchu_seoul/',
'월-토: 11:30 - 23:00 일: 11:30 - 21:30','서울특별시 마포구 와우산로35길 75');

POINT 좌표의 경우 저런식으로 변환과정을 거쳐야한다.

또한, id의 경우 자동으로 들어가도록 해놓았으니 앞에 어떤 밸류를 넣을 것인지 밸류 이름을 명시해주어야한다..

데이터베이스를 활용한 API 만들기

Cannot call sendError() after the response has been committed

테스트를 위해 Point 데이터 없이 불러왔더니 잘 되었다. Point 객체를 제대로 사용하지 못해 발생한 오류인 것 같음

How to read geography values using hibernate?

나와 똑같은 에러를 겪는 사람을 발견함

~~결국에는 long 타입으로 데이터를 저장한 다음 위도,경도를 따로 저장하기로 했다.~~

Decimal Type 으로 저장하기로 함

name	type	option	comment
id (pk)	long	not null	음식점 id
name	varchar(100)	not null	음식점명
gubun	varchar(50)	not null	음식점 분류
gugun	varchar(50)	not null	구명
lat	DECIMAL(16,14)	not null	위도
lng	DECIMAL(16,14)	not null	경도
cntct	varchar(100)	not null	전화번호
homepage_url	varchar(255)		식당홈페이지 주소
usage_of_week_and_time	varchar(100)		식당 영업 시간
address	varchar(100)	not null	식당 주소

create table information(
		                        id INT NOT NULL AUTO_INCREMENT COMMENT '음식점 ID',
                            name VARCHAR(100) NOT NULL COMMENT '음식점명',
                            gubun VARCHAR(50) NOT NULL COMMENT '음식점 분류',
                            gugun VARCHAR(50) NOT NULL COMMENT '구명' ,
                            lat DECIMAL(16,14) NOT NULL COMMENT '위도',
                            lng DECIMAL(17,14) NOT NULL COMMENT '경도',
                            cntct VARCHAR(100) NOT NULL COMMENT '전화번호',
                            homepage_url VARCHAR(255) COMMENT '식당 홈페이지 주소',
                            usage_of_week_and_time VARCHAR(100) COMMENT '식당영업시간',
                            address VARCHAR(100) NOT NULL COMMENT '식당주소',

                            PRIMARY KEY (id)
)

Decimal (길이, 소수점 자리 수) 로 생성한다. long,int 를 넘어가는 경우 해당 타입으로 저장하여 핸들링한다.

일단, maximum으로 생성되도록 설정함.

DECIMAL을 사용하여 DECIMAL(10,7) 과 같이 사용하여 DB에 저장을 하는것 이 좋을 것 같습니다. (DECIMAL의 범위는 -10^38+1 ~ 10^38-1 까지 입니다,)

[출처] [DB] 지도 좌표(위도,경도) 저장하기|작성자 jeongupark

mysql , 위경도 저장시 칼럼형식

출력화면

마지막 소숫점이 남기는 하지만 예비용으로 남겨두는 것도 좋을지도 모른다.

허나, 해당 부분이 디비의 리소스 많은 부분을 차지하게 된다면 수정을 변경해야할듯하다.

csv를 cli환경에서 마리아디비에 삽입하였다.

📍 ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

set global local_infile=true;

show global variables like 'local_infile';

mysql --local_infile -uroot -p

LOAD DATA LOCAL INFILE '/Users/onyoo/Downloads/hong.csv' 
into table store.information fields terminated by ',' enclosed by '"' lines 
terminated by '\n' ignore 1 rows 
(@name,@gubun,@gugun,@lat,@lng,@cntct,@homepage_url,@usage_of_week_and_time,@address) 
set name=@name, gubun = @gubun, gugun = @gugun, lat = @lat, lng = @lng, cntct = @cntct, 
homepage_url = @homepage_url, usage_of_week_and_time = @usage_of_week_and_time,
address=@address;