[GIS] Creating geometry from lat/lon in table using PostGIS

Question

I have a table with over 800,000 records including a lat and long column. The data source did not create geometries, so I was looking around how to do this. I know there's ST_MakePoint(x,y,SRID) but all the documentation and other threads show how to create single points. How do I create the geometry type using these columns?

Answer 1

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