Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I understand that the function Populate_Geometry_Columns() inserts records in the geometry_columns table for geometry columns that are not yet listed there. However, an invocation of Probe_Geometry_Columns() reports:

select probe_geometry_columns();
              probe_geometry_columns               
---------------------------------------------------
 probed:1086 inserted:0 conflicts:1086 stale:53612
(1 row)

This is after a call to Populate_Geometry_Columns(). Is there an easy/documented way how to get rid of the conflicts and the stale entries?

This has been posted a while ago to dba.sx with no replies: http://dba.stackexchange.com/q/14975/6934.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

There's not a function from within postgis to do what you're talking about. But you have three options that I see:

First, just truncate geometry_columns, then re-run SELECT probe_geometry_columns(). If that's too frightening, then:

Second, you can do this with a bit of introspection. This should work:

DELETE FROM geometry_columns g
  WHERE NOT EXISTS 
   (SELECT 1 from information_schema.columns as c
     JOIN geometry_columns g ON 
       (c.table_schema=g.f_table_schema
        AND c.table_name = g.f_table_name 
        AND c.column_name = g.f_geometry_column)
    )

As always, you should test that before actually doing anything, but probe_geometry_columns() should fix it if you screw it up too badly. I didn't test that code because I've already done:

Third, you can upgrade to PostGIS 2.x, where geometry_columns has become a view, and is thus always current – probe_geometry_columns() no longer exists, nor is it needed.

share|improve this answer
    
Good it's finally fixed in 2.0, and the workaround should do until then. –  krlmlr Nov 30 '12 at 20:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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