# Merge CSV file data into a single data frame

file_data = read.csv("downloads/chi-srp.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$Type = NA
file_data$State = "IL"
file_data$Status = NA
file_data$Source = "Illinois EPA Site Remediation Program"
file_data = file_data[,c("NAMESITE,C,50", "Type", "Source", "IEPAID,C,10", "Status",
	"STREET,C,40", "CITY,C,25", "State", "LATITUDE,N,20,5", "LONGITUDE,N,20,5")]
colnames(file_data) = c("Name", "Type", "Source", "SourceID", "Status", 
	"Address", "City", "State", "Latitude", "Longitude")
csv = file_data


file_data = read.csv("downloads/envirostor-los-angeles.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$State = "CA"
file_data$Source = "EnviroStor"
file_data = file_data[,c("SITE / FACILITY NAME", "SITE / FACILITY TYPE", "Source", "ENVIROSTOR ID", "STATUS",
	"ADDRESS DESCRIPTION", "CITY", "State", "LATITUDE","LONGITUDE")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)
	

file_data = read.csv("downloads/envirostor-san-diego.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$State = "CA"
file_data$Source = "EnviroStor"
file_data = file_data[,c("SITE / FACILITY NAME", "SITE / FACILITY TYPE", "Source", "ENVIROSTOR ID", "STATUS",
	"ADDRESS DESCRIPTION", "CITY", "State", "LATITUDE","LONGITUDE")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)


file_data = read.csv("downloads/nyc-esrd.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$State = "NY"
file_data$Source = "NYS Environmental Site Remediation Database"
codes = c('1' = "Contamination Confirmed", '2' = "Hazardous Disposal Confirmed", 
	'3' = "No Threat", '4' = "Closed With Continued Management", '5' = "Closed",
	'A' = "Active", 'C' = "Completed", 'P' = "Potential")
file_data$Status = codes[file_data$"Site Class"]
file_data = file_data[,c("Site Name", "Program Name", "Source", "Site Code", "Status",
	"Address", "City/town", "State", "Latitude", "Longitude")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)


file_data = read.csv("downloads/azdeq-brownfield-grants.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$Nada = NA
file_data$State = "AZ"
file_data$Source = "AZDEQ Brownfields Assistance Program"
file_data = file_data[,c("Site Name", "Nada", "Source", "Nada", "Nada",
	"Nada", "City", "State", "Latitude", "Longitude")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)


file_data = read.csv("downloads/texas-vcadb.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$Nada = NA
file_data$State = "TX"
file_data$Source = "TX Voluntary Cleanup Program"
file_data = file_data[,c("Site", "Nada", "Source", "VCP ID", "Nada",
	"Site Address", "Site City", "State", "LATDD", "LONGDD")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)


file_data = read.csv("downloads/texas-bsadb.csv",
	colClasses = "character", fill=T, check.names=F)
file_data$State = "TX"
file_data$Source = "TX Voluntary Cleanup Program"
file_data = file_data[,c("Site", "Type Facility", "Source", "BFG No", "Phase",
	"Site Address", "Site City", "State", "LATDD", "LONGDD")]
colnames(file_data) = colnames(csv)
csv = rbind(csv, file_data)



# Create points from valid long/lat

csv[,"Latitude"] = as.numeric(csv[,"Latitude"])
csv[,"Longitude"] = as.numeric(csv[,"Longitude"])
csv = csv[(!is.na(csv[,"Latitude"]) & !is.na(csv[,"Latitude"]) & 
	(csv[,"Latitude"] < 180) & (csv[,"Latitude"] > -180)),]

csv_points = SpatialPoints(coords = csv[, c("Longitude","Latitude")], 
	proj4string=CRS("+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"))

# Find analysis cities

analysis_areas <<- readOGR(dsn="/home/michael/mapping-sustainability/boundaries/analysis-areas",
	layer="boundaries-county")
analysis_areas <<- spTransform(analysis_areas, CRS("+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"))
analysis_polygons <<- SpatialPolygons(analysis_areas@polygons, proj4string=CRS(proj4string(analysis_areas)))
indices = over(csv_points, analysis_polygons)
csv$City = as.character(analysis_areas@data[indices, "CITY"])
csv = csv[!is.na(csv$City),]


# Create data frame containing only points within analysis cities

geospatial = SpatialPointsDataFrame(coords = csv[,c("Longitude","Latitude")], data = csv)
geospatial = geospatial[!is.na(geospatial@data$City),]


# Save as shapefile

writeOGR(geospatial, dsn="/home/michael/mapping-sustainability/brownfields",
	layer="state-programs", driver="ESRI Shapefile", overwrite_layer=T)
