Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSION www_fdw;
CREATE SERVER www_fdw_server_gpx
FOREIGN DATA WRAPPER www_fdw
OPTIONS ( uri 'http://thinkspatial.com.au/xml/2012-05-02.gpx',
response_type 'xml',
response_deserialize_callback 'gpx_deserialize_callback');
CREATE USER MAPPING FOR current_user SERVER www_fdw_server_gpx;
CREATE FOREIGN TABLE www_fdw_server_gpx (
"id" integer,
"lat" numeric,
"lng" numeric,
"ele" numeric,
"time" timestamp,
"speed" numeric
) SERVER www_fdw_server_gpx;
CREATE OR REPLACE FUNCTION gpx_deserialize_callback(options WWWFdwOptions, response text)
RETURNS SETOF www_fdw_server_gpx AS $$
DECLARE
rows RECORD;
stime timestamp;
lat numeric;
lng numeric;
ele numeric;
speed numeric;
count integer := 0;
r RECORD;
BEGIN
FOR rows IN SELECT unnest(xpath('//gpx:trkpt', response::xml, ARRAY[ARRAY['gpx', 'http://www.topografix.com/GPX/1/1']]))
LOOP
count := count+1;
ele := CAST(CAST((xpath('/trkpt/ele/text()', rows.unnest))[1] As varchar) As numeric);
lng := CAST(CAST((xpath('/trkpt/@lon', rows.unnest))[1] As varchar) As numeric);
lat := CAST(CAST((xpath('/trkpt/@lat', rows.unnest))[1] As varchar) As numeric);
speed := CAST(CAST((xpath('/trkpt/speed/text()', rows.unnest))[1] As varchar) As numeric);
stime := TO_TIMESTAMP(CAST((xpath('/trkpt/time/text()', rows.unnest))[1] As varchar), 'YYYY-MM-DD HH24:MI:SS ');
r := ROW(count,lat, lng, ele, stime, speed);
RETURN NEXT r;
END LOOP;
END; $$ LANGUAGE PLPGSQL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment