I am still new to PostgreSQL and am hoping someone could help be optimize the following code for speed. The hg100 table has 17 million records, curstate has 11 million, and getting any information related to it is painful so want to make sure I am coding optimally and correctly. This script does run, however , I get an error:

ERROR: GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Self-intersection at or near point 5095.56885091 6240.45418185 at 537.56804091 62441.4540885 ********** Error **********

The first time I ran the code it took 1.5 hours to get that error message. To try and solve this issue I placed ST_MAKEVALID; it crashed again with the same error but it took 3.3 hours instead.

I am trying to get a summary of lang. The result will have 15 rows and the area within each according to disturbance state.

SELECT d.lang, st_area(st_union(st_intersection(st_makevalid(b.geom), st_makevalid(d.geom)))) / 10000 distbuf_area
     FROM p797.hg100 b, p797.ages d 
     WHERE st_intersects(b.geom,d.geom)
     AND b.hex_id in 
              SELECT hex_id 
              FROM p797.curstate_20170120_100 
              WHERE disturbed = 1)
     GROUP BY lang;

Geoms and Hex_ID's are indexed. Hex_id is PK.

PostgreSQL 9.3

  • What is the relations between the both tables? – huckfinn Jan 22 '17 at 4:19
  • Not sure I quite understand, again new, but one (ages) has 15 rows that encompass hg100. THe only relationship they have is spatial location – D_C Jan 22 '17 at 4:29
  • I ask because this ..FROM p797.hg100 b, p797.ages d is expensive. Do you have spatial indexes on b.geom and d.geom. ..revenant.ca/www/postgis/workshop/indexing.html – huckfinn Jan 22 '17 at 5:14
  • geom's and hex_id's have indexes, yes. I am sure there are better ways to code this to make it more efficient... please! – D_C Jan 22 '17 at 5:17
  • 1
    First, update all unvalid geoms to make them valid. Try make_valid() after st_intersection: st_makevalid(st_intersection(b.geom, d.geom))... – WKT Jan 22 '17 at 11:43

Your Answer

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

Browse other questions tagged or ask your own question.