[postgis-users] ST_Union (8.4) and TopologyException

[Simon Greener]

Brent,

Thanks for taking the time to answer my problem. 

I too have lots of client tools that can fix the data as well but what I am after is a solution based entirely on PostGIS. 

You see all (2500) the polygons in the inner table (see query below - outer table has only 2 polygons) pass 
validation both by FME and by PostGIS yet this occurs on union which seems to indicate that the problem 
is less related to the actual source data but occurs as the union is being built from the input data. 

create table simon.si_result 
as 
select i.si_grouping_f_id, ST_Difference(ST_Transform(s.geom,96687),i.geom) as geom, s.geom As s_geom, i.geom As i_geom
  from simon.si_grouping_f /* 2 polygons */ s
       inner join 
       (select si_grouping_f_id, ST_Union(ST_Transform(geom,96687)) as geom
          from simon.si_grouping_f_store_f /* 2500 polygons */ a
         where geom is not null and st_isvalid(geom) = true
         group by si_grouping_f_id
        ) as i
        on (s.id = i.si_grouping_f_id);

I have had to use ST_Transform as I have found the query even more problematic when run against the original geodetic data.

I suspect it is the underlying algorithms being used that are causing the problem as the same data can be successfully  
unioned using my principle desktop GIS tool Manifold GIS via the following query in about 10 seconds.

INSERT INTO [SI_GROUPING_F_SUBTRACT_RESULT] ([ID 2],[Geom (I)])
SELECT [A].[ID 2], ClipSubtract([A].[Geom (I)], [B].[clipGeom] ) 
  FROM [SI_GROUPING_F_SUBTRACT] as [A]
       INNER JOIN
       ( SELECT [SI_GROUPING_F_ID],UnionALL([Geom (I)]) as clipGeom
           FROM [SI_GROUPING_F_STORE_F_NORMALISED]
          GROUP BY [SI_GROUPING_F_ID]) as [B]
       ON ([A].[ID 2] = [B].[SI_GROUPING_F_ID] );
 
Running [DifferenceQuery]: 13.242 sec
[DifferenceQuery]: 2 row(s) affected.


regards
Simon

On Wed, 05 Aug 2009 09:56:04 +1000, <pcreso at pcreso.com> wrote:

>
> Hi Simon,
>
> I've found the simplest way, if you don't have too many such instances, is to open the offending table in QGIS, zoom into the specified location, identify & fix the problem manually.
>
> There are a number of possible topological errors which can occur, & this way allows a choice of solution, usually move or delete a point or two, or a hole...
>
> You can do similar things with OpenJUMP & other FOSS GIS tools as well I believe.
>
> Cheers,
>
>   Brent Wood
>
>
> --- On Wed, 8/5/09, Simon Greener <simon at spatialdbadvisor.com> wrote:
>
>> From: Simon Greener <simon at spatialdbadvisor.com>
>> Subject: [postgis-users] ST_Union (8.4) and TopologyException
>> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> Date: Wednesday, August 5, 2009, 11:51 AM
>> I have been trying out the new
>> ST_Union in 1.4 and have a question.
>>
>> I note that, whenever I hit an error like this:
>>
>> NOTICE:  TopologyException: found non-noded
>> intersection between 5.74214e+006 7.36573e+006, 5.74304e+006
>> 7.36666e+006 and 5.74339e+006 7.36629e+006, 5.74304e+006
>> 7.36666e+006 5.74304e+006 7.36666e+006
>>
>> ST_Union fails to produce a result.
>>
>> Is there anything I can do to address this
>> TopologyException issue within PostGIS.
>>
>> regards
>> Simon
>> --
>> SpatialDB Advice and Design, Solutions Architecture and
>> Programming,
>> Oracle Database 10g Administrator Certified Associate;
>> Oracle Database 10g SQL Certified Professional
>> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE,
>> Manifold GIS, FME, Radius Topology and Studio Specialist.
>> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania,
>> Australia.
>> Website: www.spatialdbadvisor.com
>>   Email: simon at spatialdbadvisor.com
>>   Voice: +61 362 396397
>> Mobile: +61 418 396391
>> Skype: sggreener
>> Longitude: 147.20515 (147° 12' 18" E)
>> Latitude: -43.01530 (43° 00' 55" S)
>> NAC:W80CK 7SWP3
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> 



-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
  Email: simon at spatialdbadvisor.com
  Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3