Here is a snippet from my code:
ST_DWithin(c.convex_hull::geography , cp."geoJson"::geography, 20)
Please note both "convex_hull" and "geoJson" are geometry columns (which is why i am casting to geography). Further, please note that both columns have spatial indexing implemented as follows:
CREATE INDEX idx_bfp_clusters_convex_hull ON public.bfp_clusters USING gist (convex_hull)
CREATE INDEX "cp_geoJson_idx" ON public.campaign_pings USING gist ("geoJson")
Do I have to cast geometries to geography in both the CREATE INDEX commands above, or can I cast to geography in just one of the commands (the convex_hull one) to avail the speed benefit of spatial indexing?
Asking this since I would rather not tamper with the INDEX on the "geoJson" since it might affect existing code. Is there a way I can keep the existing index on the "geoJson" column, and add another geography index on the same column?
For more context, here is the aforementioned code snippet in place in a CTE:
clusters_with_coverage AS ( -- Select the cluster_id, convex_hull geometry, and calculate coverage select c.uc_name, c.cluster_id, c.convex_hull, CASE -- Use the EXISTS keyword to check if any campaign pings are within 20 meters of the cluster's convex hull WHEN EXISTS ( -- Subquery: Check if there is any campaign ping within 20 meters of the cluster SELECT 1 FROM campaign_ping cp -- Use ST_DWithin function to determine if the campaign ping is within 20 meters of the cluster's convex hull WHERE ST_DWithin(c.convex_hull::geography , cp."geoJson"::geography, 20) ) THEN 'covered' -- If campaign pings are within 20 meters, mark coverage as 'covered' ELSE 'missed' -- If no campaign pings are within 20 meters, mark coverage as 'missed' END AS coverage FROM bfp_clusters c -- Select cluster data from the 'bfp_clusters' table )