[OSGeo Africa] Oracle select query to select entities where the geometry only has one boundary
gavinjfleming at gmail.com
Tue Jun 12 22:14:48 PDT 2018
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)
> On 26 Mar 2018, at 09:44, Mike Caister <Mike.Caister at drdlr.gov.za> wrote:
> 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.
> Mike Caister
> Spatial Database Manager
> Survey Technician ST1397
> Office of the Surveyor General: KwaZulu-Natal
> 300 Pietermaritz Street
> PO Box 396
> 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...
More information about the Africa