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, <pcr...@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 <si...@spatialdbadvisor.com> wrote:
>
>> From: Simon Greener <si...@spatialdbadvisor.com>
>> Subject: [postgis-users] ST_Union (8.4) and TopologyException
>> To: "PostGIS Users Discussion" <postgis-users@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: si...@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@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: si...@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
