[postgis-users] newbie question about non closed rings

[Simon Greener]

>> I think that if both Oracle Spatial and MS-SQL allow geometries to be
>> stored with deformed features (i.e. incomplete rings) then I could be
>> persuaded that the ERROR upon insertion should be downgraded to a
>> WARNING instead. Would anyone like to check this behaviour and report
> back?
>
> Mark -- I can check on SQL Server and see.  I have Oracle XE installed
> too so I guess once I figure out how to use it I can check on that too.
> Unless if someone knows off hand.

SQL Server will store a bad geometry so you have to use MakeValid as follows

UPDATE dbo.multi
   SET SHAPE = SHAPE.MakeValid()
  WHERE SHAPE.STIsValid() = 0;

The same is true for Oracle.

UPDATE multi m
   SET m.shape = mdsys.sdo_util.rectify_geometry(m.shape,0.005)
 WHERE mdsys.sdo_geom.validate_geometry(m.shape,0.005) <> 'TRUE';

> HOWEVER: I don't really care if those allow bad geometries or not,
> because at least comparatively I have found PostGIS has more cleaning
> functions than SQL Server and I imagine if you are using Oracle XE your
> options are limited anyway so you may want to use PostGIS to clean your
> geometries before you stuff them in Oracle.  So it makes more sense for
> PostGIS to allow these things in since you have a better chance of
> fixing them in PostGIS :)

Quite true about PostGIS having more functions than Oracle. But if I had to use a second tool other than Oracle
for this I would probably use my GIS (or FME) in preference. What tools are available in the open source world
for replicating/pushing geometries between PostGIS and other spatial databases.

Also, I keep meaning to make a list of JTS functions that do data cleaning so that I can recast them for use inside the Oracle JVM (I've already done this for other aspects of GeoTools/JTS).

> We should focus more on outputting OGC compliant geometry constructs and
> less on preventing people from bringing in invalid things.  It makes it
> difficult for people coming from older versions of PostGIS to upgrade
> that were more liberal if we don't give that option in some way. As Paul
> likes to say "Be liberal with what you accept and conservative with what
> you emit", but of course we don't want to go too far.

After having managed Oracle Locator/Spatial databases for over 10 years I have to admit that I prefer PostGIS's automatic checking of the validity of the data before it can be stored: it certainly would have saved me, and the organisations I have worked for, a lot of time building tools to check data quality. (One thing that annoyed me about ArcSDE on Oracle Spatial was that it validated every geometry it read from the database for every query it executed - thus, in  any one day it could check the same geometry hundreds of times - and one could not turn off that checking!)

Before anything in PostGIS is changed to degrade this aspect of the system perhaps someone ought to conduct a survey to ask if anything should be changed and, if a respondant votes for a degrading of the quality checking function, specifically ask what business requirement demands that such a degrading takes place.

Or perhaps my perspective on this is skewed by my view that a database is about data management for businesses/departments and less a tool for GIS practitioners.

Surely, without changing anything, one can use PostGIS as a tool for data cleaning. For example, why not load the data as WKT into a text column and then update a geometry column after processing the text data to ensure it is valid?

Sorry for the ramble...

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: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3