Postgres functions er et generelt begreb, som dækker over kode, som eksekveres proceduralt. Modsat Oracle skelnes ikke mellem Procedurer og Functions.
Det mest normale er plpgsql
Hello world
CREATE FUNCTION hilsen(text, text)
RETURNS text AS
$$
SELECT $1 || ' ' || $2
$$ LANGUAGE SQL;
SELECT hilsen('hej', 'kbh')
Lav din egen specialtilpasses 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;
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'));
-- 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'))