I am fairly new to Postgres (1995-96, and now again) and totally new to
GIS.
We're working with an inherited database in which we know there are
invalid geometries.
Rather than continue to let various queries fail, I'm working on a way
to detect the specific entries so we can go clean them up.
I've been working on a function that will test a geometry for validity
within the following functions:
I have the following test scenario to demonstrate my current challenge:
Ubuntu 10.04.02
Postgres 8.4.7
Postgis 1.5.2
GEOS 3.2.2 [I think]
-- Sample data
drop table testloc;
create table testloc (
id integer,
loc geometry
);
insert into testloc values(
1,ST_GeomFromEWKT('SRID=4326;MULTIPOLYGON(( (-180 -90, -180 90, 180 90,
180 -90, -180 -90), (-90 -45, -90 45, 90 45, 90 -45, -90 -45)), ( (-60
-30, -60 30, 60 30, 60 -30, -60 -30))) ')
);
insert into testloc values(
2,ST_GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-170 -80, -170 80, 170 80,
170 -80, -170 -80 ),( -80 -35, -80 35, 80 35, 80 -35, -80 -35 )), (( -50
-20, -50 20, 50 20, 50 -20, -50 -20 ))) ')
);
insert into testloc values(
3,ST_GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-165 -75, -165 75, 165 75,
165 -75, -165 -75 ),( -75 -30, -75 30, 75 30, 75 -30, -75 -30 )), (( -35
-15, -35 15, 35 15, 35 -15, -35 -15 ))) ')
);
-- Type for return list... this is good 'nuff for demonstration
purposes
DROP TYPE temprec ;
CREATE TYPE temprec AS
(
func_name text,
table_id integer,
reason text,
stat text
);
-- Function which takes in a Key and a Geometry.
-- Tests the Geometry using various functions [starting with IsValid,
but that isn't at issue for this problem].
-- Returns a list of id's and the error's that are captured by
operations against the geometry
drop FUNCTION testint (keyid integer, loc geometry) ;
CREATE OR REPLACE FUNCTION testint (keyid integer, loc geometry)
RETURNS temprec[] AS $f$
DECLARE
junk geometry;
rec temprec;
arr1 temprec[];
BEGIN
rec.func_name := NULL;
rec.table_id := keyid;
rec.reason := NULL;
rec.stat := '00000';
BEGIN
rec.func_name := 'ST_Intersects';
junk := ST_Intersects(loc, loc);
EXCEPTION
WHEN OTHERS THEN
rec.reason := sqlerrm;
rec.stat := SQLSTATE;
arr1 := arr1 || ARRAY[rec];
END;
RETURN arr1 ;
END;
$f$ LANGUAGE plpgsql IMMUTABLE;
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
