32

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?

2
  • 1
    do you mean create lines from these points? Because points are geometry just as lines and polygons are... So if you're looking at creating lines from these points, check this recent blog post from Paul Ramsey: blog.cleverelephant.ca/2015/03/making-lines-from-points.html May 4 '15 at 16:29
  • Nope. I meant like converting them to geometry types to be used in spatial queries. Looks like my question has been answered though so thank you!
    – dknuth
    May 4 '15 at 21:00
54

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:

19

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

Reference to ST_SetSrid: https://postgis.net/docs/ST_SetSRID.html

1
  • 4
    I think ST_SRID should be ST_SetSRID. I got an error when using ST_SRID May 17 '19 at 11:48
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.