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

[Bob Basques]

Dang LAT/LON LON/LAT differences. 

This works now, notice that the LAT/LON was switched around to LON/LAT.: 

-- PostGIS likes LON/LAT (NOT LAT/LON) 
-- DROP VIEW public.plots; 

CREATE VIEW plots AS 
    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_Transform(ST_SetSRID(ST_MakePoint(part4::numeric, part3::numeric),4326), 200068) 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; 


bobb 



>>> "Bob Basques" <Bob.Basques at ci.stpaul.mn.us> wrote:


Yeah, that was my latest fix, I don't have user rights (yet) to run against the Spatial_ref_sys table, so waiting for someone else to run it. 


bobb 




>>> Mike Toews <mwtoews at gmail.com> wrote:


On 7 March 2012 06:10, Bob Basques <Bob.Basques at ci.stpaul.mn.us> wrote:
> ...
>            ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)), 200068) as geom_city
> ...
> ERROR:  function st_transform(geometry) does not exist
> LINE 4:            ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa...

It looks like "4326)), 200068)" should be "4326), 200068))"

-Mike
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120306/222fde7b/attachment.html>