0
library(RMySQL)
con <- dbConnect(MySQL(), dbname="hs***", host="localhost",
                 port = 3306, user="****",
                 password="****")
dbSendQuery(con, "SEt NAMES euckr")
d <- dbReadTable(con, "accidents")
str(d)

#it results :
$ lat        : num  38 38 38 38 38 38 37 38 38 35 ...
$ longt      : num  127 127 127 127 127 ...

structure of latitude and longitude in R says num, but I made these columns decimal(9,7) and (11,8) in MYSQL.

I want to know how to fix it.

Also, I got these error codes:

1: In .local(conn, statement, ...) :
  Decimal MySQL column 14 imported as numeric
2: In .local(conn, statement, ...) :
  Decimal MySQL column 15 imported as numeric
zx8754
  • 52,746
  • 12
  • 114
  • 209
이훈석
  • 45
  • 5
  • how about `dbGetQuery(conn, "select * from accidents")` – gaut Nov 10 '20 at 12:11
  • I did that.. but I got almost same results : $ lat : num 37.6 37.6 37.6 37.6 37.6 ... $ longt : num 127 127 127 127 127 ... – 이훈석 Nov 10 '20 at 12:14
  • To complement the coment @CatalystRPA: have you tried casting to character within the select and then convert it to double once the data is in R? – DPH Nov 10 '20 at 12:16
  • also have to tried to convert the data to double in R to make sure numeric is not just rounding the results for displaying (possibly there are many digits behind the comma) – DPH Nov 10 '20 at 12:18
  • @DPH oh, yes! It has many digits behind the comma because it is information of lattitude and longitude. Then, should I try to convert data in R into double? – 이훈석 Nov 10 '20 at 12:22
  • you can try to subtract 37 from $lat to see if the data got read in correctly ... just tried a few things here an R rounds doubles also for display depending but one I subtract the value in front of the comma/dot I get a number smaller than 1 – DPH Nov 10 '20 at 12:30

1 Answers1

0

have a look at this sequence as it might be the solution to your problem - R seems to round values for displaying:

y <- "1.1000000001"
y
[1] "1.1000000001"

y1 <- as.double(y)
y1
[1] 1.1


y2 <- y1-1
y2-0.1
[1] 1.000001e-10
zx8754
  • 52,746
  • 12
  • 114
  • 209
DPH
  • 4,244
  • 1
  • 8
  • 18
  • Is that mean R round values just for "displaying" and raw data doesn't change? – 이훈석 Nov 10 '20 at 12:36
  • I tried as.double(d$latitude) and as.double(d$longitude) in R -> I got raw data latitude and longitude that I want to get. However, when I try str(d) in R -> It still says it is numeric.. – 이훈석 Nov 10 '20 at 12:38
  • IIRC numeric can be double and integer depending on what you supply to it - and from what my sequence show the data is beeing rounded for display reasons... I just tried my sequence with as.numeric() instead of as.double and it works also – DPH Nov 10 '20 at 12:43
  • 1
    From the manual: "double is identical to numeric.... R has no single precision data type. All real numbers are stored in double precision format. " – zx8754 Nov 10 '20 at 12:52
  • 1
    Related post about print method, R is not rounding it: https://stackoverflow.com/q/2287616/680068 – zx8754 Nov 10 '20 at 12:54
  • @zx8754 I am sorry to not express myself correctly: I meant that R rounds the data when printing (and now thx to you I now how to increase the quantity of digits after the point/comma) – DPH Nov 10 '20 at 13:22
  • 1
    @DPH no worries, we have all been there :) – zx8754 Nov 10 '20 at 13:23