Functions
Postgres functions er et generelt begreb, som dækker over kode, som eksekveres proceduralt. Modsat Oracle skelnes ikke mellem Procedurer og Functions.
- Funktioner kan tage hvad som helst som parameter (atomare datatyper, recordstrukturer, tabelstrukturer)
- Funktioner kan returnere hvad som helst
- Funktioner kan skrives i mange sprog:
- PL/pgSQL
- PL/Python
- PL/R
Det mest normale er plpgsql
Jeg (Niels Kjøller) vil gerne anbefale at man så vidt muligt holder sig fra PL/Python, bl.a. fordi det af sikkerhedsmæssige årsager ikke kører på alle opsætninger, og at man får adgang til host-maskinen.
Kan man nøjes med PL/pgSQL, så er man rimeligt sikker på at det kører alle steder.
Eksempel: PL/pgSQL (simpel)
Hello world
CREATE FUNCTION hilsen(text, text)
RETURNS text AS
$$
SELECT $1 || ' ' || $2
$$ LANGUAGE SQL;
SELECT hilsen('hej', 'kbh')
Lav din egen special tilpassede 1000-METER-bufferzone
CREATE FUNCTION bufferzone(geometry)
RETURNS geometry AS
$$
SELECT ST_Buffer($1, 1000)
$$ LANGUAGE SQL;
SELECT st_area(bufferzone(geom)), area FROM att0902.bygninger;
Eksempel: PL/Python: Geokod med Google
Geokod en adresse med Google
CREATE PROCEDURAL LANGUAGE 'plpythonu'
CREATE EXTENSION plpython2u
-- Function: att0902.g_geocode(text)
-- DROP FUNCTION att0902.g_geocode(text);
CREATE OR REPLACE FUNCTION att0902.g_geocode(adresse text)
RETURNS geometry AS
$BODY$
import urllib
import urllib2
import simplejson
import os
geocode_url = 'http://maps.googleapis.com/maps/api/geocode/json?address='+urllib.quote_plus(adresse)+'&sensor=false&output=json'
georeq = urllib2.Request(geocode_url)
geo_response = urllib2.urlopen(georeq)
geocode = simplejson.loads(geo_response.read())
try:
geocode['status'] != 'ZERO_RESULTS'
data_lat = geocode['results'][0]['geometry']['location']['lat']
data_lng = geocode['results'][0]['geometry']['location']['lng']
except:
return None
geom = plpy.execute("select ST_SetSRID(ST_MakePoint(%s, %s),4326) as geom" % (data_lng, data_lat))
return geom[0]['geom']
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
ALTER FUNCTION att0902.g_geocode(text)
OWNER TO mbj;
SELECT st_astext(att0902.g_geocode('læstedet 6, Valby'));
Eksempel: PL/Python:Kald eSeptimas rutewebservice
-- Type: septima.osrm
-- DROP TYPE septima.osrm;
CREATE TYPE osrm AS
(route geometry,
drivingtime integer,
distance integer,
instructions text,
status text,
status_message text,
request text);
ALTER TYPE osrm
OWNER TO mbj;
-- Function: osrm_car(geometry, geometry, boolean, integer)
-- DROP FUNCTION osrm_car(geometry, geometry, boolean, integer);
CREATE OR REPLACE FUNCTION osrm_car(pointa geometry DEFAULT st_geomfromtext('POINT(12.569920 55.677900)'::text, 4326), pointb geometry DEFAULT st_geomfromtext('POINT(10.199920 57.475600)'::text, 4326), instructions boolean DEFAULT false, outputsrid integer DEFAULT 25832)
RETURNS osrm AS
$BODY$
##TODO: check input geometrytypes or use centroids
from urllib2 import urlopen
from simplejson import loads
## need to set inputarguments as global variables
global pointa
global pointb
global instructions
global outputsrid
###urlparams ###
output = 'json'
zoomlevel = 20
##need to lowercase for url request
if instructions is False:
instructions = 'false'
else:
instructions = 'true'
host='http://routing.septima.dk/car/viaroute?' ### set this as needed
###Calculate lat/lons from input geometries ###
source_x = plpy.execute("SELECT ST_x('%s')" % pointa)
source_x = source_x [0]['st_x']
source_y= plpy.execute("SELECT ST_y('%s')" % pointa)
source_y = source_y [0]['st_y']
latlon1 = '%s,%s' % (source_y, source_x)
target_x = plpy.execute("SELECT ST_x('%s')" % pointb)
target_x = target_x [0]['st_x']
target_y= plpy.execute("SELECT ST_y('%s')" % pointb)
target_y = target_y [0]['st_y']
latlon2 = '%s,%s' % (target_y, target_x)
### request osrm with urlparamteres and lat/lons ###
f = (host+'instructions='+instructions+'&output='+output+'&z='+str(zoomlevel)+'&loc='+latlon1+'&loc='+latlon2+'&geomformat=cmp&alt=false')
try:
json = urlopen(f).read()
except:
return {"route": None, "drivingtime": None, "distance": None, "instructions": 'bad http request', "status": 'bad http request', "status_message": 'bad http request', "request": f}
try:
result = loads(json)
except:
return {"route": None, "drivingtime": None, "distance": None, "instructions": 'Unable to parse repsonse', "status": 'Unable to parse repsonse', "status_message": 'Unable to parse repsonse', "request": f}
### all went pretty well with basic http and start parsing response values###
drivingtime = result['route_summary']['total_time']
distance = result['route_summary']['total_distance']
instructions = result['route_instructions']
status= result['status']
status_message = result['status_message']
request = f
### check if status error is different from 0
if status <> 0:
return {"route": None, "drivingtime": None, "distance": None, "instructions": None, "status": status, "status_message": status_message, "request": f}
## Create Google polyline decoder function #######
def decode_line(encoded):
"""Decodes a polyline that was encoded using the Google Maps method.
See http://code.google.com/apis/maps/documentation/polylinealgorithm.html
This is a Python port of Mark McClure's JavaScript polyline decoder
(http://facstaff.unca.edu/mcmcclur/GoogleMaps/EncodePolyline/decode.js)
and Peter Chng's PHP polyline decode
(http://unitstep.net/blog/2008/08/02/decoding-google-maps-encoded-polylines-using-php/)
"""
encoded_len = len(encoded)
index, array, lat, lng = 0, [], 0, 0
while index < encoded_len:
b, shift, result = 0, 0, 0
while True:
b = ord(encoded[index]) - 63
index += 1
result |= (b & 0x1f) << shift
shift += 5
if b < 0x20:
break
dlat = ~(result >> 1) if result & 1 else result >> 1
lat += dlat
shift, result = 0, 0
while True:
b = ord(encoded[index]) - 63
index += 1
result |= (b & 0x1f) << shift
shift += 5
if b < 0x20:
break
dlng = ~(result >> 1) if result & 1 else result >> 1
lng += dlng
array.append((lng * 1e-5,lat * 1e-5))
return 'LINESTRING('+str(array).replace(","," ").replace("[(","").replace(") (",",").replace("]","")
### Deocede route geoemtry and create postgis geometry ###
linestringwkt = decode_line(result['route_geometry'])
if outputsrid == 4326:
linestringgeom = plpy.execute("select st_setsrid(st_geometryFromtext('%s'),4326) as geom" % linestringwkt)
else:
linestringgeom = plpy.execute("select st_transform(st_setsrid(st_geometryFromtext('%s'),4326),%s) as geom" % (linestringwkt, outputsrid))
linestringgeom = linestringgeom[0]['geom']
route = linestringgeom
return {"route": route, "drivingtime": drivingtime, "distance": distance, "instructions": instructions, "status": status, "status_message": status_message, "request": f}
$BODY$
LANGUAGE plpythonu VOLATILE
COST 100;
ALTER FUNCTION osrm_car(geometry, geometry, boolean, integer)
OWNER TO mbj;
SELECT * FROM osrm_car(att0902.g_geocode('læstedet 6, Valby'),att0902.g_geocode('parkbo 6, Valby'))
Øvelse
Byg en funktion der med en geometri som input returnerer et areal afrundet til nærmeste hele kvadratmeter.