4

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

Improve this question
1
  • 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
    Commented Apr 20, 2012 at 12:15

4 Answers 4

Reset to default
9

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)

Improve this answer
6
  • 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 **********
    – SNT
    Commented Apr 20, 2012 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
    Commented Apr 20, 2012 at 13:38
  • 1.5.3 is version
    – SNT
    Commented Apr 20, 2012 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
    – SNT
    Commented Apr 20, 2012 at 13:58
  • Glad it worked in the end!
    – atlefren
    Commented Apr 23, 2012 at 11:34
2

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.

Improve this answer
4
  • 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
    Commented Apr 20, 2012 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 .
    – SNT
    Commented Apr 20, 2012 at 12:24
  • Ok, I have posted the screenshot
    – SNT
    Commented Apr 20, 2012 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
    Commented Apr 20, 2012 at 13:01
1

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

Improve this answer
0

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(lng, lat), 4326) as the_geom FROM your_table;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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