How to extract only a fragment of composite type

user_6454038 0
Viewed 102

I have the following SQL code that does some spatial operations of PostGIS in PostgreSQL. 

SELECT 
ST_Dump(
    ST_SymDifference(a.geom, b.geom) 
)
FROM smp_areas a, smp_areas b

It returns for me in a format of a composite type, composed of two parts:

output

How to get only the second part (that, in this case, contains the geometry that I need)?

Ps: I've read that re-arranging like the following would work:

SELECT * FROM( myquery ) as alias;

I tried but returned the same result. Can anyone help?

postgis postgresql sql
2 Answers

To access the geometry, you need to put extra parenthesis around the ST_Dump() and add the .geom. Like this:

SELECT 
(ST_Dump(
    ST_SymDifference(a.geom, b.geom) 
)).geom
FROM smp_areas a, smp_areas b
user_47883730

Could you wrap that query in something like

Select * from (my query) as alias where geometry_dump 
Like '{2}%'

P.s. the above is sql server format so may not directly translate to postgres.

user_83358970
Related