I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing duplicate geometries in the process of fixing invalid geometries. However, the query takes really long to run (days). This is the SQL statement:

CREATE TABLE schema.table_geomfix AS 
SELECT gid,id, ST_MakeValid(geom) as geom 
FROM schema.table_polygons 
WHERE geom IN (SELECT DISTINCT geom FROM schema.table_polygons);

Running the two select statements separately is quick. About 1 minute for the makevalid and 15 seconds on the distinct statement.

  1. Why does it take so long to run the combined statement?
  2. Any advice for improving the query in order to make it faster?

NOTE: There is an index on the geom column.

  • It is slower because rather than just reading the (presumably already cached) data, it's having to write it. It's going to be an order of magnitude slower – Philᵀᴹ Jan 28 '16 at 9:59
  • Check the execution plan using explain (analyze, verbose) – a_horse_with_no_name Jan 28 '16 at 10:01
  • Btw: the distinct in the sub-query is useless and can be removed (but Erwin's query is even better) – a_horse_with_no_name Jan 28 '16 at 10:03

Your query is needlessly complicated and can be untangled to this 100 % equivalent one:

SELECT gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons

Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL, but I doubt that was your intention).

Possible misunderstanding

There is a common misunderstanding concerning output column names in the WHERE clause - where you can only reference input column names. So, geom in:

WHERE geom IN ...

references the original input column not the output column (the result of ST_MakeValid(geom)).


If your actual intention was to only keep rows where the result of ST_MakeValid(geom) is equal to any currently existing geom entry:

SELECT gid, id, ST_MakeValid(t.geom) AS geom 
FROM   schema.table_polygons t
   FROM   schema.table_polygons
   WHERE  geom = ST_MakeValid(t.geom)

I added a table alias and table qualification to clarify what's what.

The index you mention is only going to help with this variant.

If your actual intention was to merge duplicate rows after fixing geom values:

SELECT DISTINCT ON (ST_MakeValid(t.geom))
       gid, id, ST_MakeValid(geom) AS geom 
FROM   schema.table_polygons t
ORDER  BY ST_MakeValid(t.geom), gid, id;

You did not define how to break ties and which row to keep from a set of dupes. I keep the row with the smallest gid from each set. And the smallest id from remaining dupes. Details:

Aside: Calling a schema "schema" is like naming your son "son" and hoping for the best that no more sons shall arrive. In other words: don't.

  • If you want to remove duplicates, you have to be more specific about which rows to keep exactly. – Erwin Brandstetter Jan 28 '16 at 10:07
  • 4
    That's why "Sean" Connery named his son "Jason" and not just "Son" ;) – ypercubeᵀᴹ Jan 28 '16 at 10:20
  • 1
    @ErwinBrandstetter, now that is what I call a proper answer. The last query does what I wanted and it takes about 50 seconds to run through about 3 000 000 records. I have much to learn. Thanks – PyMapr Jan 29 '16 at 7:14

Your Answer

