Difference between revisions of "Technical Notes"

Revision as of 16:22, 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')