Dtjytyk

Copenhagen passport control - US citizen

Is there a familial term for apples and pears?

How do you conduct xenoanthropology after first contact?

"which" command doesn't work / path of Safari?

Concept of linear mappings are confusing me

Why CLRS example on residual networks does not follows its formula?

N.B. ligature in Latex

How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?

Infinite past with a beginning?

The magic money tree problem

Extreme, but not acceptable situation and I can't start the work tomorrow morning

How do I create uniquely male characters?

What makes Graph invariants so useful/important?

Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?

least quadratic residue under GRH: an EXPLICIT bound

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Japan - Plan around max visa duration

Draw simple lines in Inkscape

What are these boxed doors outside store fronts in New York?

Non-Jewish family in an Orthodox Jewish Wedding

The use of multiple foreign keys on same column in SQL Server

If Manufacturer spice model and Datasheet give different values which should I use?

Can Medicine checks be used, with decent rolls, to completely mitigate the risk of death from ongoing damage?

Circuitry of TV splitters

Efficiently query two properties QGIS spatialite

How to speed up spatialite-query using spatialindex?PostgreSQL/PostGIS: create spatial viewSpatialite table column is function in itselfselect by location point in polygon with sql queryWhat command to use in PostGIS to get same result as ArcGIS?SpatiaLite : update table with a spatial relationshipsPostgis st_intersects not selecting correct features3d Intersect query compatible with GeoTIFF/DEM?Return boolean type in PostgreSQLExtract intersecting polygon from multipolygon search

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

I have a simple query that involves two steps:

1. non-spatial criteria (should be super-fast)


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

share|improve this question

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

add a comment | 

1

I have a simple query that involves two steps:

1. non-spatial criteria (should be super-fast)


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

share|improve this question

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

add a comment | 

I have a simple query that involves two steps:

1. non-spatial criteria (should be super-fast)


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

share|improve this question

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

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)

share|improve this question

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

share|improve this question

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

edited Mar 20 at 12:56

Taras

2,3733727

edited Mar 20 at 12:56

Taras

2,3733727

asked Mar 20 at 12:44

user3386170user3386170

697622

asked Mar 20 at 12:44

user3386170user3386170

697622

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)

share|improve this answer

edited Mar 20 at 13:06

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 20 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  right join is not part of QGIS set of sql queries. using left join instead just retreives all the features.
  
  – user3386170
  Mar 21 at 19:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  Mar 21 at 19:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 21 at 21:20
  

  
  
  
  

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

Name

Email

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

Name

Email

Required, but never shown

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)

share|improve this answer

edited Mar 20 at 13:06

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 20 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  right join is not part of QGIS set of sql queries. using left join instead just retreives all the features.
  
  – user3386170
  Mar 21 at 19:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  Mar 21 at 19:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 21 at 21:20
  

  
  
  
  

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)

share|improve this answer

edited Mar 20 at 13:06

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 20 at 13:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  right join is not part of QGIS set of sql queries. using left join instead just retreives all the features.
  
  – user3386170
  Mar 21 at 19:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  Mar 21 at 19:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Mar 21 at 21:20
  

  
  
  
  

add a comment | 

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)

share|improve this answer

edited Mar 20 at 13:06

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

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)

share|improve this answer

edited Mar 20 at 13:06

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

answered Mar 20 at 12:56

PieterBPieterB

2,7701128

answered Mar 20 at 12:56

PieterBPieterB

2,7701128