[postgis-users] intersecting and exporting

[Sandro Santilli]

On Thu, Nov 08, 2012 at 11:46:22PM -0600, Seb wrote:

> CREATE OR REPLACE VIEW subset1 AS
> SELECT gid,
>     ST_Intersection((SELECT geom FROM gshhs_c_l1 WHERE gid=4),
> 	(SELECT geom FROM regions WHERE id=1))
> FROM gshhs_c_l1;

Above, you're running two subselects for each rows in gshhs_c_l1.
Additionally, for each row in gshhs_c_l1, you're running the
_SAME_ binary function against the result of those subqueries.
Finally, the binary function you're running is very expensive.

Did you mean to run something like this instead ?

 SELECT ST_Intersection(g1.geom, g4.geom)
 FROM gshhs_c_l1 g1, gshhs_c_l1 g4
 WHERE g1.gid=1 AND g4.gid=4;

--strk;

 http://www.cartodb.com - Map, analyze and build applications with your data

                                       ~~ http://strk.keybit.net