[postgis-users] Looking for help figuring out overlays

[Jonathan Schultz]

Hello,

I think I've reached the limit of my PostGIS skills so hoping someone can help me solve a problem.

What I'm trying to do is create overlays from a collection of overlapping polygons as described here: http://blog.cleverelephant.ca/2019/07/postgis-overlays.html

The data I am working from is a publicly available dataset of the fire history of Western Australia: https://catalogue.data.wa.gov.au/dataset/dbca-fire-history I downloaded the data as CSV and loaded it into a PostgreSQL table. That much seems fine.

I then select a region and dump the MULTIPOLYGONs into POLYGONs using the following query:

> CREATE TABLE dbca_fire_history_dump AS
>     SELECT fih_perimeter, fih_author, fih_season1, fih_capt_meth, fih_year1, fih_district, fih_burn_purp, fih_detect_fdi, fih_poly_type, fih_fire_type, fih_master_key, fih_number, fih_date1, fih_cause, fih_ignit_type, fih_comment, fih_hectares, fih_hist_distr, fih_fire_seaso, fih_name, (st_dump(geometry)).geom AS geometry FROM dbca_fire_history
>     WHERE st_intersects(geometry, st_makeenvelope(<longmin>, <latmin>, <longmax>, <latmax>, 4326));

and from there create the boundary polygon using this query:

> CREATE TABLE dbca_fire_history_boundaries AS
>   SELECT ST_Union(ST_ExteriorRing(geometry)) AS geometry
>     FROM dbca_fire_history_dump;

This is where things get weird. If I select a small enough region (eg 36S 116E to 35S 117E) the result looks fine. But if I select a slightly larger region (eg 36S 116E to 34S 117E) then the result is very strange - the geometry seems to have snapped to a much larger grid to the extent that it bears no obvious resemblance to the data.

I've pasted images that demonstrate the result here: https://pasteboard.co/JcPeDUk.png and here: https://pasteboard.co/JcPfju0H.png

Thanks in advance for any help or suggestions.

Cheers,
Jonathan