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
EXPLAIN ANYZE <query>;
to the question body. Also, what indexes are there besides the GIST on the geometries? What is the CRS of theshape
geometries? Do you need to run this over and over, or why is a minute too much? Do you update the table regularly?st_dwithin(p1.shape::geography, p2.shape::geography,90)