Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 welcome to doing this more efficiently if there's an easier way.

share|improve this question
    
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
add comment

1 Answer 1

up vote 1 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.