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:
- Altitude (double) e.g. 129.5
- Class (char) e.g. Basic
- geometry (geometry, a multiline string)
- OID (integer)
- 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);
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.