Difference between revisions of "Technical Notes"
From Kbwiki
Aubrey Moore (Talk | contribs) |
Aubrey Moore (Talk | contribs) (→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')