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

  1 | 01030000000100000004000000000000000000000000000000000000000000000000000000
(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
  • 1
    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
  • 1
    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
  • 1
    @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
  • 1
    @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.

| improve this answer | |
  • 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

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.