I have two tables in a PostGIS database:
rides, containing about 3 million datasets:
start_geom(point geometry with index in 4326)
end_geom(point geometry with index in 4326)
- a lot more stuff
rides table also has some invalid geometries in it, namely
NULL values in
end_geom or non-valid geometries laying somewhere near north pole or sahara dessert. So I'd check for that using
where ST_Within(st.start_geom,ST_MakeEnvelope(5, 35, 20, 55, 4326)) to prevent a reprojection error.
polygons, containing about 30 datasets:
geometry(multipolygons with index in 25832)
a lot more stuff
I want to figure out the start's and end's for all polygon's and a given range of years. I also want to consider 0 rides in a year/polygon. E.g. as result:
I am struggling to find an efficient query to achieve my goal in one go. This is what I got so far; It returns my desired result for only starts or only ends in about ~4-5 minutes (which is acceptable).
select hlpr.uid_key, hlpr.name, hlpr.y, coalesce(count(s.ride_uid),0) as cnt_start --, --coalesce(count(e.ride_uid),0) as cnt_end -- takes forever with this at the same time from ( select poly.uid_key, poly.name, cal.y, poly.geom from schema_b.polygons as poly cross join ( select cast(generate_series(2015,2021) as integer) as y ) as cal ) as hlpr left join ( select st.ride_uid, st.start_date_year, st.start_geom, pol.uid_key from schema_a.rides as st left join ( select uid_key, geom from schema_b.polygons ) as pol on ST_Intersects(ST_Transform(st.start_geom,25832),pol.geom) where ST_Within(st.start_geom,ST_MakeEnvelope(5, 35, 20, 55, 4326)) ) as s on s.start_date_year = hlpr.y and s.uid_key = hlpr.uid_key -- takes forever with this: /* left join ( select en.ride_uid, en.end_date_year, en.end_geom, pol.uid_key from schema_a.rides as en left join ( select uid_key, geom from schema_b.polygons ) as pol on ST_Intersects(ST_Transform(en.end_geom,25832),pol.geom) where ST_Within(en.end_geom,ST_MakeEnvelope(5, 35, 20, 55, 4326)) ) as e on e.end_date_year = hlpr.y and e.uid_key = hlpr.uid_key */ group by hlpr.uid_key, hlpr.name, hlpr.y order by hlpr.uid_key, hlpr.y
Can I optimize this query somehow to get my desired result faster in one go? Or is this an unusual approach that should be avoided at all and instead only one count be done per query and the results merged manually afterwards?