10

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 Mar 16 '13 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 Mar 16 '13 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 Mar 16 '13 at 12:15
16

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);
  • Thanks for the help. I should have thought about that before... – Zach Mar 20 '13 at 19:06

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.