[postgis-users] Spatial column from text via a VIEW, Possible?

[Bob Basques]

All, 

First posting, been looking for a while now, maybe not possible, or I'm not asking/searching with correct vocabulary. 

I have a table with records like this that are fed from a Vendor source: 

ID  STAT RXTIME                     CMD 
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; 

via a VIEW I have it looking like this: 

RXTIME                     CMD_TYPE ESN      TIME_IDX              LAT     LON      SPEED EVENT GOOD 
2012-03-05 08:14:09.095626 Plot     11072940 2012-02-29 05:33:03.0 44.9677 -93.1268 4.10  0     Good 

Question, how can I (if possible) convert the LAT/LON fields into a Spatial column in the/a VIEW from the TEXT fields. 

My (future) VIEW creation SQL so far: 

select rxtime, 
       ltrim(split_part(part1, ':', 1), '>') as cmd_type, 
       split_part(part1, ':', 2) as esn, 
       part2 as time_idx, 
       part3 as lat, 
       part4 as lon, 
       part5 as speed, 
       part6 as event, 
       rtrim(part7, ';') as good 
  from 
      (select rxtime, 
              split_part(cmd, ',', 1) as part1,  
              split_part(cmd, ',', 2) as part2, 
              split_part(cmd, ',', 3) as part3, 
              split_part(cmd, ',', 4) as part4, 
              split_part(cmd, ',', 5) as part5, 
              split_part(cmd, ',', 6) as part6, 
              split_part(cmd, ',', 7) as part7 
         from  
              cmdstpinfo 
        where cmd  
         like '>Plot:%') AS first_pass; 


thanks 

bobb 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120305/dbf3359a/attachment.html>