[postgis-users] need better performance

[=?utf-8?Q?Nicklas_Av=E9n?=]

Hallo As Chris wrote yo definitly want ST_Intersects in the where-clause because then you will get the gain from the bouding box comparasion. What version of postgis are you using when you are not using the ST_ prefix of the functions. They are there without the prefix in the later releases but are often a slower alternative. If you have an old postgis version it might help to upgrade too because than you will also get a newer GEOS-library which arrects functions like ST_Intersects and ST_Intersection. So, to get some speed,create indexes:
create index idx_v_al001_geom on alk.v_al001 using gist(the_geom);
create index idx_el_alle_geom on einzellagen.el_alle using gist(the_geom);
 
then you have to analyze the tables to tell the planner about your new indexes:
analyze alk.v_al001;analyze einzellagen.el_alle; 
 
then I would write your query like this:
 
create table einzellagen.alk_kombi as
SELECT g.oid, g.code, b.wlg_nr, b.wlg_name,
ST_Area(ST_Intersection( g.the_geom, b.the_geom )) as f_alk_teilfläche,
ST_Area(g.the_geom) as f_alk_gesamt, g.the_geom
FROM alk.v_al001 AS g, einzellagen.el_alle AS b
where ST_Intersects( g.the_geom, b.the_geom ) ;
 
note that ST_Intersection is totally different from ST_Intersects
 
read about them here
http://postgis.org/documentation/manual-1.5/ST_Intersects.html
http://postgis.org/documentation/manual-1.5/ST_Intersection.html
 
If you have an old postgis version you might also gain from upgrading because you then will get a newer GEOS library which performs the intersection.
 
Hope that helps
 
Nicklas


 

2010-02-09 vollbotz wrote:


>Hallo,
>i would like to use the following sql statement:
>
>create table einzellagen.alk_kombi as
>SELECT g.oid, g.code, b.wlg_nr, b.wlg_name,
>AREA (Intersection ( g.the_geom, b.the_geom )) as f_alk_teilfläche,
>AREA (g.the_geom) as f_alk_gesamt,
>g.the_geom
>FROM alk.v_al001 AS g, einzellagen.el_alle AS b
>where INTERSECTION ( g.the_geom, b.the_geom ) = TRUE;
>
>> it should calculate the area of intersecting geometries
>>> syntax is ok
>>> number of Geometries: 6.500.000 zu 2000
>
>Problem: Quest need a few day - and then i stop with no result....
>>> In ArcMap it works within hours....
>
>Is there something i could change to get a better performance (hardware is
>ok) ??
>
>Thanks / Greets
>
>-- 
>View this message in context: http://old.nabble.com/need-better-performance-tp27517322p27517322.html
>Sent from the PostGIS - User mailing list archive at Nabble.com.
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100209/0adc9d17/attachment.html>