| Src |
CREATE OR REPLACE FUNCTION sesar_fill_geom_latlong(sample_id_min integer, sample_id_max integer)
RETURNS boolean AS
$$
DECLARE
geom Geometry;
okay boolean;
curInfo RECORD;
BEGIN
geom :=null;
RAISE NOTICE 'Fill_Geom_Latlong()';
-- Loop through each sample which has latitude and longitude 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 latitude is not null and longitude is not null and (latitude<=90 and latitude>=-90) and (longitude<=180 and longitude>=-180) and sample_id >= sample_id_min and sample_id < sample_id_max
LOOP
-- Set geom object. If end and start longitude or latitude are different, the point will be the center point of line string.
geom := ST_SetSRID(ST_MakePoint(curInfo.longitude,curInfo.latitude),4326);
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
if curInfo.longitude_end<=180 and curInfo.longitude_end>=-180
then
-- 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;
end if;
else
If curInfo.longitude_end IS NULL
then
if curInfo.latitude_end<=90 and curInfo.latitude_end>=-90
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;
end if;
else
if curInfo.latitude_end<=90 and curInfo.latitude_end>=-90 and curInfo.longitude_end<=180 and curInfo.longitude_end>=-180
then
-- 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;
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;
|
