Forum Stats

  • 3,838,987 Users
  • 2,262,432 Discussions
  • 7,900,829 Comments

Discussions

the queries takes too much time

1005896
1005896 Member Posts: 7
edited May 16, 2013 1:00PM in SQL & PL/SQL
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

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    You would be better asking this question in a forum dedicated to spatial: 3078
  • Pleiadian
    Pleiadian Member Posts: 521 Silver Badge
    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
    1005896 Member Posts: 7
    I'm using 11g r2, how can I build the query different way without SDO_AGGR_UNION?
  • Pleiadian
    Pleiadian Member Posts: 521 Silver Badge
    edited May 16, 2013 11:03AM
    Maybe this thread can help you: 9395455

    The last poster suggests the use of SDO_AGGR_SET_UNION.
  • 1005896
    1005896 Member Posts: 7
    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
This discussion has been closed.