topic Re: 999999 Add Spatial Join Doesn't Work on External Database in ArcGIS Pro Questions

999999 Add Spatial Join Doesn't Work on External Database

ack_ack_ack_ack — Sat, 07 Jun 2025 01:38:28 GMT

Per the title, spatial joins don't work when using an external database. In this case, PostgreSQL.

One table has points encoded as geometry. ArcGIS correctly identifies them.
The other table has polygons included in a shapefile. Polygons render correctly on the map.
Attempt to join the two tables using "intersect" generates the error.

ERROR 999999: Something unexpected caused the tool to fail. Contact Esri Technical Support (http://esriurl.com/support) to Report a Bug, and refer to the error help for potential solutions or workarounds.
Invalid SQL syntax[ERROR: syntax error at or near "%"
LINE 1: select bike_share.public.%bike_stations_data_3_8_52_305_1406...
^::SQLSTATE=42601]
Failed to execute (AddSpatialJoin).

How does one workaround the wildcard symbol where it does not belong?
Is it the case that while one can connect to external databases, the feature is totally broken?

Re: 999999 Add Spatial Join Doesn't Work on External Database

MarcoBoeringa — Sat, 07 Jun 2025 09:44:27 GMT

In the error I see reference to PostgreSQL's 'public' user schema.

As of PG15, working with public schema to store user data has been strongly discouraged by the the PostgreSQL developers and community due to security concerns, and default access rights revoked. This may be causing you issues.

I recommend you to create a new user with its own dedicated user schema and not grant it superuser rights but just leave it at its ordinary limited rights.

Re: 999999 Add Spatial Join Doesn't Work on External Database

ack_ack_ack_ack — Tue, 10 Jun 2025 02:09:46 GMT

LOL no.

This is a local Postgresql instance. I'm using the postgres user credentials.

The error message makes it obvious the problem is the developers didn't take the time to parse available columns, then write the SELECT statement using the available columns.
The documentation suggests it would "just work." It does not. At all.
https://pro.arcgis.com/en/pro-app/3.3/help/data/databases/database-connections-in-arcgis-pro.htm

Re: 999999 Add Spatial Join Doesn't Work on External Database

ack_ack_ack_ack — Wed, 11 Jun 2025 06:05:01 GMT

3 + 3 = 33

Null. Duplicate

Re: 999999 Add Spatial Join Doesn't Work on External Database

ack_ack_ack_ack — Wed, 11 Jun 2025 06:04:03 GMT

1 + 1 = 3
Null. Duplicate