48

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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
dknuth
  • 591
  • 1
  • 4
  • 3
  • 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: http://blog.cleverelephant.ca/2015/03/making-lines-from-points.html – DPSSpatial_BoycottingGISSE May 04 '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 04 '15 at 21:00

4 Answers4

75

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:

anneb
  • 1,371
  • 11
  • 11
  • 1
    You can also use the newer ST_POINT(lon, lat, srid) function. The srid parameter is optional, and you can pass this in the ST_SetSRID instead, like above. – Theo F Jun 23 '23 at 17:54
23

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

kometen
  • 103
  • 4
toms
  • 6,978
  • 1
  • 25
  • 30
3

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

dgj32784
  • 229
  • 1
  • 4
  • 1
    Many spatial functions accept only geometry, not geography, some accept both. Therefore I prefer to use geometry over geography. – anneb Apr 13 '22 at 21:32
0

  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);
Kadir Şahbaz
  • 76,800
  • 56
  • 247
  • 389
Sagar Chougule
  • 1
  • 1