Create a gist now

Instantly share code, notes, and snippets.

@jamesrom /Blog SQL
Last active Aug 29, 2015

What would you like to do?
--http://thinkspatial.com.au/xml/2012-05-02.gpx
declare
v_kml clob;
begin
WITH DATA AS (
SELECT x1,
y1,
d,
DECODE(d1,'0',TO_CHAR(d,'YYYY-MM-DD"T"HH24:MI:SS"Z"'),d1) d1,
DECODE(d2,'0',TO_CHAR(d,'YYYY-MM-DD"T"HH24:MI:SS"Z"'),d2) d2,
speed,
ROUND(ATAN2(COS(y1)*SIN(y2)-SIN(y1)*COS(y2)*COS(x2-x1),SIN(x2-x1)*COS(x2)) * 57.2957795,5)-90 brg
FROM (
SELECT extractvalue(value(tp), '*/@lon') x1,
LEAD(extractvalue(value(tp), '*/@lon'), 1, 0) OVER (ORDER BY rownum) x2,
extractvalue(value(tp), '*/@lat') y1,
LEAD(extractvalue(value(tp), '*/@lat'), 1, 0) OVER (ORDER BY rownum) y2,
extractValue(value(tp), '*/ele', 'xmlns="http://www.topografix.com/GPX/1/1"') z,
to_date(
extractValue(value(tp),
'*/time', 'xmlns="http://www.topografix.com/GPX/1/1"'),
'YYYY-MM-DD"T"HH24:MI:SS"Z"') d,
LAG(extractvalue(value(tp), '*/time', 'xmlns="http://www.topografix.com/GPX/1/1"'), 1, 0) OVER (ORDER BY rownum) d1,
LEAD(extractvalue(value(tp), '*/time', 'xmlns="http://www.topografix.com/GPX/1/1"'), 1, 0) OVER (ORDER BY rownum) d2,
extractValue(value(tp), '*/speed', 'xmlns="http://www.topografix.com/GPX/1/1"') speed
FROM TABLE(
XMLSEQUENCE(
EXTRACT(httpuritype('http://thinkspatial.com.au/media/2012-05-02.gpx').getxml(),'//trkpt','xmlns="http://www.topografix.com/GPX/1/1"')
)
) tp)
)
SELECT XMLELEMENT ("kml",
XMLATTRIBUTES ('http://www.opengis.net/kml/2.2' AS "xmlns",
'http://www.google.com/kml/ext/2.2' AS "xmlns:gx",
'http://www.opengis.net/kml/2.2' AS "xmlns:kml",
'http://www.w3.org/2005/Atom' AS "xmlns:atom"),
XMLELEMENT ("Document",
XMLELEMENT("name",'GPX Conversion'),
XMLELEMENT("open",1),
XMLELEMENT("StyleMap",
XMLATTRIBUTES('gpx' as "id"),
XMLELEMENT("Pair",
XMLELEMENT("key",'normal'),
XMLELEMENT("styleUrl",'#gpx_n')
),
XMLELEMENT("Pair",
XMLELEMENT("key",'highlight'),
XMLELEMENT("styleUrl",'#gpx_h')
)
),
XMLELEMENT("Style",
XMLATTRIBUTES('gpx_n' as "id"),
XMLELEMENT("BalloonStyle",
XMLELEMENT("bgColor",'ff915f36'),
XMLELEMENT("text",
XMLCDATA(
'<html lang="en">
<head>
<meta charset="utf-8" />
<title>Speedometer</title>
<script src="http://thinkspatial.com.au/media/speedometer.js"></script>
<style type="text/css"/>
p {
font-family: monospace;
font-size: 18px;
text-align: center;
width: 220px;
color: ffffff;
}
</style>
</head>
<body onload="draw($[speed]);">
<p>$[speed] mph</p>
<canvas id="tutorial" width="220" height="120">Canvas not available.</canvas>
<div>
</div>
</body>
</html>'
)
)
),
XMLELEMENT("LabelStyle",
XMLELEMENT("color",'e5ffffff'),
XMLELEMENT("scale",'0')
),
XMLELEMENT("IconStyle",
XMLELEMENT("Icon",
XMLELEMENT("href",'http://thinkspatial.com.au/media/car.png')
)
)
),
XMLELEMENT("Style",
XMLATTRIBUTES('gpx_h' as "id"),
XMLELEMENT("LabelStyle",
XMLELEMENT("color",'e5ffffff'),
XMLELEMENT("scale",'0.8'),
XMLELEMENT("bgColor",'00e6b922')
),
XMLELEMENT("IconStyle",
XMLELEMENT("Icon",
XMLELEMENT("href",'http://thinkspatial.com.au/media/car.png')
)
)
),
XMLELEMENT ("Folder",
XMLELEMENT ("name",'Waypoints'),
XMLAGG (
XMLELEMENT ("Placemark",
XMLATTRIBUTES('trg'||to_char(rownum) as "id"),
XMLELEMENT ("name", 'Point:'||rownum),
XMLELEMENT ("Description", 'Point:'||rownum),
XMLELEMENT ("visibility", 1),
XMLELEMENT ("styleUrl", '#gpx'),
XMLELEMENT ("TimeStamp",
XMLELEMENT ("when",to_char(d,'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
),
XMLELEMENT ("ExtendedData",
XMLELEMENT ("Data",
XMLATTRIBUTES('speed' as "name"),
XMLELEMENT ("value", speed)
)
),
XMLELEMENT ("Point",
XMLELEMENT ("coordinates",x1||','||y1)
)
)
) -- end xmlagg
), -- end folder
XMLELEMENT ("gx:Tour",
XMLELEMENT ("name",'Double-click me!'),
XMLELEMENT ("gx:Playlist",
XMLAGG(
XMLCONCAT(
XMLELEMENT ("gx:AnimatedUpdate",
XMLELEMENT ("Update",
XMLELEMENT ("Change",
XMLELEMENT ("Placemark",
XMLATTRIBUTES('trg'||to_char(rownum) as "targetId"),
XMLELEMENT ("gx:balloonVisibility",1)
)
)
)
),
XMLELEMENT("gx:Wait",
XMLELEMENT("gx:duration",0.25)
),
XMLELEMENT("gx:FlyTo",
XMLELEMENT("gx:duration",1),
XMLELEMENT("gx:flyToMode",'smooth'),
XMLELEMENT("LookAt",
XMLELEMENT("gx:TimeSpan",
XMLELEMENT("begin",d1),
XMLELEMENT("end",d2)
),
XMLELEMENT("longitude",x1),
XMLELEMENT("latitude",y1),
XMLELEMENT("altitude",0),
XMLELEMENT("heading",brg),
XMLELEMENT("tilt",65),
XMLELEMENT("range",300),
XMLELEMENT("gx:altitudeMode",'relativeToGround')
)
),
XMLELEMENT("gx:Wait",
XMLELEMENT("gx:duration",0.25)
),
XMLELEMENT ("gx:AnimatedUpdate",
XMLELEMENT ("Update",
XMLELEMENT ("Change",
XMLELEMENT ("Placemark",
XMLATTRIBUTES('trg'||to_char(rownum) as "targetId"),
XMLELEMENT ("gx:balloonVisibility",0)
)
)
)
)
)
) -- end xmlagg
)
)
)
).getClobVal() doc
INTO v_kml
FROM DATA;
DBMS_XSLPROCESSOR.CLOB2FILE(v_kml, 'DATA_PUMP_DIR', 'gpx2kml.kml'); -- Or an appropriate Oracle directory.
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment