1

I am trying to make some calculations using PostGIS and PostgreSQL. There are 3 important tables : wbn, wgo and wvb. These tables have about 500.000 objects in them (geometries). The problem is that my calculations are taking way to much time. I think I found the query's responsible for this;

INSERT INTO bio_schema.wgotemp1(wbn_uidn,wgo_uidn) 
            ( 
                SELECT gdi_agiv_schema.wbn.uidn, gdi_agiv_schema.wgo.uidn
                FROM gdi_agiv_schema.wbn, gdi_agiv_schema.wgo
                WHERE ST_Intersects(gdi_agiv_schema.wgo.the_geom, gdi_agiv_schema.wbn.the_geom)='t'
                    AND ST_Length(ST_Intersection(gdi_agiv_schema.wgo.the_geom, gdi_agiv_schema.wbn.the_geom)) > 2
                    AND gdi_agiv_schema.wbn.uidn = f_uidn
                    AND gdi_agiv_schema.wgo.type = f_type

            );

AND

INSERT INTO bio_schema.tempwvb(wbn_uidn,wvb_uidn,sum) 
            (
                SELECT gdi_agiv_schema.wbn.uidn, gdi_agiv_schema.wvb.uidn, ST_Length(ST_Intersection(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom))
                FROM gdi_agiv_schema.wbn, gdi_agiv_schema.wvb
                WHERE ST_Intersects(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom)='t' 
                    AND gdi_agiv_schema.wbn.uidn = f_uidn
            );

What they are doing is looking for geometries (from the tables wgo and wvb) located in a certain polygon (from table wgo). This takes a lot of time, but for my calculations it is absolutely necessary to know which objects from the tables wgo and wvb are located inside this polygon and what their length is.

What can I do to speed up my queries?

Thanks

  • 2
    Post an EXPLAIN? – dbaston Apr 18 '14 at 19:04
  • Do you want to know the length of an object that is fully within a polygon, or partly within? – Stefan Apr 18 '14 at 20:44
1

Create GiST index on tables

CREATE INDEX name ON table USING gist(column);
  • The tables alreadt gave a gist index for the "geom" column – user28088 Apr 18 '14 at 14:32
1

This could speeding up your query.

INSERT INTO bio_schema.tempwvb(wbn_uidn,wvb_uidn,sum) 
(
SELECT
    gdi_agiv_schema.wbn.uidn,
    gdi_agiv_schema.wvb.uidn,
    ST_Length(gdi_agiv_schema.wbn.the_geom)
FROM gdi_agiv_schema.wbn INNER JOIN gdi_agiv_schema.wvb
    ON ST_Intersects(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom)
        AND gdi_agiv_schema.wbn.uidn = f_uidn);

When you want to know the length of an object that lays fully within a polygon you ask if there a intersecting and query the length of this object.

When you want to know the intersection geometry of an object that intersects the boundaries (the hull) of the polygon you have to use ST_Intersection. I can see, at the moment, no enhancement regarding to the query speed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.