the queries takes too much time

1005896

Hi,
I want to compare time of queries between Spatial and PostGIS, but Spatial take too much time. I created spatial indexes in both databases so I have no idea where can be the problem :-(. I'm actually student and I try this for my essay, but I start with Oracle by myself so I'll appreciate some help.

I have table with cca 7000 polygons and I want to create new table like dissolve these polygons by attribute katuze.

Query in Spatial takes 8506 s :

CREATE TABLE dp_diss_p AS
SELECT KATUZE_KOD, SDO_AGGR_UNION(
MDSYS.SDOAGGRTYPE(a.geom, 0.005))GEOM
FROM dp_plochy a GROUP BY a.KATUZE_KOD;

Query in PostGIS takes 10.149 s :

CREATE TABLE plochy_diss AS
SELECT st_union(geom) AS geom
FROM plochy
GROUP BY katuze_kod;

I really don't know what to do, please give somebody me some advice :-(. Please excuse my English.

Thx Eva

Paul Horth

You would be better asking this question in a forum dedicated to spatial: 3078

Pleiadian

What Oracle version are you using?

Personally, I have very bad experiences with using SDO_AGGR_UNION on larger recordsets on 10gR2. It is very slow

1005896

I'm using 11g r2, how can I build the query different way without SDO_AGGR_UNION?

Pleiadian

Maybe this thread can help you: 9395455

The last poster suggests the use of SDO_AGGR_SET_UNION.

1005896

I tryed use the sdo_aggr_set_union like this:

CREATE OR REPLACE FUNCTION get_geom_set (table_name VARCHAR2,
column_name VARCHAR2,
predicate VARCHAR2 := NULL)
RETURN SDO_GEOMETRY_ARRAY DETERMINISTIC AS

type cursor_type is REF CURSOR;
query_crs cursor_type ;
g SDO_GEOMETRY;
GeometryArr SDO_GEOMETRY_ARRAY;
where_clause VARCHAR2(2000);
BEGIN
IF predicate IS NULL
THEN
where_clause := NULL;
ELSE
where_clause := ' WHERE ';
END IF;

GeometryArr := SDO_GEOMETRY_ARRAY();
OPEN query_crs FOR ' SELECT ' || column_name ||
' FROM ' || table_name ||
where_clause || predicate;
LOOP
FETCH query_crs into g;
EXIT when query_crs%NOTFOUND ;
GeometryArr.extend;
GeometryArr(GeometryArr.count) := g;
END LOOP;
RETURN GeometryArr;
END;

CREATE TABLE dp_diss_p AS
SELECT KATUZE_KOD, sdo_aggr_set_union (get_geom_set ('dp_plochy', 'geom','CTVUK_KOD <>''1'''), .0005 ) geom
FROM dp_plochy a GROUP BY a.KATUZE_KOD;

It takes 21.721 s, but it return type of collection and I can't export it to *.shp because Georaptor export it like 'unknown' . Has anybody idea what can I do with it to export it like polygon?

Eva