topic Spatial Join with st_geometry in Geodatabase Questions

Spatial Join with st_geometry

forestknutsen1 — Sat, 11 Mar 2023 01:35:40 GMT

I would like to do a spatial join between points and polygons with st_geometry this is what I have come up with:

select sp.OBJECTID, p.parcel_num from servicepoint_evw sp join parcels p on (sde.st_intersects(sp.shape, p.shape)) = 1

This does appear to work but it is supper slow... I took over a minute to return one row.

Re: Spatial Join with st_geometry

DavidPike — Sat, 11 Mar 2023 12:18:39 GMT

How big is the dataset? One thing to check is that you have a spatial index, it is much faster if so. Maybe the 'disjoint' test is faster.

When was the last time the database was compressed (I'm guessing versioned as you have _evw)? Is it going to state 1 or 0? Otherwise the versioned view query is quite expensive to work through - checking on the delta tables and the base table differences.

Re: Spatial Join with st_geometry

forestknutsen1 — Mon, 13 Mar 2023 18:18:38 GMT

yes, it has a spatial index
yes, we are versioned and no we never get back a low state count and we do have a large number of delta rows. So this could be the issue. (and yes we know this is a problem... I will spare you the details)
I will give disjoint a try

Thanks for the feedback

Re: Spatial Join with st_geometry

DavidPike — Mon, 13 Mar 2023 18:28:25 GMT

Might also be worth testing against the base table rather than evw to confirm this as the likely cause of the inefficiency (appreciate record count would differ however).

Re: Spatial Join with st_geometry

forestknutsen1 — Mon, 13 Mar 2023 21:04:47 GMT

That is a great idea. I just did it against the base table and it is only 10% faster. So, the versioning is playing a role. But it is not the main issue.