SQL: update new column as geometric point from two columns lat, long_

Question

Background

I have a table hospitals_healthcenters_jointed_to_evacuationzones with following attributes:

• lat latitude
• long_ longitude

What I tried

I am trying to create a geometry datatype column Point that combines lat and long_

I used the following code that I found:

ALTER TABLE hospitals_healthcenters_jointed_to_evacuationzones
  ADD       shape Point
  ;

UPDATE  hospitals_healthcenters_jointed_to_evacuationzones
  SET   shape = Point("long_", "lat")

Error I got

But I get the following error:

ERROR:  function point(character varying, numeric) does not exist
LINE 4: SET     shape = Point("long_", "lat");
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 157

Answer 1

Assuming you are using PostgreSQL

Using PostGIS extension

With extension PostGIS installed there is the geography datatype for points with latitude and longitude.

To add a column:

ALTER TABLE hospitals_healthcenters_jointed_to_evacuationzones
  ADD COLUMN geo_point GEOGRAPHY(POINT,4326);

To update column geo_point for each record (based on existing coordinates in columns long, lat_):

UPDATE hospitals_healthcenters_jointed_to_evacuationzones
  SET geo_point = 'SRID=4326;POINT(' || long || ' ' || lat_ || ')';

The data value of point is given as text, see following example and explanation:

'SRID=4326;POINT(-71.104 42.315)'

• Spatial Referencing System ID (SRID) is specified as 4326 for WGS 84 (same as used by GPS, see Wikipedia)
• the geo-coordinates are specified as type POINT described by -71.104 42.315 in exactly this order: longitude, followed by space as separator, followed by latitude

Using native data types

Without PostGIS installed, you still have the geometric type point. Values of this type are constructed using the geometric conversion function point(double precision, double precision).

To add a column:

ALTER TABLE hospitals_healthcenters_jointed_to_evacuationzones
  ADD COLUMN geo_point POINT;

To update points for each record (based on existing coordinates in double typed columns long, lat_):

UPDATE hospitals_healthcenters_jointed_to_evacuationzones
  SET geo_point = point(long, lat_);

This (native) geometric point can later be converted to a geographic point if PostGIS is installed and should be used.

Answer 2

 ALTER TABLE hospitals_healthcenters_jointed_to_evacuationzones
     ADD COLUMN shape geometry(Point,4326);

    UPDATE  hospitals_healthcenters_jointed_to_evacuationzones
      SET   shape = st_Setsrid(st_Point("long_"::double precision, "lat"::double precision),4326);