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

[Mike Caister]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/africa/attachments/20180326/fd3ac6d3/attachment.html>