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.