1

I have a table of 115,677 records. I am attempting to create a unique ID for groups of parcels that are owned by the same owner and are within 90 meters of each other. I determine if they are the same owner if the owner name column is the same or if the owner address column values are the same. Here's the SQL that I was able to get working for this:

ALTER TABLE parcel_owner_group_analysis
ADD COLUMN owner_group_id serial;

ALTER TABLE parcel_owner_group_analysis ALTER COLUMN owner_group_id DROP NOT NULL;

SELECT p1.objectid, p1.combined_owner_address, p1.combined_owner_name, p1.shape,
(
    SELECT min(p2.owner_group_id)
    FROM parcel_owner_group_analysis p2
    WHERE (
        (p1.combined_owner_name <> 'UNKNOWN' AND p2.combined_owner_name <> 'UNKNOWN' 
            AND p1.combined_owner_name = p2.combined_owner_name)
        OR (p1.combined_owner_address <> 'UNKNOWN' AND p2.combined_owner_address <> ('UNKNOWN')
            AND p1.combined_owner_address = p2.combined_owner_address)
    )
    AND ST_DWithin(p1.shape, p2.shape, 90) -- within 90 meters
) AS result_column
FROM parcel_owner_group_analysis p1;

The above takes 26 seconds to run on 115k records. Is there any way I can speed this up or do this better? There is a GIST index created on the geometry column (shape)

SRID: 3857. Any suggestions would be appreciated. Here is the EXPLAIN ANALYZE:

Seq Scan on parcel_owner_group_analysis p1  (cost=0.00..3598224.19 
rows=115677 width=359) (actual time=0.114..22288.375 rows=115677 
loops=1)
 SubPlan 1
 ->  Aggregate  (cost=31.04..31.05 rows=1 width=4) (actual 
 time=0.191..0.191 rows=1 loops=115677)
      ->  Bitmap Heap Scan on parcel_owner_group_analysis p2  
(cost=4.91..31.04 rows=1 width=4) (actual time=0.123..0.182 rows=2 
 loops=115677)
            Recheck Cond: (((p1.combined_owner_name)::text = 
 (combined_owner_name)::text) OR ((p1.combined_owner_address)::text 
 = (combined_owner_address)::text))
            Filter: (((((p1.combined_owner_name)::text <> 
 'UNKNOWN'::text) AND ((combined_owner_name)::text <> 
 'UNKNOWN'::text) AND ((p1.combined_owner_name)::text = 
 (combined_owner_name)::text)) OR 
 (((p1.combined_owner_address)::text <> 'UNKNOWN'::text) AND 
 ((combined_owner_address)::text <> 'UNKNOWN'::text) AND 
 ((p1.combined_owner_address)::text = 
 (combined_owner_address)::text))) AND st_dwithin(p1.shape, shape, 
 '90'::double precision))
            Rows Removed by Filter: 0
            Heap Blocks: exact=230342
            ->  BitmapAnd  (cost=4.91..4.91 rows=1 width=0) (actual 
 time=0.110..0.110 rows=0 loops=115677)
                  ->  Bitmap Index Scan on geom_idx  
 (cost=0.00..1.60 rows=12 width=0) (actual time=0.064..0.064 
 rows=32 loops=115677)
                        Index Cond: (shape && st_expand(p1.shape, 
 '90'::double precision))
                  ->  BitmapOr  (cost=3.07..3.07 rows=4 width=0) 
 (actual time=0.040..0.040 rows=0 loops=115677)
                        ->  Bitmap Index Scan on 
  combined_owner_name_idx  (cost=0.00..1.53 rows=2 width=0) (actual 
 time=0.023..0.023 rows=66 loops=115677)
                              Index Cond: 
((combined_owner_name)::text = (p1.combined_owner_name)::text)
                        ->  Bitmap Index Scan on 
combined_owner_address_idx  (cost=0.00..1.53 rows=2 width=0) 
(actual time=0.016..0.016 rows=40 loops=115677)
                              Index Cond: 
 ((combined_owner_address)::text = 
 (p1.combined_owner_address)::text)
 Planning Time: 0.378 ms
 Execution Time: 22301.460 ms
8
  • 2
    Please add the output of EXPLAIN ANYZE <query>; to the question body. Also, what indexes are there besides the GIST on the geometries? What is the CRS of the shape geometries? Do you need to run this over and over, or why is a minute too much? Do you update the table regularly?
    – geozelot
    Jun 6, 2023 at 8:28
  • 1
    I don't understand why you're using serial type for the owner_group_id column. Why not int? Then you don't need to drop the not null, and it would make sense. What SRID is the geometry? If it's something like 4326 or other geographic system, you would be searching a 90 DEGREE radius (half the entire earth). If that's the case, you can cast the geometry to geography for the st_dwithin - st_dwithin(p1.shape::geography, p2.shape::geography,90)
    – jbalk
    Jun 6, 2023 at 17:56
  • Are you aware that creating that column as serial type is also filling the column with values? When you create a serial type column, it will fill the column with auto-incremented numbers.
    – jbalk
    Jun 6, 2023 at 18:01
  • 2
    Also, take a look at ST_ClusterDBSCAN. It can group geoms within a specified distance. Because it is a window function it can be partitioned by owner name and address. Then it is a simple matter of filtering groups with more than two parcels in a group.
    – DavidP
    Jun 7, 2023 at 7:19
  • 1
    I've also had luck with st_clusterwithin
    – jbalk
    Jun 8, 2023 at 0:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.