[postgis-users] Centroid out a list of points

[strk]

On Fri, Jun 12, 2009 at 10:54:11AM +0100, Pedro Doria Meunier wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Thank you Strk.
> 
> The actual query is as thus:
> 
> select astext(st_centroid(st_collect(coordinates))) from units where
> customer_id=someid
> 
> So there's a 'where' clause in it :]

The 'where' clause specifies a condition on the 'customer_id' field.
The only index the planner might use would be on on that column.
Have an index there (looks like the analizer already thinks it'd be
worth using, estimating only 9 rows selectivity for that clause, where
4 are returned in reality)

--strk;

> EXPLAIN ANALYZE tells me:
> 
> "Aggregate  (cost=49790.11..49790.13 rows=1 width=25) (actual
> time=524.380..524.381 rows=1 loops=1)"
> "  ->  Seq Scan on units  (cost=0.00..49790.09 rows=9 width=25)
> (actual time=445.782..524.231 rows=4 loops=1)"
> "        Filter: (customer_id = 20)"
> "Total runtime: 524.451 ms"
> 
> The runtime is a bit stiff, don't you agree? ;-)

Probably. How many rows does the table have ?

--strk; 

 Free GIS & Flash consultant/developer      ()  ASCII Ribbon Campaign
 http://foo.keybit.net/~strk/services.html  /\  Keep it simple!