<html>
<head>
</head>
<body style="font-size: 12pt; font-style: normal; margin-right: 4px; margin-top: 4px; font-family: Comic Sans MS; margin-left: 4px; margin-bottom: 1px; line-height: normal; font-weight: normal; font-variant: normal">
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Hi, Simon,</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Cool, almost there, I swear I tried something similar to that this afternoon . . .</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Ok, this is good, that got it further, now I need to change the data type, from TEXT to NUMERIC, . . . .</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">ERROR: column "lon" does not exist</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3">LINE 7: ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4...</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Courier" size="3"> ^</font><br><br>>>> "Simon Greener" <simon@spatialdbadvisor.com> wrote:<br> </p>
<table bgcolor="#f3f3f3" style="font-size: 1em; margin-right: 0; margin-top: 0; margin-left: 15px; margin-bottom: 0" border="0">
<tr>
<td>
<div style="border-left: solid 1px #050505; padding-left: 7px">
<p style="margin-bottom: 0; margin-top: 0">
ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom,<font face="Comic Sans MS" size="3"></font> </p>
<br> <br><br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">\</font> </p>
</div>
</td>
</tr>
</table>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">My (future) VIEW creation SQL so far:</font> </p>
<p style="margin-bottom: 0; margin-top: 0">
<br>
</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"> 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">
ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom,<font face="Courier" size="3"><br style="font-size: 3; font-family: Courier"></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>
</body>
</html>