local_repository / new_local_repository should support paths relative to the current module

### Description of the feature request:

Support a way to let `local_repository` or `new_local_repository` to specify paths relative to the current MODULE.bazel, not the root of the workspace.

### Which category does this issue belong to?

External Dependency

### What underlying problem are you trying to solve with this feature?

When paths in `local_repository(path=)` and `new_local_repository(path=)` are relative, they are relative to the workspace:

https://bazel.build/rules/lib/repo/local

This doesn't make sense in bzlmod. If a module uses local_repository / new_local_repository rules in its MODULE.bazel, it can no longer be used by other modules; the `path`s are now interpreted against the root module.

**Example / Repro**

```
# mymod/MODULE.bazel
bazel_dep(name = "mydep")
local_path_override(module_name = "mydep", path = "mydep")
```
```
# mymod/mydep/MODULE.bazel
module(name = "mydep")

local_repository = use_repo_rule("@bazel_tools//tools/build_defs/repo:local.bzl", "local_repository")
local_repository(
name = "myrepo",
path = "myrepo",
)
```
```
# mymod/mydep/BUILD.bazel
alias(name = "alias", actual = "@myrepo")
```
```
# mymod/mydep/myrepo/BUILD.bazel
filegroup(name = "myrepo")
```

Then build:

```
cd mymod
bazel build @mydep//:alias
```

This gives the error:
```
Error in fail: The repository's path is "myrepo" (absolute: "/myrepo") but it does not exist or is not a directory.
ERROR: no such package '@@mydep++_repo_rules+myrepo//': The repository's path is "myrepo" (absolute: "/myrepo") but it does not exist or is not a directory.
```

This is problematic because `mydep` should be a standalone module. However, because of the `local_repository` in `mydep/MODULE.bazel`, `@mydep` can no longer be used by any module.

**Workarounds**
1. In this example, `mydep` is a `local_path_override` so we could create a symlink. However, this doesn't work if `mydep` is downloaded from the Internet.
2. The owner of `mydep` could write its own implementation of `local_path_repository()` to interpret paths relative to the module root, not the workspace root. Example: https://cs.android.com/android/kernel/superproject/+/common-android-mainline:build/kernel/kleaf/impl/local_repository.bzl . However, this only works if I OWN `mydep`. If I am the author of `mymod` that uses `mydep` from somewhere else, this doesn't work.
3. To achieve 2 when we don't own `mydep`, we could use a `git_override()` with a `patch` argument that changes the `local_repository = ...` statement.

**Real life example**
I discovered this issue when trying to use google/rootcanal ( https://github.com/google/rootcanal/blob/0a1741ab74af21b1cb2099c5f4c985919f5aa846/MODULE.bazel#L17 ).

### Which operating system are you running Bazel on?

8.3.1

### What is the output of `bazel info release`?

_No response_

### If `bazel info release` returns `development version` or `(@non-git)`, tell us how you built Bazel.

_No response_

### What's the output of `git remote get-url origin; git rev-parse HEAD` ?

```text

```

### Have you found anything relevant by searching the web?

_No response_

### Any other information, logs, or outputs that you want to share?

_No response_

Start by reproducing the example with mymod/MODULE.bazel, mydep/MODULE.bazel, and the local_repository call from the issue, then inspect the local_repository and new_local_repository entry points. Done means relative paths are interpreted from the module's MODULE.bazel rather than the workspace root, and bazel build @mydep//:alias succeeds.

Written by the indexing model from the issue text.