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

[Bob Basques]

Hi, Simon, 

Cool, almost there, I swear I tried something similar to that this afternoon . . . 

Ok, this is good, that got it further, now I need to change the data type, from TEXT to NUMERIC,  . . . . 

ERROR:  column "lon" does not exist 
LINE 7:        ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4... 
                                       ^

>>> "Simon Greener" <simon at spatialdbadvisor.com> wrote:


ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom, 



\ 

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, 
                ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric),4326) as geom,
 
       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; 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120305/12bc20ef/attachment.html>