Optimising a PostGIS table

Question

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);

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.

user25730 · Answer

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).

Alex Leith · Answer

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.

Optimising a PostGIS table

2 Answers

Add your own answers!

Ask a Question