ST_IsValid
Indietro  7.6. Geometry Validation  Avanti

Nome

ST_IsValid — Tests if a geometry is well-formed in 2D.

Sinossi

boolean ST_IsValid(geometry g);

boolean ST_IsValid(geometry g, integer flags);

Descrizione

Tests if an ST_Geometry value is well-formed and valid in 2D according to the OGC rules. For geometries with 3 and 4 dimensions, the validity is still only tested in 2 dimensions. For geometries that are invalid, a PostgreSQL NOTICE is emitted providing details of why it is not valid.

For the version with the flags parameter, supported values are documented in ST_IsValidDetail This version does not print a NOTICE explaining invalidity.

For more information on the definition of geometry validity, refer to Sezione 4.4, «Geometry Validation»

[Nota]

SQL-MM defines the result of ST_IsValid(NULL) to be 0, while PostGIS returns NULL.

Eseguito dal modulo GEOS.

The version accepting flags is available starting with 2.0.0.

Questo metodo implementa le OGC Simple Features Implementation Specification for SQL 1.1.

Questo metodo implementa la specifica SQL/MM. SQL-MM 3: 5.1.9

[Nota]

Neither OGC-SFS nor SQL-MM specifications include a flag argument for ST_IsValid. The flag is a PostGIS extension.

Esempi

Code
SELECT ST_IsValid('LINESTRING(0 0,1 1)') As good_line,
ST_IsValid('POLYGON((0 0,1 1,1 2,1 1,0 0))') As bad_poly;
Output
NOTICE:  Self-intersection at or near point 0 0
 good_line | bad_poly
-----------+----------
 t         | f
Figure
Geometry figure for visual-st-isvalid-01

Find tables with invalid geometries in registered geometry columns.

Code
CREATE TEMP TABLE invalid_geometry_columns (
    table_name text,
    column_name text,
    invalid_count bigint
);

DO $$
DECLARE
    rec record;
    invalid_count bigint;
BEGIN
    FOR rec IN
        SELECT f_table_schema, f_table_name, f_geometry_column
        FROM geometry_columns
        ORDER BY f_table_schema, f_table_name, f_geometry_column
    LOOP
        EXECUTE format(
            'SELECT count(*) FROM %I.%I WHERE NOT ST_IsValid(%I, 0)',
            rec.f_table_schema,
            rec.f_table_name,
            rec.f_geometry_column
        ) INTO invalid_count;

        IF invalid_count 
> 0 THEN
            INSERT INTO invalid_geometry_columns
            VALUES (
                format('%I.%I', rec.f_table_schema, rec.f_table_name),
                rec.f_geometry_column,
                invalid_count
            );
        END IF;
    END LOOP;
END;
$$;

SELECT * FROM invalid_geometry_columns;