1

I need to show land plots in QGIS that have two buildings.

My query only shows buildings which are placed on land plots.

enter image description here

Create or Replace VIEW dvi_budivli_view AS
Select
"budivly".*,
"dilyanki"
From
public."budivly",
public."dilyanki"
Where
ST_contains ( public."dilyanki".geom, public."budivly".geom);
3

I'm presuming "budivly" is your land plots and "dilyanki" is your buildings, and that you have an "id" column as a primary key. I've not tested this, but it should get you started.

EDIT to add option to display count:

CREATE OR REPLACE VIEW dvi_budivli_view AS
SELECT
    budivly.id,
    budivly.geom,
    COUNT(dilyanki.id) -- Optional
FROM
    budivly
JOIN dilyanki
    ON ST_Contains(budivly.geom, dilyanki.geom)
GROUP BY
    budivly.id,
    budivly.geom
HAVING
    COUNT(dilyanki.id) > 1
  • You don't want to GROUP By budivly.geom and bidivly.id, as their distinct combination will always be one, and so the having clause will do nothing. – John Powell May 16 at 9:06
  • clarification: HAVING COUNT(dilyanki.id) = 2 – Cyril May 17 at 18:10
  • @JohnPowell I just tested the SQL, and it works the same with or without grouping by budivly.geom (only thing that changed was order of parcels). The HAVING refers to the dilyanki.id, not the budivly.id. – Nate Wanner May 17 at 22:08
  • 1
    @Cyril You may be correct - it depends on whether the OP considers parcel 1 that has 3 buildings to also contain 2 buildings. – Nate Wanner May 17 at 22:09

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.