[postgis-users] need better performance
nicklas.aven at jordogskog.no
Tue Feb 9 13:21:03 PST 2010
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
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
2010-02-09 vollbotz wrote:
>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,
>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
>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
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users