<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2769" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Dear list,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I have a PostgreSQL table as follows:</FONT></DIV>
<DIV><FONT face=Arial size=2>CREATE TABLE lineup<BR>(<BR> lineup_id int4
NOT NULL DEFAULT nextval('lineup_lineup_id_seq'::regclass),</FONT></DIV>
<DIV><FONT face=Arial size=2> state char(1) NOT NULL DEFAULT
'T'::bpchar,<BR> gps_lat_degree int4,<BR> gps_long_degree
int4,<BR> gps_altitude int4,</FONT></DIV>
<DIV><FONT face=Arial size=2> gps_lat_hemisphere char(1),<BR>
gps_long_hemisphere char(1),</FONT></DIV>
<DIV><FONT face=Arial size=2> gps_lat_minutes float8,</FONT></DIV>
<DIV><FONT face=Arial size=2> gps_long_minutes float8,</FONT></DIV>
<DIV><FONT face=Arial size=2> CONSTRAINT lineup_gps_lat_hemisphere_check
CHECK (gps_lat_hemisphere = 'S'::bpchar OR gps_lat_hemisphere =
'N'::bpchar),<BR> CONSTRAINT lineup_gps_long_hemisphere_check CHECK
(gps_long_hemisphere = 'E'::bpchar OR gps_long_hemisphere =
'W'::bpchar),<BR> CONSTRAINT lineup_gps_magn_hemisphere_check CHECK
(gps_magn_hemisphere = 'E'::bpchar OR gps_magn_hemisphere =
'W'::bpchar),<BR> CONSTRAINT lineup_state_check CHECK (state = 'T'::bpchar
OR state = 'O'::bpchar OR state = 'F'::bpchar OR state = 'S'::bpchar)<BR>)
<BR>WITH OIDS;<BR>ALTER TABLE lineup OWNER TO postgres;<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2>How can I generate the POINT geometry column in
order to contain the POINT object?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I experimented with Views but I doubt this is the
way to go</FONT></DIV>
<DIV><FONT face=Arial size=2>I could display the POINT construction such as
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>CREATE OR REPLACE VIEW stations_pos
AS:<BR> 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"<BR> FROM
lineup;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>which gives query answers such as:
POINT(18:44.2333W 42:31.3667S)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I tried to convert this string based definition to
POINT object but it did not work (I</FONT><FONT face=Arial size=2> am not
sure if this representation is ok in PostGIS):</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I doubt this is the way to go. This might give a
big overhead and slow down the database.</FONT></DIV>
<DIV><FONT face=Arial size=2>Any ideas?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thanks for your help!</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Cheers,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Carlo</DIV></FONT></BODY></HTML>