| Src |
CREATE OR REPLACE FUNCTION sesar_sample_geom_latlong()
RETURNS trigger AS
$$
BEGIN
-- Check latitude and longitude are given
IF NEW.latitude IS NULL THEN
RAISE NOTICE 'The sample does not have latitude information';
RETURN NEW;
END IF;
IF NEW.longitude IS NULL THEN
RAISE NOTICE 'The samples does not have longitude information';
RETURN NEW;
END IF;
-- Check latitude and longitude are within range
IF (NEW.latitude>90 or NEW.latitude<-90) THEN
RAISE EXCEPTION 'The latitude is not correct. latitude=%lf',NEW.latitude;
END IF;
IF (NEW.longitude>180 or NEW.longitude<-180) THEN
RAISE EXCEPTION 'The longitude is not correct. longitude=%lf',NEW.longitude;
END IF;
-- Update the geom_latlong
IF NEW.latitude_end IS NULL THEN
IF NEW.longitude_end IS NULL THEN
NEW.geom_latlong := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude),4326);
ELSE
IF (NEW.longitude_end>180 or NEW.longitude_end<-180) THEN
RAISE EXCEPTION 'The longitude_end is not correct. longitude_end=%lf',NEW.longitude_end;
END IF;
IF(( @(NEW.longitude*100000) - @(NEW.longitude_end*100000) )>1)THEN
NEW.geom_latlong := ST_SetSRID(ST_MakePoint((NEW.longitude+NEW.longitude_end)/2.0,NEW.latitude),4326);
ELSE
NEW.geom_latlong := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude),4326);
END IF;
END IF;
ELSE
IF NEW.longitude_END IS NULL THEN
IF(( @(NEW.latitude*100000) - @(NEW.latitude_end*100000) )>1)THEN
IF (NEW.latitude_end>90 or NEW.latitude_end<-90) THEN
RAISE EXCEPTION 'The latitude_end is not correct. latitude_end=%lf',NEW.latitude_end;
END IF;
NEW.geom_latlong := ST_SetSRID(ST_MakePoint(NEW.longitude,(NEW.latitude+NEW.latitude_end)/2.0),4326);
ELSE
NEW.geom_latlong := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude),4326);
END IF;
ELSE
IF( ( @(NEW.latitude*100000) - @(NEW.latitude_end*100000) )>1 AND ( @(NEW.longitude*100000) - @(NEW.longitude_end*100000) )>1 ) THEN
IF (NEW.longitude_end>180 or NEW.longitude_end<-180) THEN
RAISE EXCEPTION 'The longitude_end is not correct. longitude_end=%lf',NEW.longitude_end;
END IF;
IF (NEW.latitude_end>90 or NEW.latitude_end<-90) THEN
RAISE EXCEPTION 'The latitude_end is not correct. latitude_end=%lf',NEW.latitude_end;
END IF;
NEW.geom_latlong := ST_SetSRID(ST_MakePoint((NEW.longitude+NEW.longitude_end)/2.0,(NEW.latitude+NEW.latitude_end)/2.0),4326);
ELSE
NEW.geom_latlong := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude),4326);
END IF;
END IF;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;
|
