CODEWITHSUNDEEP
sqlpostgresqlpostgisspatial-query
Eric
Eric

Reputation: 57

Using ST_Union combined with ST_contains

I have a database of polygon features, where each polygon is constructed by smaller seamless polygons of the same type. Each of these polygons (composed of smaller polygons) is also sometimes intersecting with another polygon features also compose of their own sub-polygones.

enter image description here

There is also set of polylines in the database. My aim is to merge all the polygons into one and then extract all those polylines that they are completely withing the big polygon. I just want to use a select query for this purpose. I have tried below script but I could implement ST_Union inside this patch of script.

> Select * From tbl.A a, tbl.b b  where
> ST_Contains(a.geometry,b.geometry) and pol_id in ('1','2','3')

Above returns polylines inside each polygone but those polylines which are half in dark green polygne and half in purple polygon are not retrieved. I am sure ST_Union will help here but I am not sure how to implement it. Thanks for your help.

Upvotes: 0

Views: 464

Answers (1)

Avocado
Avocado

Reputation: 901

You could use a CTE to establish the "unioned" polygon:

WITH polygon AS (
   SELECT ST_Union(polygon_table.geometry) from polygon_table
)
SELECT
  *
FROM
    linestring_table
  JOIN
    polygon ON ST_Contains(polygon.geometry, linestring_table.geometry)

Upvotes: 0

Related Questions