def setUpClass(cls): super(ProjectsProjectDAOTestCase, cls).setUpClass() metadata = MetaData() sources = {} sources['Src1'] = Table( 'Src1', metadata, Column('id', Integer, primary_key=True), GeometryExtensionColumn('geom', MultiPolygon(2)), ) GeometryDDL(sources['Src1']) sources['Src2'] = Table( 'Src2', metadata, Column('id', Integer, primary_key=True), Column('src1_id', Integer, ForeignKey('Src1.id')), Column('value', Float), ) cls.schema = { 'metadata': metadata, 'sources': sources, } cls.connection = cls.getConnection() cls.spatializeDB(cls.connection) cls.dao = ProjectDAO(cls.connection, cls.schema) metadata.create_all(bind=cls.connection) n = 10 for i in range(n): src1_record = { 'id': None, 'geom': WKTSpatialElement( dg.generate_multipolygon_wkt(x=i, y=i) ), } cls.dao.connection.execute(sources['Src1'].insert(values=src1_record)) for j in range(2): src2_record = { 'id': None, 'src1_id': i, 'value': i, } cls.dao.connection.execute(sources['Src2'].insert(values=src2_record))
def test_idx_performance(self): con = self.getConnection() self.spatializeDB(con) dao = ProjectDAO(con, self.schema) self.schema['metadata'].create_all(bind=con) n = 1e4 geom_step = 180.0/n trans = con.begin() for i in range(int(n)): src1_record = { 'id': None, 'geom': WKTSpatialElement( dg.generate_multipolygon_wkt(x=i*geom_step, dx=geom_step, y=i*geom_step, dy=geom_step) ), } dao.connection.execute(self.schema['sources']['Src1'].insert(values=src1_record)) for j in range(2): src2_record = { 'id': None, 'src1_id': i, 'value': i, } dao.connection.execute(self.schema['sources']['Src2'].insert(values=src2_record)) trans.commit() geom_entity = {'EXPRESSION': '__Src2__Src1__geom', 'ID': 'geom'} value_entity = {'EXPRESSION': 'func.sum(__Src2__value)', 'ID': 'value'} frame_entity = { 'EXPRESSION': 'func.BuildMbr(0,0,90,90)', 'ID': 'frame_', } inner_query = { "ID": 'inner', 'SELECT': [ geom_entity, value_entity ], "GROUP_BY": [ geom_entity, ] } query = { "ID": "outer", "SELECT" : [ {'EXPRESSION': '__inner__%s' % value_entity['ID']}, ], "WHERE": [ [{'TYPE': 'ENTITY', 'EXPRESSION': 'func.ST_Intersects(__inner__geom, func.BuildMbr(0,0,90,90))'}, '==', 1] ], "FROM": [{'ID': 'inner', 'SOURCE': inner_query}] } print "w/ index" for i in range(10): start_time = time.time() q = dao.get_spatialite_spatial_query(query, geom_entity, frame_entity) rows = dao.connection.execute(q).fetchall() end_time = time.time() elapsed = end_time - start_time print len(rows), elapsed print "w/o index" for i in range(10): start_time = time.time() q = dao.get_query(query) rows = dao.connection.execute(q).fetchall() end_time = time.time() elapsed = end_time - start_time print len(rows), elapsed