[GIS] Line to polygon in Oracle Spatial

linestringoracle-spatialpolygonsql

I have a table that contains buildings in a line geometry where a line starts and ends at the same point. Is there away to convert lines to polygons in Oracle Spatial?

Best Answer

This solution works for linestrings with not too much vertices (which should be the case for most buildings):

CREATE OR REPLACE FUNCTION line_to_polygon (closedLineGeom SDO_GEOMETRY) 
RETURN SDO_GEOMETRY
AS
    wktStringLine VARCHAR2(4000);
    wktStringPolygon VARCHAR2(4000);
BEGIN
    wktStringLine := SDO_UTIL.TO_WKTGEOMETRY(closedLineGeom);
    wktStringPolygon := replace(wktStringLine, 'LINESTRING (', 'POLYGON ((');
    wktStringPolygon := wktStringPolygon || ')';
    RETURN SDO_UTIL.FROM_WKTGEOMETRY(wktStringPolygon);
END;

The function just replaces the linestring WKT (SDO_UTIL.FROM_WKTGEOMETRY) with polygon WKT and returns it as a geometry (SDO_UTIL.TO_WKTGEOMETRY). Note that this function will break for linestrings with a lot of vertices because the length of a VARCHAR2 is limited to 4000. Also you have to make sure that the line is really closed!

UPDATE - Better solution

The following is a better solution that does not have the previous limitation:

UPDATE your_table_name x
SET 
x.geometry_column_name.SDO_GTYPE = 2003,
x.geometry_column_name.SDO_ELEM_INFO = SDO_ELEM_INFO_ARRAY(1,1003,1);

Of course you still have to make sure that your linestrings are really closed!

Related Question