[postgis-users] sum or not sum..

[Ricardo Scachetti Pereira]

    Hi Gus,

    The problem is that your last query is creating the cartesian 
product of tables orig1 and orig2. See the URL below for detailed 
explanations of what a cartesian product is:

http://www.fluffycat.com/SQL/Cartesian-Joins/
http://www.google.com.br/search?q=cartesian+product&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

    In practice, you are producing the combinations of every feature in 
table orig1 with every other feature in orig2. For this reason, you are 
adding the area of each feature several times.

    To correct that you need to constrain the cartesian product by 
creating a proper join. That is, you must say which subset (rows) of the 
cartesian product you want returned. You can do this by either using the 
JOIN table ON (a.id = b.id) construct or adding the equivalent 
constraint to the WHERE clause.

    I hope this helps.

    Cheers

Ricardo



Gustavo Ces wrote:
> Hi all,
>  
>     I´ve got two tables with geoms. When i try to sum(area) something 
> strange happens:
>  
> select sum(area(the_geom)) from orig1
>  
> 61301.4750045538
>
> select sum(area(the_geom))from orig2
>  
> 33996.0705435276
>  
>  
> but now...
>  
> select sum(area(a.the_geom)) as sum1,sum(area(b.the_geom))as sum2 from 
> orig1 a,orig2  b
>
> sum1                        sum2
> 11218169.9258333     4963426.29935503
>  
> smells like postgresql basic concept unknowed for me.... :(
>  
> Gus
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>