[GIS] Making POINT from longitude/latitude coordinates in existing table using PostGIS

coordinatesgeometrypointpostgistable

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.

Best Answer

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);
Related Question