CREATE TABLE r_vegetationtype 
(
aekosid CHAR(120),
 latitude character varying,
  longitude character varying,
  mvg_name character varying,
  CONSTRAINT aekosid_pk PRIMARY KEY (aekosid)

      );


      COPY r_vegetationtype FROM '/Users/a1618290/IngestionData/sPlots/vegStudylocIntersect.csv' DELIMITER ',' CSV;

CREATE TABLE r_landuse 
(
aekosid CHAR(120),
 latitude character varying,
  longitude character varying,
  derivedLanduse character varying,
  CONSTRAINT aekosid_landuse_pk PRIMARY KEY (aekosid)

      );


      COPY r_landuse FROM '/Users/a1618290/IngestionData/sPlots/landuseStudylocIntersect.csv' DELIMITER ',' CSV;


CREATE TABLE r_solr 
(
aekosid CHAR(120),
 measuredSlope character varying,
  measuredAspect character varying,
  measuredAltitude character varying,
  derivedSlope character varying,
  derivedAspect character varying,
  derivedAltitude character varying,
  CONSTRAINT aekosid_solr_pk PRIMARY KEY (aekosid)

      );


      COPY r_solr FROM '/Users/a1618290/IngestionData/sPlots/solrDump.csv' DELIMITER ',' CSV;



CREATE TABLE r_flatfile
(
AEKOSLink CHAR(120),
Long character varying,
Lat character varying,
CRS character varying,
Reliab character varying,
TimeStart character varying,
TimeEnd character varying,
observedLanduse character varying,
TaxonName character varying,
NameType character varying,
LowerAbund character varying,
UpperAbund character varying,
AbundUnits character varying,
AreaSize character varying,
AreaUnits character varying,
calculatedVegetationCoverForSite character varying,
calculatedVegetationCoverForSiteUnits character varying,
Notes character varying
      );

    COPY r_flatfile FROM '/Users/a1618290/IngestionData/sPlots/allExceptQLDNSW.csv' DELIMITER ',' CSV;
    COPY r_flatfile FROM '/Users/a1618290/IngestionData/sPlots/QLDNSW1MS.csv' DELIMITER ',' CSV;
    COPY r_flatfile FROM '/Users/a1618290/IngestionData/sPlots/QLDNSW2MS.csv' DELIMITER ',' CSV;
    COPY r_flatfile FROM '/Users/a1618290/IngestionData/sPlots/QLDNSW3MS.csv' DELIMITER ',' CSV;



CREATE TABLE r_flatfileDistinct
(
AEKOSLink CHAR(120),
Long character varying,
Lat character varying,
CRS character varying,
Reliab character varying,
observedLanduse character varying,
calculatedVegetationCoverForSite character varying,
calculatedVegetationCoverForSiteUnits character varying
      );

insert into r_flatfileDistinct ( SELECT DISTINCT AEKOSLINK, LONG, LAT, CRS, RELIAB, observedLanduse, calculatedVegetationCoverForSite,
  calculatedVegetationCoverForSiteUnits
  FROM PUBLIC.R_FLATFILE);


CREATE TABLE r_sites
(
aekosid CHAR(120),
longitude character varying,
latitude character varying,
crs character varying,
reliab character varying,
observedLanduse character varying,
derivedlanduse character varying,
vegetationtype character varying,
measuredslope character varying,
derivedslope character varying,
measuredaspect character varying,
derivedaspect character varying,
measuredaltitude character varying,
derivedaltitude character varying,
calculatedVegetationCoverForSite character varying,
calculatedVegetationCoverForSiteUnits character varying
      );

insert into r_sites ( 
SELECT PUBLIC.R_FLATFILEDISTINCT.AEKOSLINK, PUBLIC.R_FLATFILEDISTINCT.LONG,
  PUBLIC.R_FLATFILEDISTINCT.LAT, PUBLIC.R_FLATFILEDISTINCT.CRS,
  PUBLIC.R_FLATFILEDISTINCT.RELIAB, PUBLIC.R_FLATFILEDISTINCT.observedLanduse,
  PUBLIC.R_LANDUSE.derivedLanduse, PUBLIC.R_VEGETATIONTYPE.MVG_NAME,
  PUBLIC.R_SOLR.MEASUREDSLOPE,PUBLIC.R_SOLR.DERIVEDSLOPE, PUBLIC.R_SOLR.MEASUREDASPECT,PUBLIC.R_SOLR.DERIVEDASPECT, PUBLIC.R_SOLR.MEASUREDALTITUDE,
    PUBLIC.R_SOLR.DERIVEDALTITUDE,
  PUBLIC.R_FLATFILEDISTINCT.calculatedVegetationCoverForSite, PUBLIC.R_FLATFILEDISTINCT.calculatedVegetationCoverForSiteUnits
  FROM
       PUBLIC.R_FLATFILEDISTINCT 
       left JOIN PUBLIC.R_LANDUSE ON PUBLIC.R_FLATFILEDISTINCT.AEKOSLINK = PUBLIC.R_LANDUSE.AEKOSID 
       left JOIN PUBLIC.R_VEGETATIONTYPE ON PUBLIC.R_FLATFILEDISTINCT.AEKOSLINK = PUBLIC.R_VEGETATIONTYPE.AEKOSID 
       left JOIN PUBLIC.r_solr ON PUBLIC.R_FLATFILEDISTINCT.AEKOSLINK = PUBLIC.r_solr.AEKOSID
order by PUBLIC.R_FLATFILEDISTINCT.AEKOSLINK);


CREATE TABLE r_speciesObservations
(
aekosid CHAR(120),
TimeStart character varying,
TimeEnd character varying,
TaxonName character varying,
NameType character varying,
LowerAbund character varying,
UpperAbund character varying,
AbundUnits character varying,
AreaSize character varying,
AreaUnits character varying,
Notes character varying
      );

insert into r_speciesObservations ( 
select PUBLIC.R_FLATFILE.AEKOSLINK ,
PUBLIC.R_FLATFILE.TimeStart,
PUBLIC.R_FLATFILE.TimeEnd,
PUBLIC.R_FLATFILE.TaxonName,
PUBLIC.R_FLATFILE.NameType,
PUBLIC.R_FLATFILE.LowerAbund,
PUBLIC.R_FLATFILE.UpperAbund,
PUBLIC.R_FLATFILE.AbundUnits,
PUBLIC.R_FLATFILE.AreaSize,
PUBLIC.R_FLATFILE.AreaUnits,
PUBLIC.R_FLATFILE.Notes
from PUBLIC.R_FLATFILE


);

CREATE TABLE r_site
(
aekosid CHAR(120),
longitude character varying,
latitude character varying,
crs character varying,
reliab character varying,
observedLanduse character varying,
derivedlanduse character varying,
vegetationtype character varying,
measuredslope character varying,
measuredaspect character varying,
measuredaltitude character varying,
derivedaltitude character varying,
calculatedVegetationCoverForSite character varying,
calculatedVegetationCoverForSiteUnits character varying,
samplingType character varying
      );

COPY r_site FROM '/Users/a1618290/IngestionData/sPlots/r_sites.csv' DELIMITER ';' CSV;

COPY r_speciesobservations to '/Users/a1618290/IngestionData/sPlots/r_speciesobservations.csv' delimiters',';


pg_dump --host localhost --port 5433 --username "aekos" --no-password  --format plain --verbose --file "/Users/a1618290/IngestionData/sPlots/r_site_speciesobservations.sql" --table "public.r_site" --table "public.r_speciesobservations" "sPlots"