I ran into a problem with Australian census compilations presented in MapInfo format by the Australian Bureau of Statistics . I load them into the PostGIS database using the ogr2ogr tool , which works for most shapes, but not for all.
A simple example of the problem I encountered is a request like this (loading NSW dataset required):
SELECT st_union(wkb_geometry) FROM cd06answ WHERE cd_code_2006 LIKE '1291%'
The result of this query is not expected, but NULL.
There are no null values in the table, but there are invalid geometries. for instance
SELECT cd_code_2006 FROM cd06answ
WHERE cd_code_2006 LIKE '1291%' AND NOT st_isvalid(wkb_geometry)
retrieves the values "1291301" and "1291321". If invalid geometries are excluded, st_union succeeds.
Connecting Quantum GIS to the database allows you to display both the figures in question. They should be part of a geometric union, so I need to fix the problem somehow.
Are there any ways to load MapInfo data into PostGIS? Or some ways of fixing data inside PostGIS? Since the database data is displayed normally, it can be saved, right?
EDIT: Based on Christophe reviews, I experimented with st_buffer and st_snaptogrid several times. The result of this query:
SELECT
cd_code_2006,
st_isvalid(st_buffer(wkb_geometry,0)),
st_isvalid(st_snaptogrid(wkb_geometry, 0.00000001)),
st_isvalid(st_snaptogrid(wkb_geometry, 0.0000001))
FROM
cd06answ
WHERE
cd_code_2006 LIKE '1291%'
AND
NOT st_isvalid(wkb_geometry)
For the two geometries involved, the first and last of the three st_isvalids are true, and the average is not.
Unfortunately, none of the approaches establishes a union, but only
SELECT st_union(st_buffer(wkb_geometry,0.4)) FROM cd06answ
WHERE cd_code_2006 LIKE '1291%'
leads to geometry but
SELECT st_union(st_buffer(wkb_geometry,0.3)) FROM cd06answ
WHERE cd_code_2006 LIKE '1291%'
no (I used to try to use a small buffer trick, but did not push it to this level).
.