Adding PostGIS Layer (Query Before Load)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding PostGIS Layer (Query Before Load)

Peter Borissow
Hello,
     I have a partitioned table in a PostgreSQL/PostGIS database with ~30 billion points. I would like to render a subset of this data in QGIS and run some analytics.

Normally, with a smaller dataset, I would simply navigate to the table I want via the "Browser" and add it to the map via a double click. Once the data is loaded, I can filter the data, run analysis, etc. However, in my case, this is impossible. What I need is an option to filter the data before loading it into the map.

I looked at the Data Source Manager (Layer -> Add Layer -> Add PostGIS Layers...) but I don't see any filtering options there and the app hangs when I hit the "Connect" button.

Obviously, I can create views in the database to circumvent this issue but I don't want to create dozens of custom views.

Is there another way to create a layer from PostgreSQL/PostGIS using a filter-first strategy in QGIS?

Thanks in advance,
Peter





_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Adding PostGIS Layer (Query Before Load)

Jorge Gustavo Rocha-3

Hi Peter,

1) Normally, after clicking "Connect" you will get all layers. You can select one of them and use "Set filter" to define a filter before adding it to the map. Sorry for asking, but you are aware of the "Set filter" button, right?

2) In your case, it hangs before that. It hangs before listing all tables. When you click connect, QGIS will fetch some metadata to fill the list of available tables with the correspondent geometry type. One of such metadata queries is failing against your tables.

Can you enable logging of all Postgresql statements and identify the query that is hanging QGIS? It would help to identify the problem.

Regards,

Jorge Gustavo

Às 23:29 de 05/08/19, Peter Borissow escreveu:
Hello,
     I have a partitioned table in a PostgreSQL/PostGIS database with ~30 billion points. I would like to render a subset of this data in QGIS and run some analytics.

Normally, with a smaller dataset, I would simply navigate to the table I want via the "Browser" and add it to the map via a double click. Once the data is loaded, I can filter the data, run analysis, etc. However, in my case, this is impossible. What I need is an option to filter the data before loading it into the map.

I looked at the Data Source Manager (Layer -> Add Layer -> Add PostGIS Layers...) but I don't see any filtering options there and the app hangs when I hit the "Connect" button.

Obviously, I can create views in the database to circumvent this issue but I don't want to create dozens of custom views.

Is there another way to create a layer from PostgreSQL/PostGIS using a filter-first strategy in QGIS?

Thanks in advance,
Peter





_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
--
Logo   Geomaster, LDA
  VENHA DESCOBRIR O CAMINHO DO OPEN SOURCE CONNOSCO

 
Avenida Barros e Soares
N.º 423, 4715-214 Braga
VAT/NIF
510 906 109
Phone
  +351 253 680 323
Site       geomaster.pt
GPS       41.53322, -8.41929


 
Jorge Gustavo Rocha

CTO

Mobile
  +351 910 333 888
Email    [hidden email]


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Adding PostGIS Layer (Query Before Load)

Jürgen E. Fischer
In reply to this post by Peter Borissow
Hi Peter,

On Mon, 05. Aug 2019 at 22:29:19 +0000, Peter Borissow wrote:
> I looked at the Data Source Manager (Layer -> Add Layer -> Add PostGIS
> Layers...) but I don't see any filtering options there and the app hangs when
> I hit the "Connect" button.

Did you enable "Use estimated metadata" on that connection?


Jürgen

--
Jürgen E. Fischer           norBIT GmbH             Tel. +49-4931-918175-31
Dipl.-Inf. (FH)             Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer           D-26506 Norden            https://www.norbit.de
QGIS release manager (PSC)  Germany                    IRC: jef on FreeNode

norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Juergen Fischer, Nils Kutscher HR: Amtsgericht Aurich HRB 100827
Datenschutzerklaerung: https://www.norbit.de/83/

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

signature.asc (844 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Adding PostGIS Layer (Query Before Load)

Peter Borissow
OK, after enabling "Use estimated metadata" on the connection, the Data Source Manager comes up (doesn't hang). I am able to select a table and apply a filter. After a brief delay, QGIS starts to load data. However, is see an odd query consuming resources in the database:

SELECT st_extent("coordinate") FROM "public"."device_location"

The device_location table is the partitioned table with billions of rows. I wound expect the above query to include the filter (where clause) I defined in the Data Source Manager.

To summarize, I am able to create a filter before loading data thanks to the "Use estimated metadata" but I seem to have found a new issue.

Thanks,
Peter


On Tuesday, August 6, 2019, 3:34:06 AM EDT, Jürgen E. Fischer <[hidden email]> wrote:


Hi Peter,

On Mon, 05. Aug 2019 at 22:29:19 +0000, Peter Borissow wrote:
> I looked at the Data Source Manager (Layer -> Add Layer -> Add PostGIS
> Layers...) but I don't see any filtering options there and the app hangs when
> I hit the "Connect" button.


Did you enable "Use estimated metadata" on that connection?


Jürgen

--
Jürgen E. Fischer          norBIT GmbH            Tel. +49-4931-918175-31
Dipl.-Inf. (FH)            Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer          D-26506 Norden            https://www.norbit.de
QGIS release manager (PSC)  Germany                    IRC: jef on FreeNodenorBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Juergen Fischer, Nils Kutscher HR: Amtsgericht Aurich HRB 100827
Datenschutzerklaerung: https://www.norbit.de/83/

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Adding PostGIS Layer (Query Before Load)

Peter Borissow
Also, after successfully adding the layer, if I go to Layers and right click on the layer and select "Update SQL Layer..." the DB Manager dialog pops up but hangs. The offending query seems to be:

SELECT * FROM "device_location"

Again, I wound expect the above query to include the filter (where clause) I defined in the Data Source Manager.

Thanks,
Peter


On Tuesday, August 6, 2019, 7:43:44 AM EDT, Peter Borissow <[hidden email]> wrote:


OK, after enabling "Use estimated metadata" on the connection, the Data Source Manager comes up (doesn't hang). I am able to select a table and apply a filter. After a brief delay, QGIS starts to load data. However, is see an odd query consuming resources in the database:

SELECT st_extent("coordinate") FROM "public"."device_location"

The device_location table is the partitioned table with billions of rows. I wound expect the above query to include the filter (where clause) I defined in the Data Source Manager.

To summarize, I am able to create a filter before loading data thanks to the "Use estimated metadata" but I seem to have found a new issue.

Thanks,
Peter


On Tuesday, August 6, 2019, 3:34:06 AM EDT, Jürgen E. Fischer <[hidden email]> wrote:


Hi Peter,

On Mon, 05. Aug 2019 at 22:29:19 +0000, Peter Borissow wrote:
> I looked at the Data Source Manager (Layer -> Add Layer -> Add PostGIS
> Layers...) but I don't see any filtering options there and the app hangs when
> I hit the "Connect" button.


Did you enable "Use estimated metadata" on that connection?


Jürgen

--
Jürgen E. Fischer          norBIT GmbH            Tel. +49-4931-918175-31
Dipl.-Inf. (FH)            Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer          D-26506 Norden            https://www.norbit.de
QGIS release manager (PSC)  Germany                    IRC: jef on FreeNodenorBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Juergen Fischer, Nils Kutscher HR: Amtsgericht Aurich HRB 100827
Datenschutzerklaerung: https://www.norbit.de/83/

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: Adding PostGIS Layer (Query Before Load)

Jürgen E. Fischer
In reply to this post by Peter Borissow
Hi Peter,

On Tue, 06. Aug 2019 at 11:43:44 +0000, Peter Borissow wrote:
>  OK, after enabling  "Use estimated metadata" on the connection, the Data Source Manager comes up (doesn't hang). I am able to select a table and apply a filter. After a brief delay, QGIS starts to load data. However, is see an odd query consuming resources in the database:
> SELECT st_extent("coordinate") FROM "public"."device_location"

That should be:

SELECT st_estimatedextent("public", "device_location", "coordinate")

Ignoring the where clause is one of the tradeoffs that is used with "estimated
metadata" - also the feature count can be inaccurate (see tip on
https://docs.qgis.org/testing/en/docs/user_manual/managing_data_source/opening_data.html).

Unless that column doesn't have pg_stats (ie. SELECT count(*) FROM pg_stats
WHERE schemaname='public' AND tablename='device_location' AND
attname='coordinate' is not 1) in which case st_extent is used, but with the
where clause.


Jürgen

--
Jürgen E. Fischer         norBIT GmbH               Tel. +49-4931-918175-31
Dipl.-Inf. (FH)           Rheinstraße 13            Fax. +49-4931-918175-50
Software Engineer         D-26506 Norden              https://www.norbit.de

--
norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Juergen Fischer, Nils Kutscher HR: Amtsgericht Aurich HRB 100827
Datenschutzerklaerung: https://www.norbit.de/83/

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user