Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Ricardo, Thanks for explaining these things. The default of Spatialite uses Geometry and PK_UID as you can see test-2.3.sqlite. All spatialite databases immigrated using the utilities of spatialite have Geometry and PK_UID as default. You can also see it in the Spatialite Tutorials. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 As suggestion, if you don't mind, probably you can change the default to Geometry and PK_UID to avoid confusion especially for the new users. However, you also put a checkbox option if you like. I think it would be nice to some documentation in plugin. e.g. PRAGMA TABLE_INFO(table_name) to view table before you start using the plugin. The probably a link to this Spatialite Tutorials (i.e. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 ). Thanks. Noli On 12/17/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi Noli, Guiseppe, all I downloaded the spatialite sample database and ran your query. I could run it fine without the errors you mention. The problem here seems to be that I still need to write some documentation on how to use the plugin so that you (the user) can know how to operate it correctly ;) I will improve the Help page of the plugin in order to clarify these issues. As for your specific problem: the SQL query you are using is: Select * from Regions where NAME = 'VENETO' - If you only intend to visualize the results in the 'result' tab of the plugin, without loading to map canvas, it works fine just like this. - BUT, If you want to load the result of the query to the map canvas, you must ensure that the 'Geometry column' and 'Identifier column' text boxes are showing the correct values for your specific tables. Using a spatialite db, I've set the default values for these text boxes as 'geometry' and 'pkuid', but you need to confirm if your data are using these names as well. In this case, the correct values should be 'Geometry' and 'PK_UID'. You can check the names of the columns in an sqlite table by using the spatialite manager, or by running the following query: PRAGMA TABLE_INFO(table_name) If you use the wrong geometry and identifier values you may (or may not) still be able to visualize your data over the map canvas but the attribute values will come as ERROR. As for the deletion of tables from the spatialite db, there isn't a nice way of doing this. To the best of my knowledge you still have to manually create the SQL DROP TABLE... statement and also remove the triggers and geometry info from spatialite by running the SQL SELECT DiscardGeometryColumn... I think this feature will come around sooner or later. It belongs to the (spatialite)manager plugin, and, even if I miss it as well, I don't think it should be implemented in the custom db query plugin. Cheers ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
I agree with everything :) It will eventually get done. Cheers On Thu, Dec 16, 2010 at 8:59 PM, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, Thanks for explaining these things. The default of Spatialite uses Geometry and PK_UID as you can see test-2.3.sqlite. All spatialite databases immigrated using the utilities of spatialite have Geometry and PK_UID as default. You can also see it in the Spatialite Tutorials. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 As suggestion, if you don't mind, probably you can change the default to Geometry and PK_UID to avoid confusion especially for the new users. However, you also put a checkbox option if you like. I think it would be nice to some documentation in plugin. e.g. PRAGMA TABLE_INFO(table_name) to view table before you start using the plugin. The probably a link to this Spatialite Tutorials (i.e. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 ). Thanks. Noli On 12/17/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi Noli, Guiseppe, all I downloaded the spatialite sample database and ran your query. I could run it fine without the errors you mention. The problem here seems to be that I still need to write some documentation on how to use the plugin so that you (the user) can know how to operate it correctly ;) I will improve the Help page of the plugin in order to clarify these issues. As for your specific problem: the SQL query you are using is: Select * from Regions where NAME = 'VENETO' - If you only intend to visualize the results in the 'result' tab of the plugin, without loading to map canvas, it works fine just like this. - BUT, If you want to load the result of the query to the map canvas, you must ensure that the 'Geometry column' and 'Identifier column' text boxes are showing the correct values for your specific tables. Using a spatialite db, I've set the default values for these text boxes as 'geometry' and 'pkuid', but you need to confirm if your data are using these names as well. In this case, the correct values should be 'Geometry' and 'PK_UID'. You can check the names of the columns in an sqlite table by using the spatialite manager, or by running the following query: PRAGMA TABLE_INFO(table_name) If you use the wrong geometry and identifier values you may (or may not) still be able to visualize your data over the map canvas but the attribute values will come as ERROR. As for the deletion of tables from the spatialite db, there isn't a nice way of doing this. To the best of my knowledge you still have to manually create the SQL DROP TABLE... statement and also remove the triggers and geometry info from spatialite by running the SQL SELECT DiscardGeometryColumn... I think this feature will come around sooner or later. It belongs to the (spatialite)manager plugin, and, even if I miss it as well, I don't think it should be implemented in the custom db query plugin. Cheers -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Ricardo. I still have problem with the save new layer to database. The SQL query seems to be invalid. no such function: SRID The error is still: /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 299, in add_spatialite_geometry_column srid = rowList[0][0] IndexError: list index out of range Noli On 12/17/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: I agree with everything :) It will eventually get done. Cheers On Thu, Dec 16, 2010 at 8:59 PM, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, Thanks for explaining these things. The default of Spatialite uses Geometry and PK_UID as you can see test-2.3.sqlite. All spatialite databases immigrated using the utilities of spatialite have Geometry and PK_UID as default. You can also see it in the Spatialite Tutorials. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 As suggestion, if you don't mind, probably you can change the default to Geometry and PK_UID to avoid confusion especially for the new users. However, you also put a checkbox option if you like. I think it would be nice to some documentation in plugin. e.g. PRAGMA TABLE_INFO(table_name) to view table before you start using the plugin. The probably a link to this Spatialite Tutorials (i.e. http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html#t1 ). Thanks. Noli On 12/17/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi Noli, Guiseppe, all I downloaded the spatialite sample database and ran your query. I could run it fine without the errors you mention. The problem here seems to be that I still need to write some documentation on how to use the plugin so that you (the user) can know how to operate it correctly ;) I will improve the Help page of the plugin in order to clarify these issues. As for your specific problem: the SQL query you are using is: Select * from Regions where NAME = 'VENETO' - If you only intend to visualize the results in the 'result' tab of the plugin, without loading to map canvas, it works fine just like this. - BUT, If you want to load the result of the query to the map canvas, you must ensure that the 'Geometry column' and 'Identifier column' text boxes are showing the correct values for your specific tables. Using a spatialite db, I've set the default values for these text boxes as 'geometry' and 'pkuid', but you need to confirm if your data are using these names as well. In this case, the correct values should be 'Geometry' and 'PK_UID'. You can check the names of the columns in an sqlite table by using the spatialite manager, or by running the following query: PRAGMA TABLE_INFO(table_name) If you use the wrong geometry and identifier values you may (or may not) still be able to visualize your data over the map canvas but the attribute values will come as ERROR. As for the deletion of tables from the spatialite db, there isn't a nice way of doing this. To the best of my knowledge you still have to manually create the SQL DROP TABLE... statement and also remove the triggers and geometry info from spatialite by running the SQL SELECT DiscardGeometryColumn... I think this feature will come around sooner or later. It belongs to the (spatialite)manager plugin, and, even if I miss it as well, I don't think it should be implemented in the custom db query plugin. Cheers -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi all To celebrate the fixing of this bug by brushtyler (Guiseppe maybe?), I have updated 'custom db query' to version 0.4. The plugin's window is now modeless, and it stays on top of the QGIS main window. So you can interact with the rest of QGIS while having the plugin open. This way you can, for example remove your query layers from the map. On Tue, Dec 14, 2010 at 7:03 PM, Luiz Motta motta.l...@gmail.com wrote: Hi Guiseppe, Sorry for the mistake on the author of the provider. I made both test, for both plugins, 'Custom database query' and 'Rt Sql Layer, and with the same result. When the table is inside public's schema don't have message of error, otherwise, i have the message of error, but the layer is created perfect. I saved the project of QGIS (with the created layer) and open again this project the message of error was showed. In this point isolat the factor of plugin, then, have some problem with provider. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz and Ricardo, On Tue, Dec 14, 2010 at 1:04 PM, Luiz Motta motta.l...@gmail.com wrote: The author of provider told me about this problem, see ticket (https://trac.osgeo.org/qgis/ticket/3329). to Luiz: as Ricardo wrote, he is the author of the plugin (CustomDatabaseQuery), not the author of the provider at all. BTW, I'm the author of both the patches for executing a custom query with postgres and spatialite provider, so I'm one of the guys you're searching for :) to Ricardo: the ticket told about an Error for postgres's provider, not custom database query plugin issue. Furthermore, the QGis trac is not for plugins' issues, except for those that are in trunk. For this reason we're working to create a plugins trac. If you're sure the issue is only related to your plugin source code and not to the qgis functions called in your code, you should close the ticket as invalid. SELECT l.id AS gid, l.the_geom AS the_geom FROM gis.tl_20101117_101056_limite l Doesn't anyone confirm that it's a provider's issue, not just a plugin issue?? Please, could you try to execute the same query using the RT Sql Layer plugin, in order to confirm the provider issue? Thanks a lot. Cheers. Please inform about your enviroment. It can be util for understand what is happened. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz, On Tue, Dec 14, 2010 at 2:43 AM, Luiz Motta motta.l...@gmail.com wrote: I am testing the 'Custom database query' plugins, where, i use layers outside public's schema. The plugin create the layer, but, before, i receive the message error about cursor od database. If the layers are inside public's schema not appear message. The one ticket is open, number 3329. I'm unable to reproduce it, both 'Custom database query' and 'RT Sql Layer' plugins seems to work with tables not in the public schema. Maybe does your layer contain invalid geometries? Cheers. -- Giuseppe Sucameli -- Giuseppe Sucameli -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Ricardo, I got the error from the new version 0.4 (below). Before I don't have this error using the same database and query in 0.3 The SQL query seems to be invalid. no such function: SRID ~~~ An error has occured while executing Python code: Traceback (most recent call last): File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 175, in parse_SQL_text self.execute_query(selectQuery) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 189, in execute_query self.add_geometry_column(connection, selectQuery) # add srid and geomType File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 262, in add_geometry_column self.add_spatialite_geometry_column(connection, initialQuery) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 299, in add_spatialite_geometry_column srid = rowList[0][0] IndexError: list index out of range Another thing, when there is an error, the table is still created. Is there simple way to delete the table in the database, not using SQL statement / query? Thanks. Noli On 12/16/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi all To celebrate the fixing of this bug by brushtyler (Guiseppe maybe?), I have updated 'custom db query' to version 0.4. The plugin's window is now modeless, and it stays on top of the QGIS main window. So you can interact with the rest of QGIS while having the plugin open. This way you can, for example remove your query layers from the map. On Tue, Dec 14, 2010 at 7:03 PM, Luiz Motta motta.l...@gmail.com wrote: Hi Guiseppe, Sorry for the mistake on the author of the provider. I made both test, for both plugins, 'Custom database query' and 'Rt Sql Layer, and with the same result. When the table is inside public's schema don't have message of error, otherwise, i have the message of error, but the layer is created perfect. I saved the project of QGIS (with the created layer) and open again this project the message of error was showed. In this point isolat the factor of plugin, then, have some problem with provider. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz and Ricardo, On Tue, Dec 14, 2010 at 1:04 PM, Luiz Motta motta.l...@gmail.com wrote: The author of provider told me about this problem, see ticket (https://trac.osgeo.org/qgis/ticket/3329). to Luiz: as Ricardo wrote, he is the author of the plugin (CustomDatabaseQuery), not the author of the provider at all. BTW, I'm the author of both the patches for executing a custom query with postgres and spatialite provider, so I'm one of the guys you're searching for :) to Ricardo: the ticket told about an Error for postgres's provider, not custom database query plugin issue. Furthermore, the QGis trac is not for plugins' issues, except for those that are in trunk. For this reason we're working to create a plugins trac. If you're sure the issue is only related to your plugin source code and not to the qgis functions called in your code, you should close the ticket as invalid. SELECT l.id AS gid, l.the_geom AS the_geom FROM gis.tl_20101117_101056_limite l Doesn't anyone confirm that it's a provider's issue, not just a plugin issue?? Please, could you try to execute the same query using the RT Sql Layer plugin, in order to confirm the provider issue? Thanks a lot. Cheers. Please inform about your enviroment. It can be util for understand what is happened. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz, On Tue, Dec 14, 2010 at 2:43 AM, Luiz Motta motta.l...@gmail.com wrote: I am testing the 'Custom database query' plugins, where, i use layers outside public's schema. The plugin create the layer, but, before, i receive the message error about cursor od database. If the layers are inside public's schema not appear message. The one ticket is open, number 3329. I'm unable to reproduce it, both 'Custom database query' and 'RT Sql Layer' plugins seems to work with tables not in the public schema. Maybe does your layer contain invalid geometries? Cheers. -- Giuseppe Sucameli -- Giuseppe Sucameli -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Ricardo, Spatialite database bug. The bug is in checkbox (on./check) save new layer to database. When I unchecked (off), it works but the resulting map in TOC is sql layer. The postgis bug, I don't know the reason since it does not work as well in the other postgis plugin (i.e PGManager). Noli On 12/16/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi Noli I can't seem to reproduce any of your errors with my sample data. Can you give me some more information? What queries are you trying? Is it possible for you to send me some testing data? On Thu, Dec 16, 2010 at 1:47 AM, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, . I got another error in 0.4, this time in PostGI. This is simple query. Select * from stands where veg_type = A ~ An error has occured while executing Python code: Traceback (most recent call last): File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 175, in parse_SQL_text self.execute_query(selectQuery) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 187, in execute_query self.run_normal_query(createTableQuery, connection) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 238, in run_normal_query cursor.execute(query) ProgrammingError: column A does not exist LINE 1: ...ne.Veg_TYPE_A AS (Select * from stands where veg_type = A) ^ Python version: 2.6.5 (r265:79063, Apr 16 2010, 13:28:26) [GCC 4.4.3] QGIS version: 1.7.0-Trunk Trunk, 14914M The previous error was in Spatialite. Thanks. Noli -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Ricardo, To replicate my error in Spatialite database. Please download the test-2.3.zip from http://www.gaia-gis.it/spatialite-2.3.0/resources.html Do the following query on the Regions table: Select * from Regions where NAME = VENETO You will see the errors. Have a look at as well the sql layer attribute table. Using Ubuntu 10.04, GGIS 1.7 trunk r14914M. Thanks. Noli Thanks. Noli On 12/16/10, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, In postgis error. The plugin has the same problem when you checkbox is on for save new layer to database. However, if I unchecked it this is the error. Unable to execute the query. The error message from the database was: ERROR: column A does not exist LINE 1: ...ect * from (Select * from stands where veg_type = A) as s... ^ . SQL: select * from (Select * from stands where veg_type = A) as subQuery_0 limit 1 Noli On 12/16/10, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, Spatialite database bug. The bug is in checkbox (on./check) save new layer to database. When I unchecked (off), it works but the resulting map in TOC is sql layer. The postgis bug, I don't know the reason since it does not work as well in the other postgis plugin (i.e PGManager). Noli On 12/16/10, Ricardo Filipe Soares Garcia da ricardo.garcia.si...@gmail.com wrote: Hi Noli I can't seem to reproduce any of your errors with my sample data. Can you give me some more information? What queries are you trying? Is it possible for you to send me some testing data? On Thu, Dec 16, 2010 at 1:47 AM, Noli Sicad nsi...@gmail.com wrote: Hi Ricardo, . I got another error in 0.4, this time in PostGI. This is simple query. Select * from stands where veg_type = A ~ An error has occured while executing Python code: Traceback (most recent call last): File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 175, in parse_SQL_text self.execute_query(selectQuery) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 187, in execute_query self.run_normal_query(createTableQuery, connection) File /home/noli/.qgis/python/plugins/customdbquery/CustomDBQueryDialog.py, line 238, in run_normal_query cursor.execute(query) ProgrammingError: column A does not exist LINE 1: ...ne.Veg_TYPE_A AS (Select * from stands where veg_type = A) ^ Python version: 2.6.5 (r265:79063, Apr 16 2010, 13:28:26) [GCC 4.4.3] QGIS version: 1.7.0-Trunk Trunk, 14914M The previous error was in Spatialite. Thanks. Noli -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi Guiseppe, Sorry for the mistake on the author of the provider. I made both test, for both plugins, 'Custom database query' and 'Rt Sql Layer, and with the same result. When the table is inside public's schema don't have message of error, otherwise, i have the message of error, but the layer is created perfect. I saved the project of QGIS (with the created layer) and open again this project the message of error was showed. In this point isolat the factor of plugin, then, have some problem with provider. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz and Ricardo, On Tue, Dec 14, 2010 at 1:04 PM, Luiz Motta motta.l...@gmail.com wrote: The author of provider told me about this problem, see ticket ( https://trac.osgeo.org/qgis/ticket/3329). to Luiz: as Ricardo wrote, he is the author of the plugin (CustomDatabaseQuery), not the author of the provider at all. BTW, I'm the author of both the patches for executing a custom query with postgres and spatialite provider, so I'm one of the guys you're searching for :) to Ricardo: the ticket told about an Error for postgres's provider, not custom database query plugin issue. Furthermore, the QGis trac is not for plugins' issues, except for those that are in trunk. For this reason we're working to create a plugins trac. If you're sure the issue is only related to your plugin source code and not to the qgis functions called in your code, you should close the ticket as invalid. SELECT l.id AS gid, l.the_geom AS the_geom FROM gis.tl_20101117_101056_limite l Doesn't anyone confirm that it's a provider's issue, not just a plugin issue?? Please, could you try to execute the same query using the RT Sql Layer plugin, in order to confirm the provider issue? Thanks a lot. Cheers. Please inform about your enviroment. It can be util for understand what is happened. Regards, Luiz Motta 2010/12/14 Giuseppe Sucameli sucam...@faunalia.it Hi Luiz, On Tue, Dec 14, 2010 at 2:43 AM, Luiz Motta motta.l...@gmail.comwrote: I am testing the 'Custom database query' plugins, where, i use layers outside public's schema. The plugin create the layer, but, before, i receive the message error about cursor od database. If the layers are inside public's schema not appear message. The one ticket is open, number 3329. I'm unable to reproduce it, both 'Custom database query' and 'RT Sql Layer' plugins seems to work with tables not in the public schema. Maybe does your layer contain invalid geometries? Cheers. -- Giuseppe Sucameli -- Giuseppe Sucameli ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
[Qgis-developer] Error for postgres's provider when table(statement SELECT ) is outside public's schema
Hi, I am testing the 'Custom database query' plugins, where, i use layers outside public's schema. The plugin create the layer, but, before, i receive the message error about cursor od database. If the layers are inside public's schema not appear message. The one ticket is open, number 3329. Regards, Luiz Motta ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
