The biggest practical difference between BSP-Trees and other kinds of 3d-trees are that BSP-Trees can be more optimal but only work on static geometry. This is because BSP-Trees are generally very slow to build, often taking hours or days for a typical static urban game level.
The two main reasons BSP-Trees take longer to build are (a) they use non-axis-aligned splitting planes, which take longer to optimally find, and (b) they subdivide geometry on axis boundaries, assuring no objects cross split planes.
Other types of 3d-trees (Octrees, Quadtrees, kd-tree, Bounding-Volume-Hierarchy) use axis-aligned bounding volumes, and volumes are (optionally) allowed to overlap, so contained objects don't need to be cut on volume boundaries. These both make the trees less optimal than BSP-trees, but quicker to build, and easier to change for dynamic objects.
Extrapolating these factors into situations...
Outdoor areas typically use height-field based ground representations, either simple heightmaps or more complex geo-mip-mapping techniques like ROAM. The ground itself doesn't participate in the 3d space partitioning, only objects placed on the ground.
Worlds with lots of instances of simpler and similar geometry (houses, trees, asteroids, etc) will often use a non-BSP-tree (such as a BVH), because putting the geometry into a BSP-tree would mean duplicating and splitting the detail geometry for every instance.
Conversely, a large custom static mesh with no instancing, such as an urban scene, or a complex indoor environment, will typically use a BSP-Tree for improved runtime performance. The fact that the BSP-Tree splits geometry on node-boundaries is helpful for rendering performance, because the BSP nodes can be used as pre-organized triangle rendering batches. The BSP-Tree can also be optimized for occlusion, avoiding the need to draw portions of the BSP-Tree which are known to be behind other geometry.
See also: Octree vs BVH (archived from original), Bounding Volume Hierarchy Tutorial, BSP Tutorial.