[postgis-users] Generating POINT geometry column for PostGIS from split latitude/longitude component fields.

[Carlo Tronnberg]

Dear list,

I have a PostgreSQL table as follows:
CREATE TABLE lineup
(
  lineup_id int4 NOT NULL DEFAULT nextval('lineup_lineup_id_seq'::regclass),
  state char(1) NOT NULL DEFAULT 'T'::bpchar,
  gps_lat_degree int4,
  gps_long_degree int4,
  gps_altitude int4,
  gps_lat_hemisphere char(1),
  gps_long_hemisphere char(1),
  gps_lat_minutes float8,
  gps_long_minutes float8,
  CONSTRAINT lineup_gps_lat_hemisphere_check CHECK (gps_lat_hemisphere = 'S'::bpchar OR gps_lat_hemisphere = 'N'::bpchar),
  CONSTRAINT lineup_gps_long_hemisphere_check CHECK (gps_long_hemisphere = 'E'::bpchar OR gps_long_hemisphere = 'W'::bpchar),
  CONSTRAINT lineup_gps_magn_hemisphere_check CHECK (gps_magn_hemisphere = 'E'::bpchar OR gps_magn_hemisphere = 'W'::bpchar),
  CONSTRAINT lineup_state_check CHECK (state = 'T'::bpchar OR state = 'O'::bpchar OR state = 'F'::bpchar OR state = 'S'::bpchar)
) 
WITH OIDS;
ALTER TABLE lineup OWNER TO postgres;

I want to extract the lat/lon coordinates via PostGIS for plotting a point (or symbol) for each record, the color of which is determined by the value of the state field. My problem is the the longitude and latitude are split into their components( latitude degrees + latitude minutes + latitude hemisphere...) given from a GPS system.
How can I generate the POINT geometry column in order to contain the POINT object?

I experimented with Views but I doubt this is the way to go
I could display the POINT construction such as 

CREATE OR REPLACE VIEW stations_pos AS:
 SELECT ((((((((('POINT('::text || lineup.gps_long_degree::text) || ':'::text) || lineup.gps_long_minutes::text) || lineup.gps_long_hemisphere::text) || ' '::text) || lineup.gps_lat_degree::text) || ':'::text) || lineup.gps_lat_minutes::text) || lineup.gps_lat_hemisphere::text) || ')'::text AS "position"
   FROM lineup;

which gives query answers such as:  POINT(18:44.2333W 42:31.3667S)

I tried to convert this string based definition to POINT object but it did not work (I am not sure if this representation is ok in PostGIS):

SELECT GeomFromText(((((((((('POINT('::text || lineup.gps_long_degree::text || ':'::text) || lineup.gps_long_minutes::text) || lineup.gps_long_hemisphere::text) || ''::text) || lineup.gps_lat_degree::text) || ':'::text) || lineup.gps_lat_minutes::text) || lineup.gps_lat_hemisphere::text || ')'::text), 4326)) AS "position" FROM lineup;

I doubt this is the way to go. This might give a big overhead and slow down the database.
Any ideas?

Thanks for your help!

Cheers,

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