Repository: geopandas/geopandas

Issue:
Spatial join loses index names of input
Small example:

```
In [15]: countries = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))

In [16]: cities = geopandas.read_file(geopandas.datasets.get_path('naturalearth_cities'))

In [17]: countries = countries.rename(columns={'name': 'country_name'}).set_index('country_name')

In [21]: cities.index.name = 'id'

In [22]: geopandas.sjoin(cities, countries, op='within').head()
Out[22]: 
             name                                     geometry index_right     pop_est continent iso_a3  gdp_md_est
0    Vatican City  POINT (12.45338654497177 41.90328217996012)       Italy  58126212.0    Europe    ITA   1823000.0
1      San Marino    POINT (12.44177015780014 43.936095834768)       Italy  58126212.0    Europe    ITA   1823000.0
192          Rome    POINT (12.481312562874 41.89790148509894)       Italy  58126212.0    Europe    ITA   1823000.0
2           Vaduz  POINT (9.516669472907267 47.13372377429357)     Austria   8210281.0    Europe    AUT    329500.0
184        Vienna  POINT (16.36469309674374 48.20196113681686)     Austria   8210281.0    Europe    AUT    329500.0
```

So both the index name of the left dataframe is lost, as the index name of the right dataframe (which has become the generic 'index_right' column)

Discussion:
martinfleis: I think it makes sense to preserve it. What happens if the right index name is equal to an existing left column name? 
jorisvandenbossche: > What happens if the right index name is equal to an existing left column name?

Then it will get the suffix added, I think (will check)
jorisvandenbossche: It's good that I tried to add a test for the case you mentioned, as that catched some corner cases.
martinfleis: If you have duplicated column names, both of them get a suffix. But if you have a duplicated column name and an index name, only the column name gets a suffix, not the index name. 

Is that what we want? It brings a bit of inconsistency into the behaviour of a suffix. I am not sure here, both options have pros and cons.
jorisvandenbossche: Hmm, that's true. I did it that way because it's what we already do for the left index / right column name case. On master:

```
In [1]: from shapely.geometry import Point

In [2]:         geoms = [Point(1, 1), Point(2, 2)]
   ...:         df1 = GeoDataFrame(
   ...:             {
   ...:                 "myidx": [1, 2],
   ...:                 "geometry": geoms,
   ...:             }
   ...:         )
   ...:         df2 = GeoDataFrame(
   ...:             {"geometry": geoms}, index=pd.Index(["a", "b"], name="myidx")
   ...:         )

In [3]: geopandas.sjoin(df2, df1)
Out[3]: 
                      geometry  index_right  myidx
myidx                                             
a      POINT (1.00000 1.00000)            0      1
b      POINT (2.00000 2.00000)            1      2
```

Although this actually even doesn't add the suffix for the column as well ..

So on this PR we get:

```
In [3]: geopandas.sjoin(df2, df1)
Out[3]: 
                      geometry  index_right  myidx_right
myidx                                                   
a      POINT (1.00000 1.00000)            0            1
b      POINT (2.00000 2.00000)            1            2
```

Not really sure what's the best .. (no suffix at all, only added to the column, or added to both index and column)


martinfleis: I am fine treating index differently, I just wanted to raise that.

We can say that columns are renamed but index stays the same because it is arguably more important than columns are.
jorisvandenbossche: On the other hand, it's also strange to have a result with a suffix applied to the right label, but not to the left. While in practice you would expect if there are suffixes added, there is both a "_left" and "_right" version?
martinfleis: I tried to check how pandas does this but it appears that it just discards the index (not ideal I guess).

```py
df1 = pd.DataFrame({'key': ['foo', 'bar', 'baz', 'loo']},
                    index=pd.Index([1, 2, 3, 4], name="value"))
df2 = pd.DataFrame({'key': ['foo', 'bar', 'baz', 'loo'],
                    'value': [5, 6, 7, 8]})
df1.merge(df2, on="key")

   key  value
0  foo      5
1  bar      6
2  baz      7
3  loo      8
```

> While in practice you would expect if there are suffixes added, there is both a "_left" and "_right" version?

When talking about columns yes. With the index, I can imagine that different people would have different expectations. I personally don't have a preference here and both options _make sense_ to me, in a way that I find a good reason why it works like that. 

If you have a preference for one over the other, go for it.
jorisvandenbossche: Testing what it would give to always add suffixes to the index, also if it is set back as the index of the result, I ran into some more corner cases with MultiIndexes that actually also with the current PR causes errors. So let's just leave this for the next release, then we have a bit more time to fix this up.
jorisvandenbossche: OK, I revived this PR and I ended up doing a full rewrite of the index-handling logic.

It is still quite some custom logic, but I think it should more or less follow our current behaviour but more consistently for left vs right join:

- To generally preserve the index columns, we reset the index before the join operation
- If the `reset_index` operation results in auto-generated names ("index", "level_0", etc), we always add a suffix to them even if there were no overlapping labels (so that you eg get "index_right" for the index column of the right frame, and not just "index" if the left dataframe did have an index with a custom name)
  - Previously, for the right dataframe, we always used auto-generated names. While now I use the resulting names from `reset_index`, which will preserve the index name if it had one, and only auto-generate one if the name was `None`
- Add suffixes to left and right column names if there are overlapping labels (treating the index column names the same)  
  - This will now also add left/right suffixes to the index labels as well if needed (see example at [comment above](https://github.com/geopandas/geopandas/pull/2144#issuecomment-931387224)), while before it would ignore those in the logic to add suffixes. 
- Restore the index at the end: set back those columns as index that were index columns in the left (left/inner join) or right (right join) dataframe. And we also restore the fact that they didn't have a name (but only in that case, and if an index did have a name originally, and a suffix was added to avoid duplicate names, we set back the index with this new name)


This adds quite some code to sjoin.py. Partly this is to keep some of the corner cases working, but mostly also because we now do the "suffix-adding logic" ourselves, instead of letting pandas' `merge` do this (this is the new `_process_column_names_with_suffix` that was added, which is mainly copied from pandas.merge). 
In a follow-up PR, I am going to replace the multiple `merge` calls with a single concat (for performance), and then we needed to handle this suffix logic ourselves anyway. So it made sense to already do this here while refactoring this part of the implementation.
jorisvandenbossche: So in summary, the main "bug fixes" / changes in behaviour are:

- Preserve the index name of the right dataframe, it it had one (so not uses "index_right" in that case)
- Added suffixes to index names as well, if they were overlapping with the index name or a column name of the other dataframe
m-richards: Just a note on having the custom indexing code, it seems contrary to the consensus in https://github.com/geopandas/geopandas/issues/498 where there is an arugment to reduce complexity in sjoin by making a user explicitly preserve an index in the same way as they would for pd.merge. But that has been open for a long time, I guess with the difficulties of backwards incompatibility. [I guess technically also they're not mutually exclusive - especially given that issue has not progressed, so fixing things in the short term makes sense.]
jorisvandenbossche: > Just a note on having the custom indexing code, it seems contrary to the consensus in #498 where there is an arugment to reduce complexity in sjoin by making a user explicitly preserve an index in the same way as they would for pd.merge. But that has been open for a long time, I guess with the difficulties of backwards incompatibility.

Yeah, I have been going back and forth on this while working on this PR. It would indeed avoid a big part of the complexity I am adding here. 
I am only not sure about the backwards compatibility issue. If you rely on eg an "index_right" column being present, this will break your code (but actually, this PR will also do that ...).

I was also thinking of yet another option: only preserve the index if has a name (so do a `reset_index()` to preserve the index as a column _if_ it has a name (so in the case we don't have to invent a name like "index_right")). We have a somewhat similar logic in `to_file`, where we also (by default) write the index to the file only if it is a non-integer index or has a name. 
m-richards: > break your code (but actually, this PR will also do that ...).

That's actually why I originally referenced the other issue, but thought this is in a sense a bug fix, even if it is breaking. Still, it probably makes sense to get the breaking change right.

> only preserve the index if has a name 

I personally like this, I don't often find myself wanting the index_right column, but it's another hard break. The only way I can see around this is pretty messy, add a keyword, along the lines of `keep_right_index`, which can be `None`, `"named"` or `"always"`, defaulting to None, where none is an alias for "always" that warns the default behaviour will change from `"always"` to `"named"` in a future release. I don't really like this to be honest, I'd prefer a clean break or to live with the current behaviour than drag it out over two releases.

martinfleis: It seems that we'll need #3180 first
martinfleis: Only thing this needs before merging is a changelog note.

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8ec1052a..b0a18651 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -11,6 +11,9 @@ Notes on dependencies:
 API changes:
 
 - `unary_union` is now deprecated and replaced by the `union_all` method (#3007).
+- The `sjoin` method will now preserve the name of the index of the right
+  GeoDataFrame, if it has one, instead of always using `"index_right"` as the
+  name for the resulting column in the return value (#846, #2144).
 
 New methods:
 
diff --git a/geopandas/tools/sjoin.py b/geopandas/tools/sjoin.py
index 67ca1775..97425da3 100644
--- a/geopandas/tools/sjoin.py
+++ b/geopandas/tools/sjoin.py
@@ -1,4 +1,6 @@
+from functools import partial
 from typing import Optional
+import warnings
 
 import numpy as np
 import pandas as pd
@@ -146,18 +148,6 @@ def _basic_checks(left_df, right_df, how, lsuffix, rsuffix):
     if not _check_crs(left_df, right_df):
         _crs_mismatch_warn(left_df, right_df, stacklevel=4)
 
-    index_left = "index_{}".format(lsuffix)
-    index_right = "index_{}".format(rsuffix)
-
-    # due to GH 352
-    if any(left_df.columns.isin([index_left, index_right])) or any(
-        right_df.columns.isin([index_left, index_right])
-    ):
-        raise ValueError(
-            "'{0}' and '{1}' cannot be names in the frames being"
-            " joined".format(index_left, index_right)
-        )
-
 
 def _geom_predicate_query(left_df, right_df, predicate, distance):
     """Compute geometric comparisons and get matching indices.
@@ -210,6 +200,101 @@ def _geom_predicate_query(left_df, right_df, predicate, distance):
     return indices
 
 
+def _reset_index_with_suffix(df, suffix, other):
+    """
+    Equivalent of df.reset_index(), but with adding 'suffix' to auto-generated
+    column names.
+    """
+    index_original = df.index.names
+    df_reset = df.reset_index()
+    column_names = df_reset.columns.to_numpy(copy=True)
+    for i, label in enumerate(index_original):
+        # if the original label was None, add suffix to auto-generated name
+        if label is None:
+            new_label = column_names[i]
+            if "level" in new_label:
+                # reset_index of MultiIndex gives "level_i" names, preserve the "i"
+                lev = new_label.split("_")[1]
+                new_label = f"index_{suffix}{lev}"
+            else:
+                new_label = f"index_{suffix}"
+            # check new label will not be in other dataframe
+            if new_label in df.columns or new_label in other.columns:
+                raise ValueError(
+                    "'{0}' cannot be a column name in the frames being"
+                    " joined".format(new_label)
+                )
+            column_names[i] = new_label
+    return df_reset, pd.Index(column_names)
+
+
+def _process_column_names_with_suffix(
+    left: pd.Index, right: pd.Index, suffixes, left_df, right_df
+):
+    """
+    Add suffixes to overlapping labels (ignoring the geometry column).
+
+    This is based on pandas' merge logic at https://github.com/pandas-dev/pandas/blob/
+    a0779adb183345a8eb4be58b3ad00c223da58768/pandas/core/reshape/merge.py#L2300-L2370
+    """
+    to_rename = left.intersection(right)
+    if len(to_rename) == 0:
+        return left, right
+
+    lsuffix, rsuffix = suffixes
+
+    if not lsuffix and not rsuffix:
+        raise ValueError(f"columns overlap but no suffix specified: {to_rename}")
+
+    def renamer(x, suffix, geometry):
+        if x in to_rename and x != geometry and suffix is not None:
+            return f"{x}_{suffix}"
+        return x
+
+    lrenamer = partial(renamer, suffix=lsuffix, geometry=left_df._geometry_column_name)
+    rrenamer = partial(renamer, suffix=rsuffix, geometry=right_df._geometry_column_name)
+
+    # TODO retain index name?
+    left_renamed = pd.Index([lrenamer(lab) for lab in left])
+    right_renamed = pd.Index([rrenamer(lab) for lab in right])
+
+    dups = []
+    if not left_renamed.is_unique:
+        # Only warn when duplicates are caused because of suffixes, already duplicated
+        # columns in origin should not warn
+        dups = left_renamed[(left_renamed.duplicated()) & (~left.duplicated())].tolist()
+    if not right_renamed.is_unique:
+        dups.extend(
+            right_renamed[(right_renamed.duplicated()) & (~right.duplicated())].tolist()
+        )
+    # TODO turn this into an error (pandas has done so as well)
+    if dups:
+        warnings.warn(
+            f"Passing 'suffixes' which cause duplicate columns {set(dups)} in the "
+            f"result is deprecated and will raise a MergeError in a future version.",
+            FutureWarning,
+            stacklevel=4,
+        )
+
+    return left_renamed, right_renamed
+
+
+def _restore_index(joined, index_names, index_names_original):
+    """
+    Set back the the original index columns, and restoring their name as `None`
+    if they didn't have a name originally.
+    """
+    joined = joined.set_index(list(index_names))
+
+    # restore the fact that the index didn't have a name
+    joined_index_names = list(joined.index.names)
+    for i, label in enumerate(index_names_original):
+        if label is None:
+            joined_index_names[i] = None
+    joined.index.names = joined_index_names
+    return joined
+
+
 def _frame_join(join_df, left_df, right_df, how, lsuffix, rsuffix):
     """Join the GeoDataFrames at the DataFrame level.
 
@@ -240,33 +325,29 @@ def _frame_join(join_df, left_df, right_df, how, lsuffix, rsuffix):
     # index in geopandas may be any arbitrary dtype. so reset both indices now
     # and store references to the original indices, to be reaffixed later.
     # GH 352
-    index_left = "index_{}".format(lsuffix)
     left_df = left_df.copy(deep=True)
-    try:
-        left_index_name = left_df.index.name
-        left_df.index = left_df.index.rename(index_left)
-    except TypeError:
-        index_left = [
-            "index_{}".format(lsuffix + str(pos))
-            for pos, ix in enumerate(left_df.index.names)
-        ]
-        left_index_name = left_df.index.names
-        left_df.index = left_df.index.rename(index_left)
-    left_df = left_df.reset_index()
-
-    index_right = "index_{}".format(rsuffix)
+    left_nlevels = left_df.index.nlevels
+    left_index_original = left_df.index.names
+    left_df, left_column_names = _reset_index_with_suffix(left_df, lsuffix, right_df)
+
     right_df = right_df.copy(deep=True)
-    try:
-        right_index_name = right_df.index.name
-        right_df.index = right_df.index.rename(index_right)
-    except TypeError:
-        index_right = [
-            "index_{}".format(rsuffix + str(pos))
-            for pos, ix in enumerate(right_df.index.names)
-        ]
-        right_index_name = right_df.index.names
-        right_df.index = right_df.index.rename(index_right)
-    right_df = right_df.reset_index()
+    right_nlevels = right_df.index.nlevels
+    right_index_original = right_df.index.names
+    right_df, right_column_names = _reset_index_with_suffix(right_df, rsuffix, left_df)
+
+    # if conflicting names in left and right, add suffix
+    left_column_names, right_column_names = _process_column_names_with_suffix(
+        left_column_names,
+        right_column_names,
+        (lsuffix, rsuffix),
+        left_df,
+        right_df,
+    )
+    left_df.columns = left_column_names
+    right_df.columns = right_column_names
+
+    left_index = left_df.columns[:left_nlevels]
+    right_index = right_df.columns[:right_nlevels]
 
     # perform join on the dataframes
     if how == "inner":
@@ -277,15 +358,11 @@ def _frame_join(join_df, left_df, right_df, how, lsuffix, rsuffix):
                 right_df.drop(right_df.geometry.name, axis=1),
                 left_on="_key_right",
                 right_index=True,
-                suffixes=("_{}".format(lsuffix), "_{}".format(rsuffix)),
+                suffixes=(None, None),
             )
-            .set_index(index_left)
             .drop(["_key_right"], axis=1)
         )
-        if isinstance(index_left, list):
-            joined.index.names = left_index_name
-        else:
-            joined.index.name = left_index_name
+        joined = _restore_index(joined, left_index, left_index_original)
 
     elif how == "left":
         join_df = join_df.set_index("_key_left")
@@ -296,15 +373,11 @@ def _frame_join(join_df, left_df, right_df, how, lsuffix, rsuffix):
                 how="left",
                 left_on="_key_right",
                 right_index=True,
-                suffixes=("_{}".format(lsuffix), "_{}".format(rsuffix)),
+                suffixes=(None, None),
             )
-            .set_index(index_left)
             .drop(["_key_right"], axis=1)
         )
-        if isinstance(index_left, list):
-            joined.index.names = left_index_name
-        else:
-            joined.index.name = left_index_name
+        joined = _restore_index(joined, left_index, left_index_original)
 
     else:  # how == 'right':
         joined = (
@@ -316,16 +389,12 @@ def _frame_join(join_df, left_df, right_df, how, lsuffix, rsuffix):
                 left_index=True,
                 right_on="_key_left",
                 how="right",
-                suffixes=("_{}".format(lsuffix), "_{}".format(rsuffix)),
+                suffixes=(None, None),
             )
-            .set_index(index_right)
             .drop(["_key_left", "_key_right"], axis=1)
             .set_geometry(right_df.geometry.name)
         )
-        if isinstance(index_right, list):
-            joined.index.names = right_index_name
-        else:
-            joined.index.name = right_index_name
+        joined = _restore_index(joined, right_index, right_index_original)
 
     return joined
 


Test patch:
diff --git a/geopandas/tools/tests/test_sjoin.py b/geopandas/tools/tests/test_sjoin.py
index a3c83df3..beeecb02 100644
--- a/geopandas/tools/tests/test_sjoin.py
+++ b/geopandas/tools/tests/test_sjoin.py
@@ -149,7 +149,7 @@ class TestSpatialJoin:
             ].astype("int64")
         if index == "named-index":
             exp[["df1_ix", "df2_ix"]] = exp[["df1_ix", "df2_ix"]].astype("int64")
-            exp = exp.set_index("df1_ix").rename(columns={"df2_ix": "index_right"})
+            exp = exp.set_index("df1_ix")
         if index in ["default-index", "string-index"]:
             exp = exp.set_index("index_left")
             exp.index.name = None
@@ -159,10 +159,7 @@ class TestSpatialJoin:
             )
             exp.index.names = df1.index.names
         if index == "named-multi-index":
-            exp = exp.set_index(["df1_ix1", "df1_ix2"]).rename(
-                columns={"df2_ix1": "index_right0", "df2_ix2": "index_right1"}
-            )
-            exp.index.names = df1.index.names
+            exp = exp.set_index(["df1_ix1", "df1_ix2"])
 
         assert_frame_equal(res, exp)
 
@@ -199,7 +196,7 @@ class TestSpatialJoin:
             res["index_right"] = res["index_right"].astype(float)
         elif index == "named-index":
             exp[["df1_ix"]] = exp[["df1_ix"]].astype("int64")
-            exp = exp.set_index("df1_ix").rename(columns={"df2_ix": "index_right"})
+            exp = exp.set_index("df1_ix")
         if index in ["default-index", "string-index"]:
             exp = exp.set_index("index_left")
             exp.index.name = None
@@ -209,10 +206,7 @@ class TestSpatialJoin:
             )
             exp.index.names = df1.index.names
         if index == "named-multi-index":
-            exp = exp.set_index(["df1_ix1", "df1_ix2"]).rename(
-                columns={"df2_ix1": "index_right0", "df2_ix2": "index_right1"}
-            )
-            exp.index.names = df1.index.names
+            exp = exp.set_index(["df1_ix1", "df1_ix2"])
 
         assert_frame_equal(res, exp)
 
@@ -315,7 +309,7 @@ class TestSpatialJoin:
             res["index_left"] = res["index_left"].astype(float)
         elif index == "named-index":
             exp[["df2_ix"]] = exp[["df2_ix"]].astype("int64")
-            exp = exp.set_index("df2_ix").rename(columns={"df1_ix": "index_left"})
+            exp = exp.set_index("df2_ix")
         if index in ["default-index", "string-index"]:
             exp = exp.set_index("index_right")
             exp = exp.reindex(columns=res.columns)
@@ -326,10 +320,8 @@ class TestSpatialJoin:
             )
             exp.index.names = df2.index.names
         if index == "named-multi-index":
-            exp = exp.set_index(["df2_ix1", "df2_ix2"]).rename(
-                columns={"df1_ix1": "index_left0", "df1_ix2": "index_left1"}
-            )
-            exp.index.names = df2.index.names
+            exp = exp.set_index(["df2_ix1", "df2_ix2"])
+
         if predicate == "within":
             exp = exp.sort_index()
 
@@ -392,6 +384,232 @@ class TestSpatialJoin:
         assert_frame_equal(expected_gdf.sort_index(), joined.sort_index())
 
 
+class TestIndexNames:
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_preserve_index_names(self, how):
+        # preserve names of both left and right index
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame({"geometry": geoms}, index=pd.Index([1, 2], name="myidx1"))
+        df2 = GeoDataFrame(
+            {"geometry": geoms}, index=pd.Index(["a", "b"], name="myidx2")
+        )
+        result = sjoin(df1, df2, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {"myidx1": [1, 2], "geometry": geoms, "myidx2": ["a", "b"]}
+            ).set_index("myidx1")
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {"myidx2": ["a", "b"], "myidx1": [1, 2], "geometry": geoms},
+            ).set_index("myidx2")
+        assert_geodataframe_equal(result, expected)
+
+        # but also add suffixes if both left and right have the same index
+        df1.index.name = "myidx"
+        df2.index.name = "myidx"
+        result = sjoin(df1, df2, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {"myidx_left": [1, 2], "geometry": geoms, "myidx_right": ["a", "b"]}
+            ).set_index("myidx_left")
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {"myidx_right": ["a", "b"], "myidx_left": [1, 2], "geometry": geoms},
+            ).set_index("myidx_right")
+        assert_geodataframe_equal(result, expected)
+
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_preserve_index_names_multiindex(self, how):
+        # preserve names of both left and right index
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame(
+            {"geometry": geoms},
+            index=pd.MultiIndex.from_tuples(
+                [("a", 1), ("b", 2)], names=["myidx1", "level2"]
+            ),
+        )
+        df2 = GeoDataFrame(
+            {"geometry": geoms},
+            index=pd.MultiIndex.from_tuples(
+                [("c", 3), ("d", 4)], names=["myidx2", None]
+            ),
+        )
+        result = sjoin(df1, df2, how=how)
+        expected_base = GeoDataFrame(
+            {
+                "myidx1": ["a", "b"],
+                "level2": [1, 2],
+                "geometry": geoms,
+                "myidx2": ["c", "d"],
+                "index_right1": [3, 4],
+            }
+        )
+        if how in ("inner", "left"):
+            expected = expected_base.set_index(["myidx1", "level2"])
+        else:
+            # right join
+            expected = expected_base.set_index(["myidx2", "index_right1"])
+            # if it was originally None, that is preserved
+            expected.index.names = ["myidx2", None]
+        assert_geodataframe_equal(result, expected)
+
+        # but also add suffixes if both left and right have the same index
+        df1.index.names = ["myidx", "level2"]
+        df2.index.names = ["myidx", None]
+        result = sjoin(df1, df2, how=how)
+        expected_base = GeoDataFrame(
+            {
+                "myidx_left": ["a", "b"],
+                "level2": [1, 2],
+                "geometry": geoms,
+                "myidx_right": ["c", "d"],
+                "index_right1": [3, 4],
+            }
+        )
+        if how in ("inner", "left"):
+            expected = expected_base.set_index(["myidx_left", "level2"])
+        else:
+            # right join
+            expected = expected_base.set_index(["myidx_right", "index_right1"])
+            # if it was originally None, that is preserved
+            expected.index.names = ["myidx_right", None]
+        assert_geodataframe_equal(result, expected)
+
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_duplicate_column_index_name(self, how):
+        # case where a left column and the right index have the same name or the
+        # other way around -> correctly add suffix or preserve index name
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame({"myidx": [1, 2], "geometry": geoms})
+        df2 = GeoDataFrame(
+            {"geometry": geoms}, index=pd.Index(["a", "b"], name="myidx")
+        )
+        result = sjoin(df1, df2, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {"myidx_left": [1, 2], "geometry": geoms, "myidx_right": ["a", "b"]}
+            )
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {"index_left": [0, 1], "myidx_left": [1, 2], "geometry": geoms},
+                index=pd.Index(["a", "b"], name="myidx_right"),
+            )
+        assert_geodataframe_equal(result, expected)
+
+        result = sjoin(df2, df1, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {"geometry": geoms, "index_right": [0, 1], "myidx_right": [1, 2]},
+                index=pd.Index(["a", "b"], name="myidx_left"),
+            )
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {"myidx_left": ["a", "b"], "myidx_right": [1, 2], "geometry": geoms},
+            )
+        assert_geodataframe_equal(result, expected)
+
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_duplicate_column_index_name_multiindex(self, how):
+        # case where a left column and the right index have the same name or the
+        # other way around -> correctly add suffix or preserve index name
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame({"myidx": [1, 2], "geometry": geoms})
+        df2 = GeoDataFrame(
+            {"geometry": geoms},
+            index=pd.MultiIndex.from_tuples(
+                [("a", 1), ("b", 2)], names=["myidx", "level2"]
+            ),
+        )
+        result = sjoin(df1, df2, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {
+                    "myidx_left": [1, 2],
+                    "geometry": geoms,
+                    "myidx_right": ["a", "b"],
+                    "level2": [1, 2],
+                }
+            )
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {"index_left": [0, 1], "myidx_left": [1, 2], "geometry": geoms},
+                index=pd.MultiIndex.from_tuples(
+                    [("a", 1), ("b", 2)], names=["myidx_right", "level2"]
+                ),
+            )
+        assert_geodataframe_equal(result, expected)
+
+        result = sjoin(df2, df1, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {"geometry": geoms, "index_right": [0, 1], "myidx_right": [1, 2]},
+                index=pd.MultiIndex.from_tuples(
+                    [("a", 1), ("b", 2)], names=["myidx_left", "level2"]
+                ),
+            )
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {
+                    "myidx_left": ["a", "b"],
+                    "level2": [1, 2],
+                    "myidx_right": [1, 2],
+                    "geometry": geoms,
+                },
+            )
+        assert_geodataframe_equal(result, expected)
+
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_conflicting_column_index_name(self, how):
+        # test case where the auto-generated index name conflicts
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame({"index_right": [1, 2], "geometry": geoms})
+        df2 = GeoDataFrame({"geometry": geoms})
+        with pytest.raises(ValueError, match="'index_right' cannot be a column name"):
+            sjoin(df1, df2, how=how)
+
+    @pytest.mark.xfail
+    @pytest.mark.parametrize("how", ["inner", "left", "right"])
+    def test_conflicting_column_with_suffix(self, how):
+        # test case where the auto-generated index name conflicts
+        geoms = [Point(1, 1), Point(2, 2)]
+        df1 = GeoDataFrame(
+            {"column": [1, 2], "column_right": ["a", "b"], "geometry": geoms}
+        )
+        df2 = GeoDataFrame({"column": [0.1, 0.2], "geometry": geoms})
+
+        result = sjoin(df1, df2, how=how)
+        if how in ("inner", "left"):
+            expected = GeoDataFrame(
+                {1: [1, 2], 2: ["a", "b"], 3: geoms, 4: [0, 1], 5: [0.1, 0.2]}
+            )
+            expected.columns = [
+                "column_left",
+                "column_right",
+                "geometry",
+                "index_right",
+                "column_right",
+            ]
+        else:
+            # right join
+            expected = GeoDataFrame(
+                {1: [0, 1], 2: [1, 2], 3: ["a", "b"], 4: [0.1, 0.2], 5: geoms}
+            )
+            expected.columns = [
+                "index_left",
+                "column_left",
+                "column_right",
+                "column_right",
+                "geometry",
+            ]
+        assert_geodataframe_equal(result, expected)
+
+
 @pytest.mark.usefixtures("_setup_class_nybb_filename")
 class TestSpatialJoinNYBB:
     def setup_method(self):