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

[Nicolas Ribot]

What about adding a true geometric column to your table with the
make_point() function ?

Maybe a trigger could do it automatically each time you insert a new
value in your table.
According to the north/south attribute, you will have to sign the
numerical values for lat/long.

(BTW, the textual representation for a point (WKT) only expects
numerical values for coordinates, so DMS values should be converted
into decimal degrees)

On 11/25/05, Carlo Tronnberg <temp at chello.hu> wrote:
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>