Based on the address locations
ad for the region of interest (as a reference), I am trying to make selections from two street layers
street2. What I have tried is shown by the code below:
Select --from address table address_id, address_locations, --from street1 foo, bar, --from street2 alpha, beta From ( Select ad.gid As address_id, ad.geom As address_locations, foo.street1 As foo, bar.street1 As bar, aplha.street2 As alpha, beta.street2 As beta, St_Distance(ad.geom, street1.geom) As d From public.ad, public.street1, public.street2 Where ST_DWithin(ad.geom, street1.geom, 30.0) OR ST_DWithin(ad.geom, street1.geom, 50.0) AND ST_DWithin(ad.geom, street2.geom, 30.0) OR ST_DWithin(ad.geom, street2.geom, 50.0) Order By address_id, d ) As nested_query;
When I used only
street1, the execution time was 142 milli-seconds. When I also include
street2, the query takes much longer. The query searches for streets within 30 meters and then within 50 meters from street1 and then does the same for street2 and returns desired values from the columns. This query is running (no syntax errors) but taking a very long time (Execution time: 30 minutes and counting...).
Can someone please suggest to improve the query and consequently the execution time? Is there any alternative way to do the same job? Address table has 87 rows while both
street2 have 16,060 rows. All tables have spatial indexes. SRID of all tables has been set to be same i.e., 3044. Both streets tables are not exactly overlapping but more or less the same. They have been used because they have different columns regarding street data.