| Src |
CREATE OR REPLACE FUNCTION sesar_update_geom_latlong()
RETURNS boolean AS
$$
DECLARE
geom Geometry;
okay boolean;
curInfo RECORD;
BEGIN
geom :=null;
RAISE NOTICE 'Update_Geom_Latlong()';
-- Loop through each sample which has latitude and longitude information but no geom_latlong information.
-- geom_latlong column will be filled according latitude and longitude values.
FOR curInfo IN
select sample_id, latitude, longitude, latitude_end, longitude_end
from sample
where geom_latlong is null and latitude is not null and longitude is not null and (latitude<=90 and latitude>=-90) and (longitude<=180 and longitude>=-180) and (latitude_end<=90 and latitude_end>=-90) and (longitude_end<=180 and longitude_end>=-180)
LOOP
-- Set geom object. If end and start longitude or latitude are different, the point will be the center point of line string.
If curInfo.latitude_end IS NULL
then
If curInfo.longitude_end IS NULL
then
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,curInfo.latitude),4326);
RAISE NOTICE 'Case: both latitude_end and longitude_end are null sample_id=%d', curInfo.sample_id;
else
RAISE NOTICE 'Case: latitude_end is null and longitude is not null sample_id=%d', curInfo.sample_id;
if(( @(curInfo.longitude*100000) - @(curInfo.longitude_end*100000) )>1)
then
geom := ST_SetSRID(ST_MakePoint((curInfo.longitude+curInfo.longitude_end)/2.0,curInfo.latitude),4326);
RAISE NOTICE ': longitude_end=%f and longitude=%f . It is average is %f',(curInfo.longitude+curInfo.longitude_end)/2.0;
else
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,curInfo.latitude),4326);
RAISE NOTICE '---: longitude_end and longitude are the same';
end if;
end if;
else
If curInfo.longitude_end IS NULL
then
RAISE NOTICE 'Case: latitude_end is not null and longitude_end is null sample_id=%d', curInfo.sample_id;
if(( @(curInfo.latitude*100000) - @(curInfo.latitude_end*100000) )>1)
then
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,(curInfo.latitude+curInfo.latitude_end)/2.0),4326);
RAISE NOTICE '*: latitude_end=%f and latitude=%f and its average = %f',curInfo.latitude,curInfo.latitude_end,(curInfo.latitude+curInfo.latitude_end)/2.0;
else
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,curInfo.latitude),4326);
RAISE NOTICE '---: latitude_end and latitude are the same';
end if;
else
RAISE NOTICE 'Case: both latitude_end and longitude_end are not null sample_id=%d', curInfo.sample_id;
if( ( @(curInfo.latitude*100000) - @(curInfo.latitude_end*100000) )>1 AND ( @(curInfo.longitude*100000) - @(curInfo.longitude_end*100000) )>1 )
then
geom := ST_SetSRID(ST_MakePoint((curInfo.longitude+curInfo.longitude_end)/2.0,(curInfo.latitude+curInfo.latitude_end)/2.0),4326);
RAISE NOTICE '**: [ latitude=%f latitude_end=%f ] [ longitude=%f and lognitude_end=%f ]',curInfo.latitude,curInfo.latitude_end,curInfo.longitude,curInfo.longitude_end;
else
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,curInfo.latitude), 4326);
RAISE NOTICE '---: latitude_end and latitude are the same. longitude and lognitude_end are the same';
end if;
end if;
end if;
-- Update the geometry column
begin
update sample set geom_latlong = geom where sample_id = curInfo.sample_id ;
RAISE NOTICE '>>> Update sample where sample_id=%d', curInfo.sample_id;
exception
when OTHERS then
RAISE NOTICE 'Error: update sample where sample_id=%d', curInfo.sample_id;
end;
End LOOP;
RETURN true::boolean;
END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;
|
