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

네이버 api로 위경도 받아왔을 때

MySQL에 Float 데이터 임포트시 잘림 현상

https://tonyne.jeju.onl/2016/06/13/mysql-csv-import-float-truncate-problem/

MySQL 데이터베이스로 CSV 데이터를 임포트 할 때

실수형(float) 값의 소수점 부분이 잘리는 문제가 있어 이를 해결하고 기록합니다.

• 환경
  Oracle 테이블 ==> CSV 파일 ==> MySQL 테이블
  (위도경도 좌표값)                            (위도경도 좌표값)
• 문제가 된 데이터 변환
  number(11,7) ==> CSV 파일 ==> float
  (CSV 파일까지는 정상인데 임포트 하면서 소수점 3번째 자리에서 잘림)
• 해결 방법
  number(11,7) ==> CSV 파일 ==> decimal(11,7)

** 참고문서
Using LOAD DATA INFILE to upload csv into mysql table

1 2 3 4 5 6 7

LOCALX   float comment '정류소 경도 (-180 ~ 180, 소수점 7자리)', LOCALY   float comment '정류소 위도 (-90 ~ 90, 소수점 7자리)',   /* ==> */   LOCALX   decimal(11,7) comment '정류소 경도 (-180 ~ 180, 소수점 7자리)', LOCALY   decimal(10,7) comment '정류소 위도 (-90 ~ 90, 소수점 7자리)',

 

 검색어 : 위경도 mysql 컬럼 형식

POINT 타입으로 저장된 컬럼에서 위경도 뽑기

POINT 타입으로 저장된 데이터에서 위도 경도를 뽑는 방법은 X(위도), Y(경도) 함수를 사용하면 됩니다.

?

1 2 3 4 5 6 7

# POINT 타입의 컬럼을 `Point` 라고 하자 # 위도는 Latitude, 경도는 Longitude   SELECT     X(`Point`) AS `Latitude`, Y(`Point`) AS `Longitude` FROM     `테이블`;

[db] DECIMAL 형식이란?

http://cafe.naver.com/swingme/329

필드 속성인 DECIMAL 은 큰 숫자에 대해서 처리할 때 사용

어느정도 크냐면 -10^38+1 부터 10^38-1 까지의 자릴수 이다..엄청나다.

 

자 그럼 본론으로 들어가기전에 제발 FLOAT 이딴거 사용하지 말자.

보통 0.000000 등의 소숫점 자리를 표현할 때 FLOAT 를 사용하는데 절대 FLOAT 는 사용하지 말아야 함

 

그 이유는 FLOAT 및 REAL 테이터 형식은 근사 데이터 형식이어서 정확한 값을 저장하지 않고

가장 가까운 근사값을 저장하기 때문이다. 따라서 요걸 이용하게 되면 나누기 등의 계산을 할때 정확한 값이

출력되지 않고 근사값을 출력하기 때문에 돈 계산시 문제가 발생할 수 있다.

 

 

그럼? INTEGER, DECIMAL, MONEY, SMALLMONEY 데이터 형식을 이용하자

 

여기서 DECIMAL 에 대한 사용방식은?

 

DECIMAL(5,2) 이렇게 지정한다는 것은 정수 5자리, 소숫점 2자리 라는 의미가 아니고 (즉, 12345.12 라는 의미가 아니다.)

전체 5자리중에서 소숫점이 2자리까지 확보 되었다 라는 의미이다.

정수는 최대 3자리, 소숫점은 최대 2자리 까지 저장될 수 있다. (즉 123.45 라는 의미이다.)

 

여기 입력될 수 있는 예를 보면

12.345 를 저장하면 12.35

1.2345 를 저장하면 1.23

123.1 을 저장하면 123.10                                  

 

에러가 발생되는 예를 보면

1234.5

12345 등이 될수 있다.

왜냐면 DECIMAL(5,2) 로 지정했다는 것은 정수가 3자리까지 올수 있다는 건데 지금의 예는 모두 3자리가 넘어간다.

그리고 요런 메세지가 출력된다 - 데이터 형식 numeric(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

 

 

자 그럼 데이터를 컨버팅할 때 사용방법

FLOAT 를 DECIMAL 로 변경하기

ABC 라는 필드 속성이 FLOAT 인데..이걸 DECIMAL 로 바꾸고 싶다면

 

ALTER TABLE dbo.Test_Tbl

           ALTER COLUMN ABC DECIMAL(5,2) NULL

 

라고 무작정 만들면 될까?? 당연히 안될 수 도 있다.

 

FLOAT 속성일 때 데이터가 어떻게 저장 되있느냐에 따라 DECIMAL 의 속성을 변경해 줘야한다.

1.12345 라고 들어가 있는데 소숫점은 2자리 까지, 정수는 5자리까지 표현하고 싶으면

DECIMAL(7,2) 라고 해야한다.

즉, 전체적으로 보고싶은 갯수에 소숫점 갯수 를 입력한다고 생각하면 될듯하다.

 

만약 지금 1.12345 를 DECIMAL(3,3) 으로 하면 바로 에러가 날 것이다.

왜냐면 전체 3자리중에 소숫점을 3자리로 했으니 당연히 에러 난다.

이렇게 - float을(를) 데이터 형식 numeric(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

 

다른 예를 들면

FLOAT 에 1234.123456 이라고 입력되있는데

DECIMAL(4,2) 라고 하면 당연히 에러난다.

 

정수가 지금 4자리까지 저장되 있으니 우선 5이상은 해야하며

소숫점 2자리 까지로 하고 싶으면 6 이상으로, 소숫점 1자리 까지 보고 싶으면 5이상은 지정해 줘야한다.

 

즉, DECIMAL(5,1) 로 하면 1234.1 로 저장될 것이고

      DECIMAL(6,2) 로 하면 1234.12 로 저장될 것이다.

 

      여기서 DECIMAL(6,2) 로 필드를 만들면 1234.12 까지 저장되는데 12345.1 을 저장하면 에러난다.

      왜냐면 DECIMAL(6,2) 라는 것은 소숫점 2자리까지 확보한것이여서 정수는 4자리까지만 올 수 있다.

      따라서 저장되는 데이터의 길이가 어느정도 까지일지 생각하고 만들어야 할 것이다.

        

 

-끝-

맘대로 퍼가셔도 좋아요..출처만 명확히...

[출처] 필드 속성 DECIMAL (문 뜻 생각나는것) |작성자 얌얌

처음에 주소를 이용해서 네이버api로 위경도를 얻어서 DB에 저장을 했었다. 

mysql 을 사용중이고 칼럼 형식은

위도 : decimal(10,7) 

경도 : decimal(11,7)

이렇게 하니까 잘리지 않고 저장할 수 있었다.

그런데 위경도를 네이버가 아닌 구글에서 가져다 쓰게 변경되었다.

위의 칼럼 형식에 구글 위경도를 넣으니 잘려서 들어갔다..

위도 : decimal(16,14) 

경도 : decimal(17,14) 이렇게 변경했다.

org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];   

--- The error occurred in sqlmap/dummy.xml.  

--- The error occurred while applying a parameter map.  

--- Check the SHOPMANAGE.insertPosition-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in sqlmap/dummy.xml.  

--- The error occurred while applying a parameter map.  

--- Check the SHOPMANAGE.insertPosition-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)

at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:378)

at oudiga.common.dao.CommonDao.update(CommonDao.java:54)

at oudiga.module.admin.serviceImpl.ShopManageServiceImpl.insertPosition(ShopManageServiceImpl.java:146)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)

at oudiga.common.aop.UgbossXMLAdvice.aroundExecuteMethod(UgbossXMLAdvice.java:88)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

at com.sun.proxy.$Proxy11.insertPosition(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)

at com.sun.proxy.$Proxy12.insertPosition(Unknown Source)

at oudiga.module.admin.controller.ShopManageController.RestaurantManage_insertPosition(ShopManageController.java:88)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:669)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:574)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in sqlmap/dummy.xml.  

--- The error occurred while applying a parameter map.  

--- Check the SHOPMANAGE.insertPosition-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:380)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:1)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)

... 76 more

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4224)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at sun.reflect.GeneratedMethodAccessor73.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)

at com.sun.proxy.$Proxy36.execute(Unknown Source)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

... 81 more

[11:22:58] 

ERROR_EXCEPTION       : class org.springframework.dao.DataIntegrityViolationException

ERROR_TARGET CLASS     : class oudiga.module.admin.serviceImpl.ShopManageServiceImpl

ERROR_TARGET METHOD   : insertPosition

ERROR_MESSAGE         : SqlMapClient operation; SQL [];   

--- The error occurred in sqlmap/dummy.xml.  

--- The error occurred while applying a parameter map.  

--- Check the SHOPMANAGE.insertPosition-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in sqlmap/dummy.xml.  

--- The error occurred while applying a parameter map.  

--- Check the SHOPMANAGE.insertPosition-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1

변경 중에 

Data truncation: Out of range value adjusted for column 'LATITUDE' at row 1

이런 에러가 났었는데

위도컬럼에 경도데이터를 넣어서 났었던 에러다