<html>
<head>
</head>
<body style="margin-right: 4px; margin-top: 4px; margin-left: 4px; margin-bottom: 1px; line-height: normal; font-variant: normal">
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">All,</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">First posting, been looking for a while now, maybe not possible, or I'm not asking/searching with correct vocabulary.</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">I have a table with records like this that are fed from a Vendor source:</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">ID STAT RXTIME CMD</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">165 1 2012-03-05 08:14:09.095626 >Plot:11072940,2012-02-29 05:33:03.0,44.9677,-93.1268,4.10,0,Good;</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">via a VIEW I have it looking like this:</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">RXTIME CMD_TYPE ESN TIME_IDX LAT LON SPEED EVENT GOOD</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">2012-03-05 08:14:09.095626 Plot 11072940 2012-02-29 05:33:03.0 44.9677 -93.1268 4.10 0 Good</font><font face="Comic Sans MS" size="3"></font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Question, how can I (if possible) convert the LAT/LON fields into a Spatial column in the/a VIEW from the TEXT fields.</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">My (future) VIEW creation SQL so far:</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">select rxtime,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> ltrim(split_part(part1, ':', 1), '>') as cmd_type,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(part1, ':', 2) as esn,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> part2 as time_idx,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> part3 as lat,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> part4 as lon,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> part5 as speed,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> part6 as event,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> rtrim(part7, ';') as good</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> from</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> (select rxtime,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 1) as part1, </font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 2) as part2,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 3) as part3,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 4) as part4,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 5) as part5,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 6) as part6,</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> split_part(cmd, ',', 7) as part7</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> from </font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> cmdstpinfo</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> where cmd </font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> like '>Plot:%') AS first_pass;</font> </p>
<br> <br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">thanks</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">bobb</font><font face="Courier" size="3"></font> </p>
<br> <br>
</body>
</html>