So, I have two R “sf” “data.frames”, one with millions of linestring geometries (vsr_segments: see below) and the other with 5 polygons (vsr_zones: see below). Each linestring has a datetime and each polygon has a unique date range. I’m trying to intersect the linestrings dataframe with the polygon data.frame based on whether the linestring datetime falls within a specific polygon’s
Tag: r
Why does this work under Microsoft SQL Server Management Studio but not under R?
I received this SQL statement: and it runs nicely under MS SSMS. The relevant part of the result is: When I run it under R I must leave out use MYDATABASE; and be sure I am connected at the right database, which is the case. But the SQL-statement is problematic. The output of: is data frame with 0 columns and
Count based on multiple conditions using sqldf
Hi all I’m writing a sql query on R using sqldf and seem to hit a roadblock. I have a table with an Id column, two dates columns and a grouping by column. The query I’m writing is From this query the result I’m getting is Whichis correct based on the query. What I want to achieve is if my
How to set flag value based on data that use one-hot-encoding
I have a database consisting of three tables like this: I want to make a machine learning model in R using that database, and the data I need is like this: I can use one hot encoding to convert categorical variable from t_pengolahan (such as “Pengupasan, Fermentasi, etc”) into attributes. But, how to set flag (yes or no) to the
How do you filter with a Dataframe, list, vector etc. to a table in a database in R?
I have a large set of id-s which is in a csv file. How could I filter on a database table using only that one-columned table in the csv file? For example in the ODBC database we have: TABLE 1 +–…
Can you name dbplyr’s simulated lazy tables?
dbplyr has some very useful-looking simulation functions so you can write queries while not connected to any real database, but I can’t seem to get actual table names into any of the queries I write …
how to export data from SQL in R
I’m a beginner in R. I have connection via ODBC to SQL DB: dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=OurServer; Database=OurDatabase;Uid=; Pwd=") initdata <...
How to compose query properly using dbGetQuery command?
I have a small database in PostgresSQL. And I connected to it via R-studio and try to retrieve data from database. I wrote a command, but it doesn’t work. And I don’t know what’s the problem. Please, help me. So, the code works well. But these two codes fail Where con is the postgres connection object. And of course query
Extracting Data from SQL Server Table within a Schema
I am having a hard time writing and extracting data from SQL Server in RStudio. My database hierarchy is as follows: LDS LDS HIG table_needed I’ve tried: ch <- DBI::dbConnect(odbc::odbc(), "…
Preserving times Class with sqldf
I am using sqldf to join several tables, but I am having trouble preserving the times class set with the chron package on a column. I used the method=”name__class” argument to the sqldf function and …