1

I've been uploading a huge amount of contour information to a PostGIS / PostGres table. Uploaded using QGIS' db manager to a separate table a chunk at a time, and then using a quick bit of SQL to append those chunks onto the main contour table.

pgAdmin is estimating it to have 492,408 rows containing five columns, which are:

  1. Altitude (double) e.g. 129.5
  2. Class (char) e.g. Basic
  3. geometry (geometry, a multiline string)
  4. OID (integer)
  5. pid (integer and primary key)

Now, if I try and add it into QGIS or Manifold (using Add PostGIS layers) it will basically take ages if it can add at all.

I've added a spatial index to it, as well as an index on the pid. Indexes that I've added are:

CREATE INDEX "Contours_50cm_Idx"
ON "Contours_50cm"
USING gist
(geometry);

and

CREATE UNIQUE INDEX "PID_INDEX"
ON "Contours_50cm"
USING btree
(pid);

Does anyone have any idea of anything else that could help optimise this?

Google has revealed people sometimes use partitioning on tables. Users won't have to see all of this data at a time - it covers quite a large area geographically.

7
  • 4
    Spatial index does not give any speedup if you do what you do and add the whole layer into QGIS because nothing gets filtered out. All the 492408 contour lines with all the attributes are read from the table anyway. Spatial index makes things faster if the query is spatially selective which means with QGIS that you have zoomed in into a small bounding box. Is the layer still slow if you zoom in? If you can't initially open it, zoom in first before adding the layer. Disabling rendering by unchecking the Render box may help as well.
    – user30184
    Aug 31, 2018 at 6:45
  • 7
    Make a simplified table of contours for small scale map rendering (ST_Simplify), and /or keep only every fifth elevation (main contours). Add both layers to QGIS and use scale dependent display to view only one of them.
    – Zoltan
    Aug 31, 2018 at 7:52
  • 1
    Remove "PID_INDEX" and make it a PRIMARY KEY instead: ALTER TABLE "Contours_50cm" ADD PRIMARY KEY (pid);. If you already have it as primary key, you can still remove the index, as primary keys are always indexed.
    – pLumo
    Aug 31, 2018 at 9:02
  • @RoVo - yes, pid is already a primary key. I'll keep the index on it for the moment as that shouldn't affect performance.
    – user25730
    Sep 2, 2018 at 22:32
  • @Zoltan - good idea, though not what I'm after. I need it adding to a Manifold project (though QGIS is the "Swiss Army Knife" of the GIS world). However, just remembered that I can try to use the AOI Update method of Manifold which should help.
    – user25730
    Sep 2, 2018 at 22:35

2 Answers 2

1

If you want to interact with them all at once, then you can't really optimise it.

Something to consider for visualisation is building simplified versions, so that when you're zoomed out and looking at a large area, you are viewing less nodes/data. This works great in something like GeoServer. And for background data in a GIS application you could have both and use zoom layering to only show one at a time.

I like simplifying while preserving topology, but you can just simplify.

Another point is that 500,000 features is really not that big a deal. But consider using tools like OGR to load it rather than a GUI. I have found this to be fast:

ogr2ogr -lco GEOMETRY_NAME=geom -lco FID=gid -nlt MULTIPOLYGON \
    -f PostgreSQL PG:'host={myhost} user={myloginname} dbname={mydbname} password={mypassword}' \
    /vsizip/{zipfile} {tablename}

Then you won't need to partition your table for import.

-1

Right, an answer - or more rather a work-around. I can open it using Manifold (which is what my project is in anyway, I was just using QGIS for the heavy lifting) by linking to the table (which is what I was doing anyway) and giving it a very arbitrary default position to look at (e.g. X 1,2 and Y 1,2). This will allow the table to be loaded with anything at those coordinates (which is nothing).

From there, the layer can be updated with what is currently being looked at by the Map component using the AOI Update tool. This is actually how our contours currently work anyway (set up by my predecessor).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.