Optimising a PostGIS tableSpeeding up OpenStreetMap PostGIS queryingPostGIS - table with 3d points with fast...
How to tell a function to use the default argument values?
How seriously should I take size and weight limits of hand luggage?
What mechanic is there to disable a threat instead of killing it?
Which is the best way to check return result?
How did the Super Star Destroyer Executor get destroyed exactly?
Forgetting the musical notes while performing in concert
How can saying a song's name be a copyright violation?
Do scales need to be in alphabetical order?
Personal Teleportation: From Rags to Riches
Would Slavery Reparations be considered Bills of Attainder and hence Illegal?
Intersection Puzzle
Short story with a alien planet, government officials must wear exploding medallions
Im going to France and my passport expires June 19th
Valid term from quadratic sequence?
Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?
If human space travel is limited by the G force vulnerability, is there a way to counter G forces?
Can we compute the area of a quadrilateral with one right angle when we only know the lengths of any three sides?
Detention in 1997
Why is this clock signal connected to a capacitor to gnd?
Why can't we play rap on piano?
What are some good books on Machine Learning and AI like Krugman, Wells and Graddy's "Essentials of Economics"
Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?
Assassin's bullet with mercury
How can I deal with my CEO asking me to hire someone with a higher salary than me, a co-founder?
Optimising a PostGIS table
Speeding up OpenStreetMap PostGIS queryingPostGIS - table with 3d points with fast ordering by distancePostGIS Intermittent INDEX PerformancePostgreSQL/PostGIS spatial index - no speed upWhy does QGIS crash when my PostGIS table has a primary key?PostGIS ST_Intersects vs ArcGIS Select by LocationOptimizing PostGIS/Geoserver schema for huge datasetIssue editing nodes on a PostGIS table via QGISPostGIS Optimising ST_Contains'UPDATE 0' results for ST_Within query - locating points within polygons
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.
postgis postgresql table
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 2 more comments
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.
postgis postgresql table
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 aPRIMARY KEYinstead: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.
– RoVo
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
|
show 2 more comments
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.
postgis postgresql table
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
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.
postgis postgresql table
postgis postgresql table
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
edited Aug 31 '18 at 10:39
Damini Jain
1,564217
1,564217
asked Aug 31 '18 at 6:22
user25730user25730
152110
asked Aug 31 '18 at 6:22
user25730user25730
152110
asked Aug 31 '18 at 6:22
user25730user25730
152110
152110
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 aPRIMARY KEYinstead: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.
– RoVo
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
|
show 2 more comments
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 aPRIMARY KEYinstead: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.
– RoVo
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
3
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
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
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
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
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.– RoVo
Aug 31 '18 at 9:02
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.– RoVo
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
@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
@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
|
show 2 more comments
1 Answer
1
active
oldest
votes
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).
answered Sep 2 '18 at 22:53
user25730user25730
152110
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "79"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f294578%2foptimising-a-postgis-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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).
answered Sep 2 '18 at 22:53
user25730user25730
152110
add a comment |
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).
answered Sep 2 '18 at 22:53
user25730user25730
152110
add a comment |
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).
answered Sep 2 '18 at 22:53
user25730user25730
152110
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).
answered Sep 2 '18 at 22:53
user25730user25730
152110
answered Sep 2 '18 at 22:53
user25730user25730
152110
answered Sep 2 '18 at 22:53
user25730user25730
152110
answered Sep 2 '18 at 22:53
user25730user25730
152110
152110
add a comment |
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f294578%2foptimising-a-postgis-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 KEYinstead: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.– RoVo
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