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 have a table in PostgreSQL(postgis). This table has latitude and longitude values in it. Now I want to plot these point in qgis. The only thing I could get done was connecting to the database from QGIS.

Could anyone please let me know on how to go about it.Thank You.

Here is the screenshot belowenter image description here

Below is the connection error I get while connection to the database from QGIS enter image description here

share|improve this question
    
I am not sure what you are asking, is the problem that you cannot view your points in qgis? are your lats and lons separate fields in your table, or have you inserted them as a geometry? –  atlefren Apr 20 '12 at 12:15
add comment

4 Answers

up vote 6 down vote accepted

Your problem is that you do not have a geometry column. You could either find out how to get QGIS to display lon/lat fields, ad GIS-Jonathan mentioned was possible, or you could create a geometry-column (assuming that all your lon/lat fields are non-empty:

First, add a geometry column:

SELECT AddGeometryColumn ('public','growth','the_geom',4326,'POINT',2);

Then insert geometries created from the lon/lat fields:

UPDATE growth SET the_geom = GeomFromEWKT('SRID=4326;POINT(' || growth.longitude || ' ' || growth.latitude || ')');

(taken from here)

share|improve this answer
    
Thanks. But due to lack of db knowledge I get an error now. ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) does not exist LINE 1: SELECT AddGeometryColumn ('public','growth','the_geom',4326,... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ********** Error ********** –  Senthil Apr 20 '12 at 13:30
    
It seems like you haven't installed PostGIS or "spatially enabled" your database, the lack of any functions and the geometry_columns table are clues. Could you try running: SELECT postgis_lib_version() –  atlefren Apr 20 '12 at 13:38
    
1.5.3 is version –  Senthil Apr 20 '12 at 13:56
    
The first syntax works fine now and I get a new error for the second one :) ERROR: parse error - invalid geometry HINT: "SRID =4326;POINT(" <-- parse error at position 17 within geometry ********** Error ********** ERROR: parse error - invalid geometry SQL state: XX000 –  Senthil Apr 20 '12 at 13:58
    
PHEW.... Thanks all is well.... –  Senthil Apr 20 '12 at 14:36
show 2 more comments

This tutorial should answer your question: Working with your PostGIS Layers using Quantum GIS (QGIS)
- from Viewing PostGIS Layers using QGIS about half way down.

share|improve this answer
    
Per @atlefren comment - if you haven't loaded the data correctly (it will need to be a geometry type to do this, though there are ways to do it if its just lat/lon fields) the first part of that tutorial does cover loading the data. –  GIS-Jonathan Apr 20 '12 at 12:19
    
Yes this is what I was looking for. But the table which was created just has a few fields with no lat longs. Now when I check the option for list geometryless tables it doesnt display this table . –  Senthil Apr 20 '12 at 12:24
    
Ok, I have posted the screenshot –  Senthil Apr 20 '12 at 12:58
    
I can't tell on there, but are your latitude and longitude stored as strings or numbers? I imagine QGIS won't like them if they're not a suitable number type which may be your problem. –  GIS-Jonathan Apr 20 '12 at 13:01
add comment

in qgis press Add PostGIS Layer button then connect your postgis connection(if you dont have create new database connection), select your table and the last thing press Add button.

i hope it helps you

share|improve this answer
add comment

If your table only has lat and long data, you will need to create a geometry column out of them. I recommend using the qgis plugin "Fast SQL Layer" and then (assuming your table structure has the columns id as integer, lat as float, lng as float and the data is in WGS84) get a new layer using

SELECT id as gid, ST_SetSRID(ST_MakePoint(lat, lng), 4326) as the_geom FROM your_table;
share|improve this answer
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.