Database Reference
In-Depth Information
5. Nowitwouldbeniceifourfunctioncouldacceptnotonlythecoordinatesof
apointbutalsoatruePostGISgeometryaswellasaninputparameter.For
thetemperatureofafeature,youcouldreturnthetemperatureoftheweath-
erstationclosesttothecentroidofthefeaturegeometry.Youcaneasilyget
thisbehaviorusingfunctionoverloading.Addanewfunction,withthesame
name,supportingaPostGISgeometrydirectlyasaninputparameter.Inthe
body of the function, call the previous function, passing the coordinates of
thecentroidofthegeometry.Notethatinthiscaseyoucanwritethefunction
without using Python, with the PL/PostgreSQL language:
CREATE OR REPLACE FUNCTION
chp08.GetWeather(geom geometry)RETURNS
float
AS $$
BEGIN
RETURN
chp08.GetWeather(ST_X(ST_Centroid(geom)),
ST_Y(ST_Centroid(geom)));
END;
$$ LANGUAGE plpgsql;
6. Now test the function, passing a PostGIS geometry to the function:
postgis_cookbook=# SELECT
chp08.GetWeather(ST_GeomFromText('POINT(-71.064544
42.28787)'));
getweather
------------
23.89
(1 row)
7. If you use the function on a PostGIS layer, you can pass the feature's geo-
metries to the function directly, using the overloaded function written in the
PL/pgSQL language:
postgis_cookbook=# SELECT name,
temperature, chp08.GetWeather(the_geom)
AS temperature2 FROM chp08.cities LIMIT 5;
name | temperature | temperature2
