# How do you combine multiple spatial functions in one query?

I want to find out the intersection and difference in the same query using postgresql.

How can I combine both st_intersect and st_Difference in same query and return the result.I want to compute the union without using ST_Union function.I want to find union only using intersection and difference.

I have two tables - table1 and table2, both table consists of a the_geom column. the_geom column has a polygon stored in it. Each table has one polygon stored in it.

I want to compute the union without using ST_Union function.I want to find union only using intersection and difference.

I used st_intersection :

``````select 1 as keys,(st_intersection(tale1.the_geom,table2.the_geom)) from table1,table2

keys |                                                                      st_intersection
----+------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 01030000000100000004000000000000000000F03F000000000000E03F0000000000000040000000000000000000000000000000000000000000000000000000000000F03F000000000000E03F
(1 row)
``````

And when I used difference

select 1 as keys,(st_difference(tale1.the_geom,table2.the_geom)) from table1,table2

I get output as:

`````` keys |                                                                       st_d
ifference

----+---------------------------------------------------------------------------
--------------------------------------------------------------------------------
-
1 | 01030000000100000004000000000000000000000000000000000000000000000000000000
000000000000F03F000000000000F03F000000000000E03F00000000000000000000000000000000
(1 row)
``````

How can I combine the above to query like : st_intersection + st_difference?

I tried :

``````select 1 as keys,(st_intersection(table1.the_geom,table2.the_geom)) from table1,table2 UNION select ,st_difference(table1.the_geom,table2.the_geom) from table1,table2;
``````

But it returned an error saying error after select ^ ,st_difference(....

• I'm confused by what you think this will do. First fix the typo (tale1) in the ST_Intersection. The show each of the two individual queries (e.g. intersection and difference) you want; and the results of those queries. Then tell us what you want to do instead. – BradHards Nov 13 '14 at 4:03
• Its only challenging because you're not providing the detail. Maybe this is a homework problem? Can you use ST_Relates? Maybe ST_Collect? Perhaps I should leave you to it... – BradHards Nov 13 '14 at 5:14
• I have answered this on SO, stackoverflow.com/questions/26900897/…, before realizing that this was a sock puppet for rdvsm and yet another unrevealed homework question. – John Powell Nov 13 '14 at 7:18
• @Rdvsm Please be aware that GIS SE does not have a policy against homework but its users sometimes exercise downvoting when they see no sign of research on questions, irrespective of whether they are homework. – PolyGeo Nov 13 '14 at 7:50
• @PolyGeo. I have no issue with homework, so long as some minimal effort has been made, but I do have an issue with simply pasting the assignment question into stack exchange, something that has happened a few times in the last week. This particular question is the least eggregious in this regard, but was the straw the broke the camel's back, so to speak. – John Powell Nov 13 '14 at 11:35

In pseudo code:

``````union (A, B) = combination of
difference (A, B),
difference (B, A),
intersect (A, B)
``````

In other words, the union of two regions is the combination of their symmetric difference and their intersection.

To actually do the combination, use ST_Collect() which

is in general orders of magnitude faster than ST_Union because it does not try to dissolve boundaries or validate that a constructed MultiPolgon doesn't have overlapping regions.

• Yes, I was having the same idea.However I am not able to write a sql query which combines this and returns me the_geom value of union – Force.comBat Nov 13 '14 at 5:47
• Yes it is a combination of symmetrical difference , Can you tell me how can form a query where I could use Collect(Intersection and Symdifference)? – Force.comBat Nov 13 '14 at 6:01
• Follow the link for collect() and see similar help for the the others. – Martin F Nov 13 '14 at 14:22
• Don't put the outer parentheses in (st_difference(tale1.the_geom,table2.the_geom)) – Martin F Nov 13 '14 at 14:26