1

I've got some QGIS data where each plot is described with some attributes e.g. Shape_Area which is the field describing the area of plot in m2. Normally I search for plot using the expression e.g. Shape_Area >= 1000 AND Shape_Area <= 1100, however this time I need to find two connected plots given their total area.

What's the best way to approach this problem?

  • Something like: take each 2-tuple of connected plots, add up their Shape_Areas and evaluate if the sum is within a range? How can the plots be connected? – Gabriel De Luca Feb 9 at 16:17
  • I am quite new to spatial data and QGIS. How to connect plots? Let's say if they share one or two points together, then let's consider them adjacent. What would be the easiest solution? Regarding the total area, yes, let's sum 2 values and use resulting value in the higher/less than condition. – NeverEndingQueue Feb 9 at 16:35
  • Join attributes by location (Summary) tool can create a table with the sum operation of the Shape_Area field, for the touches predicate. But it assumes all the plots that touches each one. There must be some appropriate SQL query to return each pair of plots that are touched, but I don't know it. I hope that some specialists can offer their answers. Meanwhile I conduct my research on this. The question seems very good to me. – Gabriel De Luca Feb 9 at 17:45
0

The Virtual Layer way

Assuming that you have a Ploygon vector layer named plots.
Assuming that each feature of the plots layer has plotId and area fields.

You can create a Virtual Layer with one feature for each pair of geometries in the layer plots that are touched, with the following SQL query:

SELECT 
    ST_Union( p1.geometry, p2.geometry) geometry, 
    p1.plotId plotId1, 
    p2.plotId plotId2, 
    ( p1.area + p2.area) sum_area 
FROM 
    plots p1, 
    plots p2 
WHERE 
    ST_Touches( p1.geometry, p2.geometry) 
    AND 
    p1.plotId < p2.plotId

The Output is a new Multipolygon virtual vector layer with one (see DISCLAIMER and EDIT) feature for each pair of plot geometries that touche, plotId1 and plotId2 fields, and sum_area field with the sum of the areas to search.

Note that when digitizing the test plots I had to enable the topological edition for the perimeter segments to be recognized by the ST_Touches() function, if your plots do not meet topological conditions you can try adding an OR ST_Overlaps() similar condition in the WHERE clause.


DISCLAIMER:

I am giving my first steps with SQL queries in PostGIS, therefore all kinds of suggestions and observations are accepted and welcome. I don't know how to select a single feature for each pair of plots. For example, if the geometries of plotId 5 and plotId 6 are touching each other, there will be a feature in which plotId1 = 5 and plotId2 = 6, but there will also be another feature in which plotId1 = 6 and plotId2 = 5. Both features with the same sum of area and spatial equivalent geometries.

EDIT:

Thank you Cyril very much for this comment to avoid mirroring.

  • 2
    Gabriel De Luca, to avoid mirroring, add the following condition to the end of your query: and p1.plotId<p2.plotId... – Cyril Feb 11 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.