[postgis-users] need better performance
chris.hermansen at timberline.ca
Tue Feb 9 09:18:33 PST 2010
AREA (Intersection... might be faster if you use AREA(ST_Intersection...
where clause you want ST_Intersects(g.the_geom, b.the_geom)
also check to make sure you have spatial indexes on your geometries in g
Beyond that, this kind of read / compose / insert of data can cause poor
backend performance depending on your configuration. I seem to recall
us having a similar problem and needing to increase some PostgreSQL
parameter related to write buffer size or number of write buffers.
Check here http://www.varlena.com/GeneralBits/Tidbits/perf.html for example.
> 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
> ok) ??
> Thanks / Greets
Chris Hermansen · chris.hermansen at timberline.ca · skype:clhermansen
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group Ltd · http://www.timberline.ca
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
More information about the postgis-users