0

I am using hibernate spatial 4.0 with hibernate core 4.3, and we are using the oracle 11g with spatial extension.

This is the entity and dao:

@Entity
public class Pts{
    @Type(type = "org.hibernate.spatial.GeometryType")
    private Geometry Shape;
    ......
}


@Repository
public class PtsDao {
    private GeometryFactory gf = new GeometryFactory(new PrecisionModel(), 8307);


    @PersistenceContext
    private EntityManager em;

    @Transactional
    public void save(Pts Pts) {
        em.persist(Pts);
    }

    public List<Pts> query(double x, double y, double radius) {
        Geometry point = gf.createPoint(new Coordinate(x, y));
        Query query = em.createQuery("select b from Pts b where distance(b.Shape,:filter) < :distance", Pts.class);
        query.setParameter("filter", point);
        query.setParameter("distance", radius);
        return query.getResultList();
    }
}

The test:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"/spring/application-config.xml"})
public class PtsTest {
    private GeometryFactory gf = new GeometryFactory(new PrecisionModel(), 8307);

    @Autowired
    private PtsDao dao;

    @Before
    public void setup() {
        Pts b1 = new Pts();
        b1.setShape(gf.createPoint(new Coordinate(21.401829098, 29.0819022380)));
        dao.save(b1);
        ...
        //save only five points to the db
    }
    @Test
    public void testQuery() {
        long st = System.currentTimeMillis();
        List<Pts> re = dao.query(20.82123, 30.12342, 10000);
        System.out.println("-------test distance get result " + re.size() + " " + (System.currentTimeMillis() - st));
    }
}

I found that the query will take almost 400 million seconds, I think this is unacceptable for only five points.

I think this maybe caused by the index, so I create index in oracle manually:

CREATE INDEX pts_sidx ON pts(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX

However once I run the test again, it still cost 400+ ms.

What's going on?


Since we are using the ORM framework, so the table are generated by hibernate, and this is what we got in the pl/sql developer:

-- Create table

create table PTS
(
  objectid   NUMBER(10) not null,
  address    VARCHAR2(255 CHAR),
  shape      SDO_GEOMETRY,
)
alter table PTS
  add primary key (OBJECTID)
  using index 
  tablespace SPRING
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

And when we insert data to the database, we save nothing except the objectid(generated) and the geometry, all other column are empty.

And we do not create any query plan in the database.

When I tried to query use the native sql:

 select objectid from pts p where mdsys.sdo_geom.sdo_distance(
       p.shape,
       sdo_geometry(2001, 8307, null, sdo_elem_info_array(1,1,1), 
          sdo_ordinate_array(20.82123, 30.12342)),
       0.5
) <10000 ;

It cost me 300+ ms.

  • There's a large difference between 400 milliseconds and 400 million seconds. The former doesn't sound unreasonable for a first query on a cold database. – Vince Jan 29 '15 at 0:34
  • While when we do the same query by the same criteria, it cost 120+ milliseconds once we use the arcgis engine with FileGeodatabase. We have almost 10K+ points in the table, and the response time can not longer than 1 seconds. – giser Jan 29 '15 at 1:14
  • 1
    File geodatabase will always be faster than a relational database. I was able to get ST_GEOMETRY mean query time for random global point in country under 6ms over 1,000,000 queries; getting the same out of SDO doesn't seem unreasonable. You probably need to provide more detail on what is in the database, and what the query plan looks like. – Vince Jan 29 '15 at 1:44
  • I updated, if further information is required, please let me know. – giser Jan 29 '15 at 2:31
  • A query plan is something generated by the database to implement a query, not something over which you have any control. Often the optimizer will realize that it's not worth the cost of driving a query on a trivial table through an index, and will fall back to a full table scan, so placing a trivial amount of data in the database is providing false feedback on a more difficult query, though in this case you've organized your search so it must perform a full table scan. There are a number of answers here in GSE which address this issue (albeit usually in PostgreSQL). – Vince Jan 29 '15 at 2:55
2

Your query, as written, forces a full table scan, since an expensive distance calculation must be performed on every row to determine which rows meet the constraint clause. If you had used the SDO_GEOM.WITHIN_DISTANCE operator, then an index could be consulted.

There seem to be a number of possible uses, but something like this might utilize the index:

 SELECT  objectid 
 FROM    pts p 
 WHERE   mdsys.sdo_geom.within_distance(
             p.shape,
             sdo_geometry(2001, 8307, null, sdo_elem_info_array(1,1,1), 
                 sdo_ordinate_array(20.82123, 30.12342)), 
             'distance=10 UNIT=KM') = 'TRUE';

I expect there are a number of optimizations you can perform, and I might not have the parameter order right, and the query itself will be calculated with spherical geometry, not spheroidal, but those can be left as an exercise.

I would caution you that latitude is Y, so if you didn't intend to generate a query circle in Libya (vice Sudan), then you'll need to flip the order of coordinates in your constructors.

  • Thanks, you are right. I change the search function, and it cost shorter. – giser Jan 29 '15 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.