1

I have downloaded all levels of GADM boundaries (https://gadm.org/). I divided each level into a separate table within PostgreSQL 10 database. Queries on these tables are quite slow considering gadm_level0 has 256 and gadm_level1 has 3610 rows. It takes more than 15 sec for fetching first 200 rows (for gadm level1) and for gadm level 0 it takes much more time.

I think the problem is with number of vertices in geometries since queries run just fine when I don't include geom into query. I even tried to simplify gadm_level0 geometries with ST_Simplify, but didn't get huge difference.

Below are execution plans and screenshots of table structure.

I wanted to publish each level (table) as a separate layer on GeoServer but in this setup it is not working fine. Could you give me some advice on how to improve the performance of PostgreSQL in this case? Then, I would use caching on GeoServer to further improve the performance of the published layers.

GADM level 0 table

explain analyze select gid_0, name_0, geom from gadm_level0

Explain analyze of gadm_level0 table

GADM level 1 table

explain analyze select gid_0, name_0, gid_1, name_1, geom from gadm_level1

Explain analyze of gadm_level1 table

7
  • Is the title "unable to perform SQL select" correct or is the query just slow?
    – user30184
    Jan 22, 2022 at 10:00
  • Very slow performance. Sorry for misunderstanding! Jan 22, 2022 at 12:01
  • 1
    please don't use screenshots instead of text.
    – Ian Turton
    Jan 22, 2022 at 12:02
  • 2
    Would your use case still work if you subdivided them? blog.cleverelephant.ca/2019/11/subdivide.html Jan 22, 2022 at 13:09
  • 1
    Thanks @bugmenot123 for help. I used your advice and now it works quite fast. For my application this is a good solution. If you like, put your comment as the answer and I'll accept it. Jan 23, 2022 at 8:05

2 Answers 2

4

You're doing a full table scan query on features with tens of millions of vertices. Yeah, that will take a while. Adding additional geometries to the table likely made it worse (more pages in the table).

The key to good draw performance with massive polygons is to not draw them.

Instead you can convert the boundaries to lines, intersect the lines with a 9x9, 15x15, or 30x30 degree fishnet, then union by fishnet ID (to make MULTILINESTRING features). And just draw the borders.

If you need polygon shading at small scale, intersect the polygons with the same fishnet, and draw them without borders (or very faint borders and draw the global fishnet as a water grid first, so the borders look like a continuation of the graticule), the draw the the GDAM1 lines (less faint) and GDAM0 lines (wider/darker).

Using scale dependency you can even split the land/ocean, admin0, and admin1 rendering process so that borders that can't be distinguished aren't drawn until the zoom level is such that they can be useful.

You can even repeat the process with generalized linework, so that above a certain map scale, only massively generalized linework (in both the polygon tiles and borders) is drawn. I've done this at a trade fair for a customer, and some booth visitors only wanted to know how the basemap was so fast, not about the data product that was being demonstrated (and I never bothered to build a cache, since there were hundreds of data layers sandwiched between the boundary grid and the admin borders).

2
  • Thanks for the reply. However, I am using also these data for SQL queries such as ST_Intersects to compare drawn geometries with countries' geometry (not just visualization). That's why it is simpler for me to stay with polygon geometries. Jan 23, 2022 at 8:08
  • You need both. And, depending on the size of drawn geometries, another copy of countries, cut into something smaller, like MGRS grid zones, to make the queries run efficiently. I've been working with spatial databases for 27 years, so I've seen the issue a couple of times.
    – Vince
    Jan 23, 2022 at 12:03
3

Depending on your use case it might be worth trying to subdivide the geometries. Paul Ramsey has a nice article on that.

Subdividing will multiply your feature count and lead to many "duplicates" regarding their attribute values but each of the new features will have its geometry only cover a smaller region. Even when using the whole table anyways, this can lead to better performance as the data can be handled in smaller chunks by both server and client.

This has many benefits on the database side as well as for a client rendering smaller "windows" of the whole data set as the amount of geometric data per feature is drastically reduced and spatial indexing can do its magic properly.

Drawbacks are the need for special attention when rendering (otherwise you get lots of borders appearing within the "original feature geometries") or when running analyses that reference the now duplicated attribute values.

An example query borrowed from Paul:

CREATE TABLE ne_10m_admin_0_countries_subdivided AS
  SELECT
    ST_SubDivide(the_geom) AS the_geom,
    admin 
  FROM
    ne_10m_admin_0_countries
;

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.