Creating geometry from lat/lon in table using PostGIS?

Newer versions of PostGIS allow the following, slightly more common, syntax:

ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);

Then use ST_SetSrid and ST_MakePoint to populate the column:

UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

See documentation here:

• https://postgis.net/docs/ST_SetSRID.html
• https://postgis.net/docs/ST_MakePoint.html

If you haven't already add a geometry column (assuming EPSG:4326 here):

SELECT AddGeometryColumn ('your_table','geom',4326,'POINT',2);

Then UPDATE, passing in the names of your x and y fields:

UPDATE yourtable SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

This will update the entire table, unless you include a WHERE clause

As a point of clarity for future users, lat / long coordinates are technically Geography (not Geometry). It is better convention to name geometry/geography database fields in accordance with the type of the object. Storing what is technically Geography in a column called 'geom' does not allow you to glance at the field and know what is actually there.

Data in lat / long are geography (i.e., geographic coordinate system) SRID: 4326

Data in a projected coordinate system like UTM, Web Mercator, State Plane, etc. are geometry (i.e., Cartesian coordinates).

Reference: http://postgis.net/workshops/postgis-intro/geography.html

1. Add a geom column to your table

   ALTER TABLE tableName ADD COLUMN geom geometry(Point, 4326);
   

2. Update the geom column using the below command

   UPDATE tableName SET geom = ST_SetSRID(ST_MakePoint(longitude::double 
   precision, latitude::double precision), 4326);
   

I have this solution to add geom column of WSGI and UTM srid values

Synopsis geometry ST_MakePoint(float x, float y); geometry ST_MakePoint(float x, float y, float z); geometry ST_MakePoint(float x, float y, float z, float m);

Note:- For geodetic coordinates, X is longitude and Y is latitude

lets add geom(geometry column)

SELECT AddGeometryColumn ('group_data','common_datatable','geom',4326,'Point',2);

Now we will update our geom column values from our lat, long column

UPDATE group_data.common_datatable
    SET  geom = ST_SetSRID(
                    ST_MakePoint(longitude, latitude)
                    ,4326);

lets create spatial index also for faster spatial query

-- DROP INDEX IF EXISTS group_data.sidx_common_datatable_geom;
CREATE INDEX IF NOT EXISTS sidx_common_datatable_geom
    ON group_data.common_datatable USING gist
    (geom);

To create geomtery column in UTM co-ordinate system also

-- Adding geom_utm column for utm srid value
SELECT AddGeometryColumn ('group_data','common_datatable','geom_utm',32643,'Point',2);

UPDATE group_data.common_datatable
    SET  geom_utm = ST_Transform(geom, 32643)
    FROM spatial_ref_sys
    WHERE ST_SRID(geom) = srid;

-- DROP INDEX IF EXISTS group_data.sidx_common_datatable_geom_utm;
CREATE INDEX IF NOT EXISTS sidx_common_datatable_geom_utm
    ON group_data.common_datatable USING gist
    (geom_utm);