Saturday, 23 April 2016

postgis - ST_MakeValid() leaving data unaltered?


I recently received a PostGIS instance and am trying to figure out how much of my current processing workflow (in proprietary tools) can be ported over to PostGIS.


One of the most frequent tasks I encounter is validating/repairing geometry.


Here's what I've done and here's where I'm stuck:


I load some data (multipolygon) to postGIS. Then I use the following statement to identify and copy invalid geometries to a different table:


create table broken_polygons as
select * from polygons
where not st_isvalid(geom);

Then, I get rid of the offending records:



delete from polygons
where not st_isvalid(geom);

This gives me a chance to take a closer look at the bad geometry; sometimes there are only one or two, sometimes there are hundreds. But when I try to do this:


select *, st_makevalid(geom) as fixed_polygons
from "broken_polygons"

It gives me a result, but when I load up fixed_polygons in QGIS and check the geometry validity (GEOS), all of them error out: nothing has been modified.


In this particular case, all of the errors are self-intersections.


I am using PostGIS 2.3.2 on postgres 9.6.2



I am not a DBA, just someone trying to get things done with the tools in front of me; so I seek examples of precise syntax to get ST_MakeValid to work.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...