//MAP HERE THE VALUES BETWEEN nativeness_fk, life_stage_fk and occurrence_status_fk between the original source and //the SDR common data model $sql = <<<SQL insert into distribution_unit(occurrence_status_fk,start_day_in_year,end_day_in_year,distribution_fk,nativeness_fk,life_stage_fk,interpreted_geom,status_tags_fk) select CASE WHEN 1=1 THEN 1 ELSE 0 END as occurrence_status_fk, start_day as start_day_in_year, end_day as end_day_in_year, d.id as distribution_fk, CASE WHEN 1=1 THEN 6 ELSE 0 END as nativeness_fk, CASE WHEN 1=1 THEN null ELSE 1 END as life_stage_fk, the_geom as interpreted_geom, st.id as status_tags_fk from imported_shapefile as ish inner join distribution as d on ish.original_name_id=d.original_name_id left join status_tags as st on ish.status=st.tag WHERE d.resource_fk={$resourceId} SQL; runSqlCommand($sql); //remove temporary tables echo "\n\nFINISHED!!!\n\n"; runSqlCommand("DROP TABLE imported_ecat_names"); runSqlCommand("DROP TABLE imported_shapefile"); runSqlCommand("vacuum analyze resource"); runSqlCommand("vacuum analyze distribution"); runSqlCommand("vacuum analyze status_tags"); runSqlCommand("vacuum analyze distribution_unit"); runSqlCommand("vacuum analyze name_usage"); function runSqlCommand($command) { global $psqlfol, $host, $dbname, $user, $pass; exec("{$psqlfol}/psql -h{$host} -d{$dbname} -U{$user} -c\"{$command}\""); }
\tWHEN name='Alberta' THEN 'CA-AB' \tWHEN name='Manitoba' THEN 'CA-MB' \tWHEN name='British Columbia' THEN 'CA-BC' \tWHEN name='Yukon Territory' THEN 'CA-YT' \tWHEN name='Saskatchewan' THEN 'CA-SK' \tWHEN name='Quebec' THEN 'CA-QC' \tWHEN name='New Brunswick' THEN 'CA-NB' \tWHEN name='Northwest Territories' THEN 'CA-NT' \tWHEN name='Nova Scotia' THEN 'CA-NS' \tWHEN name='Ontario' THEN 'CA-ON' \tWHEN name='Nunavut' THEN 'CA-NU' \tWHEN name='Prince Edward Island' THEN 'CA-PE' ELSE null END as area_code, name as area_name, {$namedAreareferenceId} as named_area_reference_fk from imported_areas_shapefile WHERE name in ('Alberta','Manitoba','British Columbia','Yukon Territory','Saskatchewan','Quebec','New Brunswick','Northwest Territories','Nova Scotia','Ontario','Nunavut','Prince Edward Island') AND area_code SQL; $sql = "UPDATE named_area SET named_area_geom_fk = (SELECT id from named_area_geom WHERE temp_id=named_area.area_code) WHERE named_area_geom_fk is null"; runSqlCommand($sql); runSqlCommand("ALTER TABLE named_area_geom DROP COLUMN temp_id"); runSqlCommand("DROP TABLE imported_areas_shapefile"); runSqlCommand("vacuum analyze named_area"); runSqlCommand("vacuum analyze named_area_geom"); echo "\n\nFINISHED!!!\n\n"; function runSqlCommand($command) { global $psqlfol, $host, $dbname, $user, $pass; exec("{$psqlfol}/psql -h{$host} -d{$dbname} -U{$user} -c\"{$command}\""); }