[postgis-users] isvalid(the_geom)

[Christo Du Preez]

Hi All,

I have used the isvalid(the_geom) in my queries but this makes a is very 
slow.  eg.

SELECT assvg(simplify(intersection(the_geom, 
geomfromtext('POLYGON((15.900000000000002 -35.0, 15.900000000000002 
-22.0, 33.3 -22.0, 33.3 -35.0, 15.900000000000002 -35.0))', -1)), 
0.025), 0, 6) AS data
FROM table
WHERE the_geom && geomfromtext('POLYGON((15.900000000000002 -35.0, 
15.900000000000002 -22.0, 33.3 -22.0, 33.3 -35.0, 15.900000000000002 
-35.0))',-1)
AND distance(geomfromtext('POINT(24.6 -28.5)',-1),the_geom) < 
10.860018416190647
AND isvalid(the_geom) = true

takes 4300ms

leaving out the 'AND isvalid(the_geom) = true' part

takes 2200ms

2200ms is still too slow but it's a big improvement already.

I thought of doing this:

ALTER TABLE table ADD COLUMN isvalid bool;

update table
set isvalid = isvalid(the_geom);

-- Index: table_btree_isvalid

-- DROP INDEX table_btree_isvalid;

CREATE INDEX table_btree_isvalid
  ON table
  USING btree
  (isvalid);

vacuum verbose analyze table

on all my tables and will then change my queries too rather include 'WHERE isvalid = true'

Is this advisable???

Any thoughts on this?

It is s bit of a worry just leaving out rows that is not valid, I suppose the issue should be resolved right from the start, to rather fix the data in the first place.

The problem is that I have no idea how to, this is public domain data supplied by the government.