PostGIS SQL error

[BrianEdmond]

Greetings!

I'm trying to add a PostGIS layer based on an attribute criterion. When I do this using the "Add PostGIS Layer" option on the menu, I can query for the attribute and it is added correctly. When I try to emulate this in Python, I get an error. Behold:

This works wonderfullly:

Code: Select all

      uriBase.setDataSource("public", "mo_county_base", "the_geom","")
      self.iface.addVectorLayer(uriBase.uri(), "County", "postgres")


This throws an error:

Code: Select all

 
    uriData.setDataSource("public", "mo_county_base", "the_geom", "COUNTYNAME = 'Pike' AND GeometryType(the_geom) IN ('POLYGON','MULTIPOLYGON')")
    self.iface.addVectorLayer(uriData.uri(), "Pike", "postgres")


Error:

Qgis was unable to determine the type and srid of column the_geom in "public"."mo_county_base". The database communication log was:

(There is nothing after the colon at the end.)

I copied the SQL parameter in the setDataSource from the MetaData of the layer after doing it manually. I dug into the code enough to figure out that it was expecting a parameter similar to the one I actually sent. But I wasn't able to find any working examples.

Does anyone have any ideas on this?

Thanks!

B

[GuZzO]

Qgis was unable to determine the type and srid of column the_geom in "public"."mo_county_base". The database communication log was:

This means that no feature are returned with this query....
so 2 possibilities:
1- query is good and no features match the request
2- query is false and as PostgresSQL request through Python never return error you can't see what's wrong with the query

I would say that the second option occured

uriData.setDataSource("public", "mo_county_base", "the_geom", "COUNTYNAME = 'Pike' AND GeometryType(the_geom) IN ('POLYGON','MULTIPOLYGON')")

In Python, this is wrongly interpreted! You should write:

uriData.setDataSource("public", "mo_county_base", "the_geom", "COUNTYNAME = \'Pike\' AND GeometryType(the_geom) IN (\'POLYGON\',\'MULTIPOLYGON\')")

Otherwise, the 2 following symbols " and ' and interpreted as end of string.

I may be wrong but COUNTYNAME shouldn't be surrounded by "" as every upcase paramaters in postgres??

[BrianEdmond]

Yes! At first, I suspected that I needed to escape some characters but I tried everything and couldn't get it. I was convinced that my problem was elsewhere. This is what I finally got to work:

Code: Select all

    uriData.setDataSource("public", "mo_county_base", "the_geom", "\"COUNTYNAME\" = \'Pike\' AND GeometryType(\"the_geom\") IN (\'POLYGON\',\'MULTIPOLYGON\')")
    self.iface.addVectorLayer(uriData.uri(), "Pike", "postgres")


Now, I'm on to the challenges of automatically formatting the symbology.

Thank you, thank you, thank you!

B

[GuZzO]

so it was an escape character issue and you had forgotten to encapsulate COUNTYNAME too.
I'm interested by the process description if you automatically format the symbology with success!!

as long as i know there's 2 ways for that:
1- load a .qml file you have created at first
2- load the default symbology from QGis database

[BrianEdmond]

>> 1- load a .qml file you have created at first

This is exactly what I ended up doing. It works just fine in my case and I won't be pursuing it further. (However, I did like the idea of everything being contained within the Python script so I never have to worry about missing qml files or whatever.)

Thanks for all of your help!

B