samples.sql

begin;

UPDATE osdregistry.samples
   SET nitrate='nan', 
       nitrite='nan',
       ammonium='nan',
       phosphate='nan',
       silicate='nan'
 WHERE submission_id = 215 and osd_id = 117 
 returning *
;


UPDATE osdregistry.samples
   SET ammonium=61,
       curation_remark='assume ammonium to also be the same as submision 168',
       curator = 'rkottman'
 WHERE submission_id = 169 and osd_id = 4
 returning *
;



select gid,label, id,
       st_distance(geog,
                   ST_GeogFromText('SRID=4326;POINT(' ||  -149.53985 || ' ' || -17.2894 || ')')
       )as dist_m
       
 from marine_regions_stage.iho
where st_dwithin(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  -149.53985 || ' ' ||
			  -17.2894 || ')'),
	 iho.geog, 100000
	 );



select gid,label, id,
       st_distance(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  33 || ' ' ||
			   35 || ')'),
	 iho.geog
	  ) as dist_m
 from marine_regions_stage.iho
where st_dwithin(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  33 || ' ' ||
			   35 || ')'),
	 iho.geog, 100000
	  ) ;



select gid,label, id,
       st_distance(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  -81.09 || ' ' ||
			   31.98 || ')'),
	 iho.geog
	  ) as dist_m
 from marine_regions_stage.iho
where st_dwithin(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  -81.09 || ' ' ||
			   31.98 || ')'),
	 iho.geog, 100000
	  ) ;


select gid,label, id,gazetteer,
       st_distance(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  -2.899 || ' ' ||
			   43.27 || ')'),
	 iho.geog
	  ) as dist_m
 from marine_regions_stage.iho
where st_dwithin(
         ST_GeogFromText('SRID=4326;POINT(' ||
	                  -2.899 || ' ' ||
			   43.27 || ')'),
	 iho.geog, 100000
	  ) ;

rollback;