<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18876"></meta>
<meta name="SKYPE_FRAMEID" content="UKSDOXEVME"></meta>
<meta id="skype_v3_tb_marker_id" name="SKYPE_PARSING_HAS_FINISHED" content="metacontent"></meta>
</head>
<body>Hallo
<div align="left"> </div>
<div align="left">As Chris wrote yo definitly want ST_Intersects in the where-clause because then you will get the gain from the bouding box comparasion.</div>
<div align="left"> </div>
<div align="left">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.</div>
<div align="left"> </div>
<div align="left">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.</div>
<div align="left"> </div>
<div align="left">So, to get some speed,</div>
<div align="left">create indexes:</div>
<div>create index idx_v_al001_geom on alk.v_al001 using gist(the_geom);</div>
<div>create index idx_el_alle_geom on einzellagen.el_alle using gist(the_geom);</div>
<div> </div>
<div>then you have to analyze the tables to tell the planner about your new indexes:</div>
<div>analyze alk.v_al001;</div>analyze einzellagen.el_alle;
<div> </div>
<div>then I would write your query like this:</div>
<div> </div>
<div>create table einzellagen.alk_kombi as<br />
SELECT g.oid, g.code, b.wlg_nr, b.wlg_name,<br />
ST_Area(ST_Intersection( g.the_geom, b.the_geom )) as f_alk_teilfläche,<br />
ST_Area(g.the_geom) as f_alk_gesamt, g.the_geom<br />
FROM alk.v_al001 AS g, einzellagen.el_alle AS b<br />
where ST_Intersects( g.the_geom, b.the_geom ) ;</div>
<div> </div>
<div>note that ST_Intersection is totally different from ST_Intersects</div>
<div> </div>
<div>read about them here</div>
<div><a href="http://postgis.org/documentation/manual-1.5/ST_Intersects.html">http://postgis.org/documentation/manual-1.5/ST_Intersects.html</a></div>
<div><a href="http://postgis.org/documentation/manual-1.5/ST_Intersection.html">http://postgis.org/documentation/manual-1.5/ST_Intersection.html</a></div>
<div> </div>
<div>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.</div>
<div> </div>
<div>Hope that helps</div>
<div> </div>
<div>Nicklas</div>
<div><br />
</div>
<div> </div>
<div><br />
2010-02-09 vollbotz wrote:<br />
<br />
<br />
>Hallo,<br />
>i would like to use the following sql statement:<br />
><br />
>create table einzellagen.alk_kombi as<br />
>SELECT g.oid, g.code, b.wlg_nr, b.wlg_name,<br />
>AREA (Intersection ( g.the_geom, b.the_geom )) as f_alk_teilfläche,<br />
>AREA (g.the_geom) as f_alk_gesamt,<br />
>g.the_geom<br />
>FROM alk.v_al001 AS g, einzellagen.el_alle AS b<br />
>where INTERSECTION ( g.the_geom, b.the_geom ) = TRUE;<br />
><br />
>> it should calculate the area of intersecting geometries<br />
>>> syntax is ok<br />
>>> number of Geometries: 6.500.000 zu 2000<br />
><br />
>Problem: Quest need a few day - and then i stop with no result....<br />
>>> In ArcMap it works within hours....<br />
><br />
>Is there something i could change to get a better performance (hardware is<br />
>ok) ??<br />
><br />
>Thanks / Greets<br />
><br />
>-- <br />
>View this message in context: http://old.nabble.com/need-better-performance-tp27517322p27517322.html<br />
>Sent from the PostGIS - User mailing list archive at Nabble.com.<br />
><br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
></div>
</body>
</html>