Efficiently query two properties QGIS spatialite
1
I have a simple query that involves two steps:
- non-spatial criteria (should be super-fast)
- spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (which is only one polygon). Is this possible?
SELECT 31H.* FROM bois_corridor_forestier, 31H
WHERE bois_corridor_forestier.ID="1" /*run this first*/
AND st_intersects(bois_corridor_forestier.geometry, 31H.geometry)
sql qgis-3 spatialite st-intersects
edited yesterday
Taras
2,2892727
asked yesterday
user3386170user3386170
638422
add a comment |
1
I have a simple query that involves two steps:
- non-spatial criteria (should be super-fast)
- spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (which is only one polygon). Is this possible?
SELECT 31H.* FROM bois_corridor_forestier, 31H
WHERE bois_corridor_forestier.ID="1" /*run this first*/
AND st_intersects(bois_corridor_forestier.geometry, 31H.geometry)
sql qgis-3 spatialite st-intersects
edited yesterday
Taras
2,2892727
asked yesterday
user3386170user3386170
638422
add a comment |
1
1
1
I have a simple query that involves two steps:
- non-spatial criteria (should be super-fast)
- spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (which is only one polygon). Is this possible?
SELECT 31H.* FROM bois_corridor_forestier, 31H
WHERE bois_corridor_forestier.ID="1" /*run this first*/
AND st_intersects(bois_corridor_forestier.geometry, 31H.geometry)
sql qgis-3 spatialite st-intersects
edited yesterday
Taras
2,2892727
asked yesterday
user3386170user3386170
638422
I have a simple query that involves two steps:
- non-spatial criteria (should be super-fast)
- spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (which is only one polygon). Is this possible?
SELECT 31H.* FROM bois_corridor_forestier, 31H
WHERE bois_corridor_forestier.ID="1" /*run this first*/
AND st_intersects(bois_corridor_forestier.geometry, 31H.geometry)
sql qgis-3 spatialite st-intersects
sql qgis-3 spatialite st-intersects
edited yesterday
Taras
2,2892727
asked yesterday
user3386170user3386170
638422
edited yesterday
Taras
2,2892727
asked yesterday
user3386170user3386170
638422
edited yesterday
Taras
2,2892727
edited yesterday
Taras
2,2892727
edited yesterday
Taras
2,2892727
2,2892727
asked yesterday
user3386170user3386170
638422
asked yesterday
user3386170user3386170
638422
asked yesterday
user3386170user3386170
638422
638422
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
3
your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.
There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.
with selection as (select * from bois_corridor_forestier where ID = "1")
select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)
answered yesterday
PieterBPieterB
2,6631128
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right joinis not part of QGIS set of sql queries. usingleft joininstead just retreives all the features.
– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
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
});
}
});
draft saved
draft discarded
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%2f316105%2fefficiently-query-two-properties-qgis-spatialite%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
3
your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.
There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.
with selection as (select * from bois_corridor_forestier where ID = "1")
select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)
answered yesterday
PieterBPieterB
2,6631128
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right joinis not part of QGIS set of sql queries. usingleft joininstead just retreives all the features.
– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
add a comment |
3
your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.
There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.
with selection as (select * from bois_corridor_forestier where ID = "1")
select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)
answered yesterday
PieterBPieterB
2,6631128
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right joinis not part of QGIS set of sql queries. usingleft joininstead just retreives all the features.
– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
add a comment |
3
3
3
your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.
There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.
with selection as (select * from bois_corridor_forestier where ID = "1")
select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)
answered yesterday
PieterBPieterB
2,6631128
your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.
There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.
with selection as (select * from bois_corridor_forestier where ID = "1")
select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)
answered yesterday
PieterBPieterB
2,6631128
edited yesterday
answered yesterday
PieterBPieterB
2,6631128
answered yesterday
PieterBPieterB
2,6631128
answered yesterday
PieterBPieterB
2,6631128
2,6631128
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right joinis not part of QGIS set of sql queries. usingleft joininstead just retreives all the features.
– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
add a comment |
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right joinis not part of QGIS set of sql queries. usingleft joininstead just retreives all the features.
– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/….
– user30184
yesterday
right join is not part of QGIS set of sql queries. using left join instead just retreives all the features.– user3386170
5 hours ago
right join is not part of QGIS set of sql queries. using left join instead just retreives all the features.– user3386170
5 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
@user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can.
– user3386170
4 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically.
– user30184
2 hours ago
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f316105%2fefficiently-query-two-properties-qgis-spatialite%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
