1

I have the following data base with information about rainfall for a set of meteorological stations:

  year  station latitude longitude depto municipio       date rainfall
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-01     13.2
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-02     83.1
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-03     10.5
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-04      9.1
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-05      0.0
  1990 11010010   0527 N    7632 W CHOCO     LLORO 1990-01-06     10.4

I am trying to import this to QGIS using the "New layer from delimited text" tool, but QGIS is unable to detect the longitude and latitude even specifying the X and Y coordinates. I know these longitudes and latitudes are not the common WGS84 ones, there is a way to reproject both coordinates?

(I also use R but rgdal commands do not work with the Spatialframe).

  • They are probably DD MM so 5 27 N and 76 32 W. – mkennedy Oct 30 '15 at 20:38
  • Is this weather station in Quebec or Columbia? – ed.hank Oct 30 '15 at 20:40
  • Chocó is a department of Colombia. – mkennedy Oct 30 '15 at 20:49
3

It looks like you have ACSII characters in your coordinate columns (ie., "N" & "W") which is also why you have leading zeros in the latitude column. Your lat/long values do not make any sense in their current format. @mkennedy is likely correct that they are DD MM and need to be reformatted accordingly.

You could read this data into R as a flat file using read.table or read.csv, reformat the columns and then coerce into a SpatialPointsDataFrame object.

Here is an example where we reformat the DD MM coordinates and create a SpatialPointsDataFrame.

First, let's recreate your example data as a data.frame object

( rain <- data.frame( year = rep(1990,6), station = rep(11010010, 6),
latitude = rep("0527 N", 6), longitude = rep("7632 W", 6),
date = c("1990-01-01","1990-01-02","1990-01-03","1990-01-04","1990-01-05","1990-01-06"), 
rainfall = c(13.2,83.1,10.5,9.1,0.0,10.4) ) )

Here we convert the ASCII coordinates to DD, [degrees + ( minutes / 60 )]. The function substr lets us pull a fixed portion of the string and lapply takes the place of a for loop. The unlist function coerces the resulting list to a vector.

lat <- lapply(rain$latitude, FUN = function(x) {
      c(as.numeric(substr(x, 2, 2)),
      as.numeric(substr(x, 3, 4))) } ) 
lat <- unlist(lapply(lat, FUN= function(x) { x[1] + ( x[2] / 60 ) } ))

long <- lapply( rain$longitude, FUN = function(x) {
      c(as.numeric(substr(x, 1, 2)),
      as.numeric(substr(x, 3, 4))) } )    
long <- unlist(lapply(long, FUN= function(x) { x[1] + ( x[2] / 60 ) } ))

Here we add the lat, long vectors to the rain data.frame (creating a new data.frame, rain.sp) and coerce to sp object.

library(sp)
rain.sp <- data.frame(rain, lat=lat, long=long * -1)
coordinates(rain.sp) <- ~long+lat
class(rain.sp)

However, one thing that I notice is that since your data is in long format you have repeated measurements of rainfall for a given station. With a direct conversion of the data you will end up with a replicated point location for each date measurement, which is bad practice and will cause considerable issues down the road. To account for this we can simply transpose the data into a long format and then apply the above method.

Here we use the spread function in the tidyr library to transpose the data.frame to a long format where each date measurement becomes a column and each station becomes a single observation (line).

library(tidyr)
( rain <- spread(rain, date, rainfall) )

Now we can repeat the above procedure and end up with a spatial object, representing single stations with rain measurements for each date, in columns.

# Convert coordinates to DD, degrees + ( minutes / 60 )
lat <- lapply(rain$latitude, FUN = function(x) {
      c(as.numeric(substr(x, 2, 2)),
      as.numeric(substr(x, 3, 4))) } ) 
lat <- unlist(lapply(lat, FUN= function(x) { x[1] + ( x[2] / 60 ) } ))  
long <- lapply( rain$longitude, FUN = function(x) {
      c(as.numeric(substr(x, 1, 2)),
      as.numeric(substr(x, 3, 4))) } )    
long <- unlist(lapply(long, FUN= function(x) { x[1] + ( x[2] / 60 ) } ))

# Add lat/long data to data.frame and coerce to sp object
library(sp)
rain.sp <- data.frame(rain, lat=lat, long=long * -1)
coordinates(rain.sp) <- ~long+lat

class(rain.sp)
str(rain.sp@data)  
rain.sp@data
  • you do not really need lapply and could do: lat <- as.numeric(substr(rain$latitude, 1, 2)) + as.numeric(substr(rain$latitude, 3, 4))/60 – Robert Hijmans Oct 30 '15 at 23:50
2

I think your best bet would be to reformat the data in the table before feeding it to QGIS, perhaps into two new columns called lat and long, and seeing that these are in signed decimal degrees. If the data are indeed in DDMM, your first line should be converted to:

0527 N  7632 W  ---->  5.45  -76.533

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.