Difference between revisions of "Technical Notes"

From Kbwiki
Jump to: navigation, search
(R scripts)
Line 25: Line 25:
 
  x<-readShapePoints(file.choose())
 
  x<-readShapePoints(file.choose())
 
  write.csv(x,"temp.csv",row.names=F)
 
  write.csv(x,"temp.csv",row.names=F)
 +
 +
# This script creates a shapefile for each trap route with UTM coordinates.
 +
# Aubry Moore 2008.03.12
 +
 +
library(maptools)
 +
library(rgdal)
 +
library(RODBC)
 +
 +
# Establish connection to database; "oryctes" is a DSN
 +
 +
channel <- odbcConnect("oryctes")
 +
 +
# Get list of trap routes
 +
 +
sql_text<-paste(
 +
  "SELECT trap_route",
 +
  "FROM trap",
 +
  "WHERE date_removed=0", #important
 +
  "GROUP BY trap_route;")
 +
result<-sqlQuery(channel,sql_text)
 +
 +
for (i in 1:nrow(result)){
 +
 +
  current_trap_route<-result$trap_route[i]
 +
 +
  # Read trap table from MySQL database.
 +
  # The fields, data_entry_date and date_removed generate the error message:
 +
  # "Error in fromchar(x) : character string is not in a standard unambiguous format"
 +
  # so these have been left out of the query.
 +
 +
  sql_text<-paste(
 +
    "SELECT ",
 +
    "  trap_id, data_entered_by, date_deployed, latitude, longitude, location_description,",
 +
    "  trap_type, trap_route, sequence, landowner, notes  ",
 +
    "FROM trap ",
 +
    "WHERE ",
 +
    "  date_removed=0",
 +
    "  AND trap_route='",
 +
    current_trap_route,
 +
    "';",
 +
    sep=""
 +
  )
 +
  traps<-sqlQuery(channel,sql_text)
 +
  PointsAsFrame <- SpatialPointsDataFrame(cbind(traps$longitude, traps$latitude),traps)
 +
 
 +
  # we need to set the projection string attribute to the point set to be transformed.
 +
 +
  proj4string(PointsAsFrame) <- CRS("+proj=longlat +datum=WGS84")
 +
 
 +
  # perform the transformation and plot the points
 +
 +
  ThePointsUTM <- spTransform(PointsAsFrame,CRS("+proj=utm +zone=55 +datum=WGS84"))
 +
 +
  # Write to a shapefile
 +
 +
  filename <- paste("C:/RMaps/guam map/traps/",current_trap_route,sep="")
 +
  write.pointShape(ThePointsUTM,file=filename,coordinates=ThePointsUTM@coords)
 +
}
 +
print('FINISHED')

Revision as of 16:20, 11 March 2008

SQL

  • Query to find trap_id's in the trap_routes table which are unmatched in the trap table:
SELECT 
  trap_routes.trap_id, trap.trap_id
FROM 
  trap_routes LEFT JOIN trap ON trap_routes.trap_id = trap.trap_id
WHERE 
  trap.trap_ID IS NULL 
  • Query to update trap table using values from trap_routes table:
UPDATE 
  trap, trap_routes 
SET 
  trap.trap_route=trap_routes.trap_route, 
  trap.trap_route_sequence=trap_routes.trap_route_sequence 
WHERE 
  trap.trap_id=trap_routes.trap_id
  • Query to add extra column with observation_date format as m/d/Y:
SELECT *,DATE_FORMAT(observation_date,'%m/%d/%Y') AS US_observation_date FROM crb_observations;

R scripts

# this R script reads a shapefile and exports it to a csv text file
library(maptools)
x<-readShapePoints(file.choose())
write.csv(x,"temp.csv",row.names=F)
# This script creates a shapefile for each trap route with UTM coordinates.
# Aubry Moore 2008.03.12
library(maptools)
library(rgdal)
library(RODBC)
# Establish connection to database; "oryctes" is a DSN
channel <- odbcConnect("oryctes")
# Get list of trap routes
sql_text<-paste(
  "SELECT trap_route",
  "FROM trap",
  "WHERE date_removed=0", #important
  "GROUP BY trap_route;")
result<-sqlQuery(channel,sql_text)
for (i in 1:nrow(result)){
 current_trap_route<-result$trap_route[i]
 # Read trap table from MySQL database.
 # The fields, data_entry_date and date_removed generate the error message: 
 # "Error in fromchar(x) : character string is not in a standard unambiguous format"
 # so these have been left out of the query.
 sql_text<-paste(
   "SELECT ",
   "  trap_id, data_entered_by, date_deployed, latitude, longitude, location_description,",
   "  trap_type, trap_route, sequence, landowner, notes  ",
   "FROM trap ",
   "WHERE ",
   "  date_removed=0",
   "  AND trap_route='",
   current_trap_route,
   "';",
   sep=""
 )
 traps<-sqlQuery(channel,sql_text)
 PointsAsFrame <- SpatialPointsDataFrame(cbind(traps$longitude, traps$latitude),traps)
 
 # we need to set the projection string attribute to the point set to be transformed.

 proj4string(PointsAsFrame) <- CRS("+proj=longlat +datum=WGS84")
 
 # perform the transformation and plot the points
 ThePointsUTM <- spTransform(PointsAsFrame,CRS("+proj=utm +zone=55 +datum=WGS84"))

 # Write to a shapefile
 filename <- paste("C:/RMaps/guam map/traps/",current_trap_route,sep="")
 write.pointShape(ThePointsUTM,file=filename,coordinates=ThePointsUTM@coords)
}
print('FINISHED')