0

I have a database with more than 110 million records (points), in the geopackage format it is 11 gigabytes and in the Geoparquet format it is 2 gigabytes. Viewing takes more than 5 minutes.

I need access to work with this base in QGIS, but it is very heavy. I limited the display by scale (1:1 to 1:10,000) and I would like to know if there is a way to limit the number of elements displayed on the screen, that is, if I zoom in, zoom out or pan, the number of points maximum displayed will be a predetermined value.

Is this possible? Are there other ways to manipulate this data? I tried to create a spatial index, but nothing happened in both formats. An example of use is: quantifying the total number of points within polygons drawn on the screen ( aggregate(layer:='domicilios',aggregate:='count',expression:="NV_GEO_COO", filter:=intersects($geometry, geometry(@parent))))

Improve this question
4
  • 1
    Have you tried importing you data to a PostgreSQL+PostGIS database and creating a spatial index? Try using point cluster symbol on small scale.
    – Bera
    Commented May 2 at 16:56
  • At the moment I am working and looking for a solution for the Geopackage and Parquet formats, but in the future, the proposal is to use PostgreSQL+PostGIS database. I use cluster point symbology and it works very well, but it does not speed up data visualization performance.
    – Denilson
    Commented May 2 at 19:18
  • See if throwing a WFS server in the middle can help (it will do what you want, i.e. to limit the number of returned geometries, but will it be faster?)
    – JGH
    Commented May 3 at 12:00
  • I believe so, using the PostgreSQL+PostGIS database plus the WFS service would work, but the challenge with the number of points leads us to have to optimize the database and the publication in wfs, this will be the next steps.
    – Denilson
    Commented May 4 at 23:09

1 Answer 1

Reset to default
0

It's not exactly what you need, but it's a tip to dig into SQL more.

Using the geopackage format, you can set a SQL like filter:

select * from random_points_in_polygons WHERE fid >= 0 LIMIT 0, 1000

This query will only filter FIRST 1 000 featues starting from the top of the table.

before filtering after filtering

Improve this answer
2
  • 1
    Spatial filtering in the current map extent: gis.stackexchange.com/questions/320991/…
    – Comrade Che
    Commented May 2 at 18:18
  • I tested these commands. The select with limit command works to limit the number of records, but it does not necessarily coincide with the data that should be on the screen, that is, it cannot be applied. The link gis.stackexchange.com/questions/320991/… works very well, the problem is that as we have more than 110 millions of points, the use of a space operator is unfeasible due to the delay in executing it.
    – Denilson
    Commented May 4 at 23:05

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.