0
sql="select long,lat from poidata.poi_ca from where city LIKE '%unnyvale'"
uri = QgsDataSourceURI()
uri.setConnection("localhost","5432","gis_data","postgres","vajja")
uri.setDataSource("",sql,"long","","lat") 
vlayer =QgsVectorLayer(uri.uri(),"LayerName","postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

Error: Unable to access the "select long,lat from poidata.poi_ca from where city LIKE '%unnyvale'" relation. The error message from the database was: ERROR: relation "select long,lat from poidata.poi_ca from where city LIKE '%unny" does not exist LINE 1: SELECT * FROM "select long,lat from poidata.poi_ca from wher... ^ . SQL: SELECT * FROM "select long,lat from poidata.poi_ca from where city LIKE '%unnyvale'"

Sources:

I tried both the above from different source but still I get an error

4
  • Please only ask one question per question as per the Tour, and try to use more words to describe precisely what leads to any error message seen. Here you appear to be asking for help to try and debug three error messages i.e. three questions.
    – PolyGeo
    Aug 19 '15 at 22:05
  • can you let me know how to connect and retrieve data from postgresql using python and add as layer in qgis Aug 19 '15 at 22:38
  • At the moment your question is On Hold because we need you to edit it to try and bring it back within the scope of the site as defined by our Tour at a high level, and its more detailed help center.
    – PolyGeo
    Aug 19 '15 at 22:56
  • i see the problem here, suspect it's an outdated tutorial pre-dating the API changes in 2.0 - although the qgis documentation (second source quoted) is correct.
    – Steven Kay
    Aug 20 '15 at 0:58
1

This line is the problem

uri.setDataSource("",sql,"long","","lat") 

The error message you're getting says that the table "select long,lat from ..." doesn't exist. The reason is you're passing the sql as the 2nd parameter; this should be the table name instead.

From the API documentation, the parameters should be in this order...

QgsDataSourceURI.setDataSource(Schema, Table, GeometryColumn, Sql, keyColumn)   

It may be that the first link you posted is out-of-date. There were API changes made in 2.0, this might be one of them. The second link (the official documentation) is up-to-date.

3
  • I tried that too I was getting an error Erroneous query: SELECT DISTINCT upper(geometrytype("long")),st_srid("long"),st_ndims("long") FROM "public"."poi_sv1" returned 7 [ERROR: function geometrytype(double precision) does not exist Aug 20 '15 at 17:59
  • it should probably be st_geometrytype(). If that doesn't work, check that the postgis extensions are loaded in your database
    – Steven Kay
    Aug 20 '15 at 18:13
  • I installed all the three extensions in my postges postgis,postgis_topology and postgis_tiger_geocoder. But still it shows an error Aug 20 '15 at 18:16

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.