0

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.

  • 3
    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 '18 at 6:45
  • 5
    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 '18 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 '18 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 '18 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 '18 at 22:35
0

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.