Trjtdtk

I2C signal and power over long range (10meter cable)

How did Monica know how to operate Carol's "designer"?

Why has "pence" been used in this sentence, not "pences"?

Identify a stage play about a VR experience in which participants are encouraged to simulate performing horrific activities

What if somebody invests in my application?

A social experiment. What is the worst that can happen?

What should I use for Mishna study?

Can the electrostatic force be infinite in magnitude?

Latex for-and in equation

Female=gender counterpart?

Suggestion on Missing Index Creation

What would you call a finite collection of unordered objects that are not necessarily distinct?

Can one define wavefronts for waves travelling on a stretched string?

NIntegrate: How can I solve this integral numerically? NIntegrate fails while Integrate works

Bob has never been a M before

Can a controlled ghast be a leader of a pack of ghouls?

Superhero words!

Indicating multiple different modes of speech (fantasy language or telepathy)

The verb "to prioritize"

What is this type of notehead called?

How to deal with loss of decision making power over a change?

tikz grid without top edge

Can a malicious addon access internet history and such in chrome/firefox?

Is there enough fresh water in the world to eradicate the drinking water crisis?

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

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,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

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,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

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,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

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,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

share|improve this question

edited Mar 20 at 12:56

Taras

2,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

edited Mar 20 at 12:56

Taras

2,3112727

edited Mar 20 at 12:56

Taras

2,3112727

asked Mar 20 at 12:44

user3386170user3386170

638522

asked Mar 20 at 12:44

user3386170user3386170

638522

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,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
  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,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
  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,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
  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,6631128

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,6631128

answered Mar 20 at 12:56

PieterBPieterB

2,6631128

answered Mar 20 at 12:56

PieterBPieterB

2,6631128