1

I'm writing an SQL statement to create a feature class and using st_geometry functions. But it's taking too long.

So I begin the statement with a join, which is taking 150-200ms

SELECT PRC.OBJECTID AS OWNED_PARCEL_FID, 
                       OWN.DEPARTMENT, 
                       OWN.STATE_NAME, 
                       OWN.CITY_NAME, 
                       OWN.NEIGHBRHD_NAME, 
                       OWN.BLOCK, 
                       OWN.PARCEL, 
                       OWN.NAME, 
                       OWN.CONDITION, 
                       PRC.SHAPE 
                FROM   CITY_PARCEL PRC 
                       INNER JOIN PARCEL_OWNERSHIP OWN 
                               ON PRC.OBJECTID = OWN.GIS_ID 
                WHERE OWN.STATUS = 'OWNED'

then I added ST_Intersects and ST_Intersection functions

SELECT OWNED_PARCEL.OWNED_PARCEL_fid AS OWNED_PARCEL_FID, 
               PLAN.OBJECTID AS PLAN_FID, 
               PLAN.PLAN_CODE, 
               PLAN.PLAN_NAME, 
               PLAN.PLAN_FUNCTION, 
               PLAN.PLAN_MAIN_FUNCTION, 
               PLAN.USAGE_YTPE, 
               OWNED_PARCEL.DEPARTMENT, 
               OWNED_PARCEL.STATE_NAME, 
               OWNED_PARCEL.CITY_NAME, 
               OWNED_PARCEL.NEIGHBRHD_NAME, 
               OWNED_PARCEL.BLOCK, 
               OWNED_PARCEL.PARCEL, 
               OWNED_PARCEL.NAME,  
               SDE.ST_INTERSECTION(OWNED_PARCEL.SHAPE, PLAN.SHAPE) AS SHAPE 
        FROM   (SELECT PRC.OBJECTID AS OWNED_PARCEL_FID, 
                       OWN.DEPARTMENT, 
                       OWN.STATE_NAME, 
                       OWN.CITY_NAME, 
                       OWN.NEIGHBRHD_NAME, 
                       OWN.BLOCK, 
                       OWN.PARCEL, 
                       OWN.NAME, 
                       OWN.CONDITION, 
                       PRC.SHAPE 
                FROM   CITY_PARCEL PRC 
                       INNER JOIN PARCEL_OWNERSHIP OWN 
                               ON PRC.OBJECTID = OWN.GIS_ID 
                WHERE OWN.STATUS = 'OWNED') OWNED_PARCEL, 
                      CITY_PLAN PLAN 
        WHERE SDE.ST_INTERSECTS(OWNED_PARCEL.SHAPE, PLAN.SHAPE) = 1)OWNED_PARCEL_PLAN_INTERSECT;

This is where it gets extremely slow. Like around 8 minutes. Parcel table has roughly 23000 records and Plan table has 280000 records. I assume it shouldn't take so long. Even if I take first 100 rows of the tables. It takes around 4 mins.

After that, I applied ST_Symmetricdiff with this select and PARCEL table and union this two tables. That SQL worked for a day and still didn't finished. So I had to kill the process. Are those times makes sense or is there something wrong with it?

Here is the last statement.

SELECT owned_PARCEL_fid, 
       plan_fid, 
       PLAN_CODE, 
       PLAN_NAME, 
       PLAN_FUNCTION, 
       USAGE_TYPE, 
       DEPARTMENT, 
       STATE_NAME, 
       CITY_ANME, 
       NEIGHBRHD_NAME, 
       BLOCK, 
       PARCEL, 
       NAME, 
       CONDIT,  
       OWNED_PARCEL_SHAPE_AREA, 
       SHAPE 
FROM   (SELECT OWNED_PARCEL.owned_PARCEL_fid, 
               OWNED_PARCEL_PLAN_INTERSECTION.plan_fid, 
               OWNED_PARCEL_PLAN_INTERSECTION.PLAN_CODE, 
               OWNED_PARCEL_PLAN_INTERSECTION.PLAN_NAME, 
               OWNED_PARCEL_PLAN_INTERSECTION.PLAN_FUNCTION, 
               OWNED_PARCEL_PLAN_INTERSECTION.USAGE_TYPE, 
               OWNED_PARCEL.DEPARTMENT, 
               OWNED_PARCEL.STATE_NAME, 
               OWNED_PARCEL.CITY_ANME, 
               OWNED_PARCEL.NEIGHBRHD_NAME, 
               OWNED_PARCEL.BLOCK, 
               OWNED_PARCEL.PARCEL, 
               OWNED_PARCEL.NAME, 
               OWNED_PARCEL.CONDIT, 
               SDE.ST_AREA(OWNED_PARCEL.SHAPE) AS OWNED_PARCEL_SHAPE_AREA, 
               SDE.ST_SYMMETRICDIFF(OWNED_PARCEL.SHAPE,OWNED_PARCEL_PLAN_INTERSECTION.SHAPE) AS SHAPE 
        FROM   (SELECT PRC.OBJECTID AS OWNED_PARCEL_FID, 
                       OWN.DEPARTMENT, 
                       OWN.STATE_NAME, 
                       OWN.CITY_ANME, 
                       OWN.NEIGHBRHD_NAME, 
                       OWN.BLOCK, 
                       OWN.PARCEL, 
                       OWN.NAME, 
                       OWN.CONDIT, 
                       PRC.SHAPE 
                FROM   CITY_PARCEL PRC 
                       INNER JOIN PARCEL_ownership OWN 
                               ON PRC.OBJECTID = OWN.GIS_ID 
                WHERE  OWN.status = 'OWNED_PARCEL') OWNED_PARCEL, 
               (SELECT OWNED_PARCEL.owned_PARCEL_fid AS OWNED_PARCEL_FID, 
                       PLAN.OBJECTID AS PLAN_FID, 
                       PLAN.PLAN_CODE, 
                       PLAN.PLAN_NAME, 
                       PLAN.PLAN_FUNCTION, 
                       PLAN.USAGE_TYPE, 
                       OWNED_PARCEL.DEPARTMENT, 
                       OWNED_PARCEL.STATE_NAME, 
                       OWNED_PARCEL.CITY_ANME, 
                       OWNED_PARCEL.NEIGHBRHD_NAME, 
                       OWNED_PARCEL.BLOCK, 
                       OWNED_PARCEL.PARCEL, 
                       OWNED_PARCEL.NAME, 
                       SDE.ST_INTERSECTION(OWNED_PARCEL.SHAPE, PLAN.SHAPE) AS 
                       SHAPE 
                FROM   (SELECT PRC.OBJECTID AS OWNED_PARCEL_FID, 
                               OWN.DEPARTMENT, 
                               OWN.STATE_NAME, 
                               OWN.CITY_ANME, 
                               OWN.NEIGHBRHD_NAME, 
                               OWN.BLOCK, 
                               OWN.PARCEL, 
                               OWN.NAME, 
                               OWN.CONDIT, 
                               PRC.SHAPE 
                        FROM   CITY_PARCEL PRC 
                               INNER JOIN PARCEL_ownership OWN 
                                       ON PRC.OBJECTID = OWN.GIS_ID 
                        WHERE  OWN.status = 'OWNED_PARCEL') OWNED_PARCEL, 
                       CITY_PLAN PLAN 
                WHERE  SDE.St_intersects(OWNED_PARCEL.SHAPE, PLAN.SHAPE) = 1) 
               OWNED_PARCEL_PLAN_INTERSECTION 
        UNION 
        SELECT OWNED_PARCEL.owned_PARCEL_fid AS OWNED_PARCEL_FID, 
               PLAN.OBJECTID AS PLAN_FID, 
               PLAN.PLAN_CODE, 
               PLAN.PLAN_NAME, 
               PLAN.PLAN_FUNCTION,
               PLAN.USAGE_TYPE, 
               OWNED_PARCEL.DEPARTMENT, 
               OWNED_PARCEL.STATE_NAME, 
               OWNED_PARCEL.CITY_ANME, 
               OWNED_PARCEL.NEIGHBRHD_NAME, 
               OWNED_PARCEL.BLOCK, 
               OWNED_PARCEL.PARCEL, 
               OWNED_PARCEL.NAME, 
               OWNED_PARCEL.CONDIT, 
               SDE.ST_AREA(OWNED_PARCEL.SHAPE) AS OWNED_PARCEL_SHAPE_AREA, 
               SDE.ST_INTERSECTION(OWNED_PARCEL.SHAPE, PLAN.SHAPE) AS SHAPE 
        FROM   (SELECT PRC.OBJECTID AS OWNED_PARCEL_FID, 
                       OWN.DEPARTMENT, 
                       OWN.STATE_NAME, 
                       OWN.CITY_ANME, 
                       OWN.NEIGHBRHD_NAME, 
                       OWN.BLOCK, 
                       OWN.PARCEL, 
                       OWN.NAME, 
                       OWN.CONDIT, 
                       PRC.SHAPE 
                FROM   CITY_PARCEL PRC 
                       INNER JOIN PARCEL_ownership OWN 
                               ON PRC.OBJECTID = OWN.GIS_ID 
                WHERE  OWN.status = 'OWNED') OWNED_PARCEL, 
               CITY_PLAN PLAN 
        WHERE  SDE.ST_INTERSECTS(OWNED_PARCEL.SHAPE, PLAN.SHAPE) = 1); 
  • 2
    That is some seriously ugly SQL. I expect that if you start reviewing the optimizer query plan, you'll see that the Esri functions are an innocent victim of a poorly structured query. – Vince Aug 4 '17 at 10:10
  • 1
    I suppose that the optimizer decides not to use spatial index or applies it too late. Use explain and analyze your query starting from simple core and adding more components step by step. Cartesian product of 23000x280000 is much if your query leads to that. – user30184 Aug 4 '17 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.