1

I'm trying to update the sites table with any ancient woodlands that each site intersects. I'm running the SQL that looks like below, which works but is very slow and doesn't appear to use spatial indexes. Is there a way to rewrite it that would use them?

UPDATE sites 
SET woodlands = (
    SELECT name 
    FROM ancientwoodlands, sites 
    WHERE ST_Intersects(
        (SELECT sites.geom WHERE sites.key_column = 1),
        ancientwoodlands.geom
    )
    LIMIT 1
) 
WHERE key_column = 1;
4
  • That's not even valid, what do you expect this to do? (select sites.geom where sites.key_column={0}) I think you need a consultant. Aug 21 '17 at 19:13
  • I've edited out the python script to just show the sql Aug 21 '17 at 19:40
  • the subquery in the st_intersects seems redundant
    – ziggy
    Aug 21 '17 at 20:02
  • 3
    When you unroll the query to look at it, it just doesn't look right -- the missing FROM stands right out.. Best practice is to use an explicit JOIN which can add legibility and order to SQL. LIMIT 1 is often a hack for queries returning more rows than intended; it makes the query return one row, but it's seldom the right one.
    – Vince
    Aug 21 '17 at 22:02
1

There is great info on indexing in POSTGIS here. To use the index you can use && prior to ST_Intersects to first get the features that have intersecting bounding boxes.

However, this is unnecessary because the index is automatically used for ST_Intersects.

Most of the commonly used functions in PostGIS (ST_Contains, ST_Intersects, ST_DWithin, etc) include an index filter automatically. But some functions (e.g., ST_Relate) do not include and index filter.

1

@Vince is right, a JOIN is usually clearer and LIMIT 1 is just random. In your case I guess the field woodlands should contain the names of all woodlands intersecting. So you may want to use an aggregate function. In case there are multiple Geometries per woodland, I added DISTINCT.

UPDATE sites 
SET woodlands = (
    SELECT string_agg(DISTINCT a.name,',')
    FROM ancientwoodlands a
    JOIN sites s
    ON ST_Intersects(s.geom, a.geom)
    WHERE s.key_column = 1
) 
WHERE key_column = 1;

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.