1

I have a queried layer and a buildings layer and I am currently running the following query to find areas of intersection using the St_Intersection() function inside the QGIS DB Manager plugin:

SELECT ST_Intersection(buildings.geom, queried.geom) as inter from buildings, queried

ST_Intersection

(I'm hoping the function is correct as it's been running for over an hour now...)


But I want to repeat this and combine it using ST_Area() so that the output layer constains all intersection features with another column in the table showing the area.

How could I combine these two functions into one query?

closed as too broad by Vince, Mapperz Dec 5 '16 at 15:02

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • It's unlikely this query is correct (unless every shape overlaps). See the documentation on how to use a JOIN clause utilizing ST_Intersects to constrain the query to only overlapping geometries. Then you have a number of ways to access the intersection geometry... My preference is via an inline query with a FROM ( SELECT ... ) block (also documented) – Vince Dec 5 '16 at 14:41
  • @Vince - Thanks, I will take a closer look at the docs =) – Joseph Dec 5 '16 at 14:47
2

Do ST_Intersection in a subquery

SELECT a.intersection, ST_Area(a.intersection) FROM
(SELECT ST_Intersection(ST_GeomFromText('POLYGON (( 76 373, 407 450, 361 168, 76 373 ))'),
ST_GeomFromText('POLYGON (( 230 460, 520 232, 415 502, 230 460 ))')) AS intersection) a;
  • Interesting, thanks for your answer. I'll try and incorporate that into what I am doing :) – Joseph Dec 5 '16 at 14:47
  • This answer does not address how to properly JOIN multiple rows from muliple tables with a spatial relationship constraint. – Vince Dec 5 '16 at 14:53
  • True, is is not a comprehensive answer but it is addressing somehow the question in the title as it stands now "How to combine ST_Intersection() and St_Area() into a single query?". – user30184 Dec 5 '16 at 15:04

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