[OSGeo Africa] Oracle select query to select entities where the geometry only has one boundary

[Gavin Fleming]

Hi Mike

Oracle not being FOSS and with it using the SDO geometry type that I’m not familiar with, I can’t give you the Oracle SQL answer, but the query in PostGIS (which DRDLR should be using :-) ) would be

SELECT * FROM mytable 
WHERE ST_NumGeometries(geom) = 1 - - this will exclude multipolygons
	AND ST_NumInteriorRings(geom) = 0 - - this will exclude polygons with holes (interior rings)

Gavin

> On 26 Mar 2018, at 09:44, Mike Caister <Mike.Caister at drdlr.gov.za> wrote:
> 
> Hi,
>  
> I need help with structuring a SQL statement to query out data from an Oracle database, using SQL Developer. I want to query out entities that have only one boundary, as opposed to entities that have disjointed boundaries, or have islands.
>  
> I have tried to write queries that use the difference in structure of the SDO_GEOMETRY field, between entities with one boundary only, as opposed to entries with multiple boundaries or islands. Is this the route to follow, or is there a simpler method?
>  
> Typically, the SDO_GEOMETRY of entities that have multiple islands starts like this:
> MDSYS.SDO_GEOMETRY(2003,NULL,MDSYS.SDO_POINT_TYPE(NULL,NULL,NULL),MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,931,2003,1),MDSYS.SDO_ORDINATE_ARRAY( . . .,
> whereas SDO_GEOMETRY of entities that have no islands starts like this:
> MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY( . . .
>  
> Any suggestions would be welcome.
>  
>  
> Regards,
>  
> Mike Caister
>  
> Spatial Database Manager
> Survey Technician ST1397
> Office of the Surveyor General: KwaZulu-Natal
> 300 Pietermaritz Street
> PO Box 396
> Pietermaritzburg
> 3200
>  
> Email:    Mike.Caister at drdlr.gov.za <mailto:macaister at sgkzn.gov.za>
> Tel:      033 355 2929
> Fax:      033 394 7610
>  
>  
> _______________________________________________
> Africa mailing list
> Africa at lists.osgeo.org <mailto:Africa at lists.osgeo.org>
> You can UNSUBSCRIBE at https://lists.osgeo.org/mailman/listinfo/africa <https://lists.osgeo.org/mailman/listinfo/africa>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/africa/attachments/20180613/aef52d9f/attachment.html>