SQL tables views

• biosample created by extraction step (Lambda)
• biosample_geocode created by geocoding step (EC2)
• biosample_geo_coordinates:

  CREATE MATERIALIZED VIEW biosample_geo_coordinates AS
          select biosample_id,
              coordinate_x,
              coordinate_y,
              b.geo_text_extracted as from_text
          from biosample b
          inner join biosample_geocode bgeo
              on (b.geo_text_extracted = bgeo.geo_text_extracted)
          where coordinate_x is not null
      union all
          select biosample_id,
              geo_coordinate_x as coordinate_x,
              geo_coordinate_y as coordinate_y,
              NULL as from_text
          from biosample
          where geo_coordinate_x is not null
  
  CREATE INDEX biosample_geo_coordinates_biosample_id_index ON biosample_geo_coordinates (biosample_id);

• srarun_geo_coordinates:

  CREATE MATERIALIZED VIEW srarun_geo_coordinates AS
      select run as sra_id,
          bio_sample as biosample_id,
          release_date,
          coordinate_x, coordinate_y, from_text
      from srarun
      inner join biosample_geo_coordinates bgeo
          on (srarun.bio_sample = bgeo.biosample_id)
  
  CREATE INDEX srarun_geo_coordinates_sra_id_index ON srarun_geo_coordinates (sra_id);