Difference between revisions of "Technical Notes"

From Kbwiki
Jump to: navigation, search
Line 18: Line 18:
 
* Query to add extra column with <b>observation_date</b> format as m/d/Y:
 
* Query to add extra column with <b>observation_date</b> format as m/d/Y:
 
  SELECT *,DATE_FORMAT(observation_date,'%m/%d/%Y') AS US_observation_date FROM crb_observations;
 
  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)

Revision as of 00:47, 9 March 2008

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