Technical Notes

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')