spatialite_indices

spatialite_indices.py

    def test_foo(self):

        limit = 1000
        
        expected_counts = {}
        if True:
            start_time = time()
            for i in range(1, limit):
                print i
                sql = """
                SELECT count(habitat_1.id)
                FROM habitat AS habitat_1, cell AS cell_1 
                WHERE Intersects(habitat_1.geom, cell_1.geom) = 1 AND cell_1.id = %d
                """ % i
                expected_counts[i] = db.session.execute(sql).fetchone()[0]

            end_time = time()
            print "TIME: ", end_time - start_time

        if False:
            counts = {}
            start_time = time()
            for i in range(1, limit):
                sql = """
                SELECT count(habitat_1.id)
                FROM habitat AS habitat_1, cell AS cell_1 
                WHERE Intersects(habitat_1.geom, cell_1.geom) = 1 AND cell_1.id = %d
                AND habitat_1.ROWID IN (
                SELECT pkid FROM idx_habitat_geom
                WHERE xmin > MbrMinX(cell_1.geom) AND
                xmax < MbrMaxX(cell_1.geom) AND
                ymin > MbrMinY(cell_1.geom) AND
                ymax < MbrMaxY(cell_1.geom)
                )
                """ % i
                counts[i] = db.session.execute(sql).fetchone()[0]
            self.assertEquals(counts, expected_counts)
            end_time = time()
            print "TIME: ", end_time - start_time

        if True:
            counts = {}
            start_time = time()
            for i in range(1, limit):
                sql = """
                SELECT count(habitat_1.id)
                FROM habitat AS habitat_1, cell AS cell_1 
                WHERE Intersects(habitat_1.geom, cell_1.geom) = 1 AND cell_1.id = %d
                AND habitat_1.ROWID IN (
                SELECT ROWID FROM SpatialIndex
                WHERE f_table_name='habitat'
                AND search_frame=cell_1.geom
                )
                """ % i
                counts[i] = db.session.execute(sql).fetchone()[0]
            self.assertEquals(counts, expected_counts)
            end_time = time()
            print "TIME: ", end_time - start_time

if __name__ == '__main__':
    unittest.main()