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?
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:
If you haven't already add a geometry column (assuming EPSG:4326 here):
SELECT AddGeometryColumn ('your_table','geom',4326,'POINT',2);
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
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).