2

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.

1 Answer 1

5

I suspect QGIS is picking the first geometry column it finds and since you are using *, the first geometry column would be the unchanged geom one.

Try instead to explicitly specify your columns.

SELECT gid, some_field, ST_MakeValid(geom) As fixed_geom
FROM broken_polygons;

In order to get the field to load correctly in QGIS, you may need to specify the geometry type (LineString, Polygon, etc.) for the new field. For polygonal data, you can do this as follows:

SELECT
  fid,
  name,
  ST_CollectionExtract(
    ST_Multi(
      ST_MakeValid(geom)
    ), 3)::geometry(MultiPolygon, 4326) AS fixed_geom
FROM broken_polygons
9
  • this generates a new table in postgis, but it just has a question-mark next to it, instead of being a geographic layer I can drag into QGIS.
    – auslander
    Commented Nov 16, 2017 at 20:53
  • Add a new row for the new table into geometry_columns.
    – user30184
    Commented Nov 16, 2017 at 21:55
  • 2
    try ST_Multi(ST_MakeValid(geom))::geometry(MultiPolygon, your_srid_here). I think the issue is the generic geometry type returned by ST_MakeValid.
    – dbaston
    Commented Nov 16, 2017 at 22:31
  • 2
    sorry, I mean to add it to the chain of function calls, like SELECT fid, name, ST_CollectionExtract(ST_Multi(ST_MakeValid(geom)), 3)::geometry(MultiPolygon, 4326) AS geom FROM broken_polygons
    – dbaston
    Commented Nov 17, 2017 at 15:08
  • 1
    @dbaston, awesome, that worked. Using QGIS's GEOS method for checking validity, the dataset now returns valid output. should this be made its own answer to be marked correct?
    – auslander
    Commented Nov 17, 2017 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.