Making POINT from longitude/latitude coordinates in existing table using PostGIS?

Question

I would like to make a POINT using the longitude and latitude columns of an existing table.
Here's a link to the documentation of ST_MakePoint.

Here's my table:

CREATE TABLE sysinst.bio (  
    oid INTEGER PRIMARY KEY,  
    longitude FLOAT,  
    latitude FLOAT,  
        geom GEOMETRY(POINT, 26913)
);

Adding Geometry Column:

SELECT AddGeometryColumn ('sysinst', 'bio', 'geom', 26913, 'POINT', 2);

Here's my query:

UPDATE sysinst.bio SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 26913);

No returned error though the POINTS are wrongly plotted when viewed in QGIS.

For (-97.5959, 21.1922) , I recieve (-109.4896, 0.0002)

Any suggestions?

I'm open to doing this more efficiently if there's an easier way.

I'm not seeing the problem with the query. Perhaps you can try to diagnose the problem in parts - perhaps try to eliminate the QGIS part, and just use the command line for postgis? — BradHards
– BradHards, Commented Mar 16, 2013 at 7:44
Maybe I'm not understanding, but you seem to be entering Longitude Latitude coordinates (-97, 21) in degrees,into a CRS that is UTM based, and uses meters. If you're declaring this as SRID 26918, then the long/lat values must be in that CRS. If the Long/Lat values are in degrees then you will need to create the geometry as ST_SetSRID(MakePoint(...),4326) then transform the layer to 26913 — Micha
– Micha, Commented Mar 16, 2013 at 12:02
@Micha, your should add your comment as an answer. I expect the proper SRID will solve the OPs problem. It might also be helpful to include and example of how to add an ST_Transform. — katahdin
– katahdin, Commented Mar 16, 2013 at 12:15

Micha · Accepted Answer · 2013-03-17 09:24:23Z

17

Maybe I'm not understanding, but you seem to be entering Longitude Latitude coordinates (-97, 21) in degrees,into a CRS that is UTM based, and uses meters. If you're declaring this as SRID 26918, then the long/lat values must be in that CRS. If the Long/Lat values are in degrees then you will need to create the geometry as ST_SetSRID(MakePoint(...),4326) then transform the layer to 26913. So, putting it all together, if you need to have the layer in 26913, but your longitude/latitude columns are in degrees, with CRS 4326, then

UPDATE sysinst.bio SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326),26913);

answered Mar 17, 2013 at 9:24

Micha

15.9k24 silver badges29 bronze badges

Thanks for the help. I should have thought about that before...

Zach
– Zach

2013-03-20 19:06:58 +00:00
Commented Mar 20, 2013 at 19:06

Add a comment |

Stack Exchange Network

Making POINT from longitude/latitude coordinates in existing table using PostGIS?

1 Answer 1

Linked

Hot Network Questions

Making POINT from longitude/latitude coordinates in existing table using PostGIS?

1 Answer 1

Linked

Related

Hot Network Questions