<!DOCTYPE html><html><head>
<style type="text/css">"body { font-family:'Times New Roman'; font-size:13px"}</style>
</head>
<body style="margin-right: 4px; margin-top: 4px; margin-left: 4px; margin-bottom: 1px; line-height: normal; font-variant: normal"><div>Bob,</div><div><br></div><div>Yeah, sorry, didn't spot the aliasing of part3 and part4 but you got it anyway.</div><div><br></div><div>Can PostgreSQL/PostGIS let you now create a function based index over the computed geometry column?</div><div><br></div><div>S<br>On Tue, 06 Mar 2012 10:28:55 +1100, Bob Basques <Bob.Basques@ci.stpaul.mn.us> wrote:<br></div><br><blockquote style="margin: 0 0 0.80ex; border-left: #0000FF 2px solid; padding-left: 1ex">
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Ha, success!!!</font> </p>
<br>
<p style="margin-bottom: 0; margin-top: 0">
<font face="Comic Sans MS" size="3">Coll, Thanks Simon (A LOT !!)</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"> ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom,</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>
</blockquote><br><br><br><div id="M2Signature"><div>-- </div><div>Holder of "2011 Oracle Spatial Excellence Award for Education and Research."<br>SpatialDB Advice and Design, Solutions Architecture and Programming,<br>Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional<br>Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.<br>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.<br>Website: www.spatialdbadvisor.com<br> Email: simon@spatialdbadvisor.com<br> Voice: +61 362 396397<br>Mobile: +61 418 396391<br>Skype: sggreener<br>Longitude: 147.20515 (147° 12' 18" E)<br>Latitude: -43.01530 (43° 00' 55" S)<br>GeoHash: r22em9r98wg<br>NAC:W80CK 7SWP3</div></div></body></html>