How to make sure that geometries copy the relationship of corresponding tables?

Question

A make a database in PostgreSQL (9.2.2)/PostGIS (2.0.1). I'm new to it, so I'm not sure how to make sure that geometries corresponding to main table (polygons or points) cover the geometries corresponding to the dependent table (always points).

In ArcGIS, I would simply apply a topology rule, so I tried to learn more about topologies in PostGIS, but what I found only confused me. Now I don't know how to do it through topology and I'm even not sure whether this way is a good solution, since most PostGIS topology application focus on other topology rules than I need.

Another alternative I found was a SELECT like this:

SELECT gid, the_geom FROM foo_geometry WHERE ST_CoveredBy(foo_geometry, bar_geometry)='f'

where foo_geometry corresponds to the dependent table and bar_geometry to the other one. I can also use a cursor for this.

What solution is the best provided that
a) as a beginner I prefer solutions easy to understand and implement
b) there won't be many geometries and the validation will be done just once in a time as a maintenance process, so performance is not crucial here
?

Answer 1

You seem to already have one answer: regularly run a summary query that looks for points that fail to meet your quality rule.

Another answer would be a post-commit trigger on your points table that runs a function that check to see if the point being inserted falls within one of your polygons: if it does not, raise an error to roll back the transaction.

The first solution is simpler but requires something outside the database to run the quality rule regularly. It also means that there will be times when invalid data is in the database. The second solution is inside the database and ensures that bad data will never enter, but will require any application that inserts data has to intelligently deal with the case when an insert fails due to an integrity trigger.