Alternative to ST_PointOnSurface Operation

Question

Hey all,

Is there an alternative to the ST_PointOnSurface operation that returns a point inside of a polygon? We have this operation in a query and it is running really slow for us (as in 48 hours slow - and never finishes). This is running in an Oracle enterprise geodatabase.

ST_Centroid won't work for our particular purpose, as it sometimes gets a point outside of the polygon.

Any thoughts?

Thanks!

VinceAngelo · Answer

It's more likely that the ST_Intersects() is your performance bottleneck, especially if you have not constructed a spatial index. Personally, I'd do this query as a single statement using a virtual table, and with an ST_Within: UPDATE parcel_new_pin u SET zoning = vt.zoning FROM ( SELECT p.pin, p.zoning FROM parcel_new_pin p JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1 WHERE p.zoning <> z2.zoning_district AND z2.zoning_district <> 'HB-S MLKO' ) vt WHERE u.pin = vt.pin; For this query, I'd make sure that ZONING has an index on the geometry column, and PARCEL_NEW_PIN has an index on PIN. Additionally, you can test the raw performance of the ST_PointOnSurface query in isolation: SELECT sde.ST_PointOnSurface((p.shape)) FROM parcel_new_pin p And the performance of the JOIN without UPDATE: SELECT p.pin, p.zoning FROM parcel_new_pin p JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1 WHERE p.zoning <> z2.zoning_district AND z2.zoning_district <> 'HB-S MLKO' If you haven't built a spatial index on the polygons of ZONING, that should be your first priority, though it's probably easiest to use the ArcPy utility Add Spatial Index (Data Management) via an enterprise geodatabase connection (.sde) file, at least at first, since the parameters can be tricky. - V

JoshBillings · Answer

Thanks for the reply @VinceAngelo. I am pretty new to the SDE.ST_GEOMETRY so I apologize if I ask a dumb question 😂 I am working in tandem with my DBA and ST_GEOMETRY is also new to him.

- There are ~163000 features.
- I'm unsure of how the indexes are built - is there an easy way to figure this out?
- The query we are using is below. Basically, we are populating fields in a parcels table based on the intersection of two shapes.

Additional info. We are currently using this procedure in an Oracle 11g geodatabase and are migrating it to a 19c database. It works ok in the Oracle 11g geodatabase (takes about 42 minutes to run), but seems to get "hung up" in the 19c database. We're wondering if the st_pointonsurface might be the cause, but are unsure.

create or replace procedure load_parcel_Surface is

cursor c1 is
  select p.pin, p.zoning, z2.zoning_district from parcel_new_pin p, ZONING z2  where 
  sde.st_intersects(z2.shape, sde.st_pointonsurface((p.shape))) = 1
  and  p.zoning != z2.zoning_district
  and z2.zoning_district !='HB-S MLKO';
v_c1     c1%rowtype;
v_count  number;
begin
  v_count :=0;
  open c1;
  loop
    fetch c1
     into v_c1;
    exit when c1%notfound;
--DBMS_OUTPUT.PUT_LINE('pin:=' || v_c1.pin);
    update parcel_new_pin PP set PP.zoning= V_C1.ZONING_DISTRICT 
       where pp.pin=v_c1.pin;
    v_count := v_count + 1;   
--DBMS_OUTPUT.PUT_LINE('v_count:=' || v_count);    
    commit;   
  end loop;
  close c1;
end load_parcel_Surface;

VinceAngelo · Answer

I've done a join with 72M ST_PointOnSurface calculations against a 35M polygon layer to verify parent-child relationships using PostgreSQL with PostGIS geometry, and it ran in less than 25min (on a RDS xlarge instance). I never tried that in Oracle with SDE.ST_GEOMETRY (or with the PG sde.ST_Geometry implementation)

How many features? What is the average vertex count in the polygons? How are the indexes built? What does the query plan look like? How long does a simple "SELECT geomcol FROM tablename" query take on the table, and how long does a unbounded ST_PointOnSurface query take.  Including the actual query will help generate feedback.

- V