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