[postgis-users] Spatial column from text via a VIEW, Possible?
Bob.Basques at ci.stpaul.mn.us
Mon Mar 5 14:46:19 PST 2012
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:
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
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
like '>Plot:%') AS first_pass;
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users