8

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?

  • 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 – DPSSpatial 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
18

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);
11

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_SRID(ST_MakePoint(longitude, latitude), 4326);

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

  • 1
    I think ST_SRID should be ST_SetSRID. I got an error when using ST_SRID – Vipin Gupta May 17 at 11:48

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.