I'm currently using a PostgreSQL database with the PostGIS extension. My querys are growing to big for my inhouse server, so I would like to port this into the cloud.
The querys mainly compute the distance between a table of geometrys and a table of points, returning all points within a certain distance to the geometry.
Does any cloud DWH (BigQuery, Redshift, ...) support processing geospatial information? If not, do you guys have an idea on how to go on with my problem?
edit: As requested by @JohnPowellakaBarça I'm posting the SQL query + explain that leads to my machine crashing.
explain
select t1.osm_id as building_osm_id, t2.osm_id as poi_osm_id,
t1.centroid::geography <-> t2.geom::geography as distance
from germany_buildings_centroid t1, germany_poi_classes_transformed t2
where t1.st_x <= (t2.st_x+0.01) AND t1.st_x >= (t2.st_x-0.01)
AND t1.st_y <= (t2.st_y+0.01) AND t1.st_y >= (t2.st_y-0.01)
AND ST_DWithin(t1.centroid::geography, t2.geom::geography,1000);
Gather (cost=1000.00..48193265925353.62 rows=809798 width=24)
Workers Planned: 2
-> Nested Loop (cost=0.00..48193265722916.41 rows=337416 width=24)
Join Filter: ((t1.st_x <= (t2.st_x + '0.01'::double precision)) AND (t1.st_x >= (t2.st_x - '0.01'::double precision)) AND (t1.st_y <= (t2.st_y + '0.01'::double precision)) AND (t1.st_y >= (t2.st_y - '0.01'::double precision)) AND ((t1.centroid)::geography && _st_expand((t2.geom)::geography, '1000'::double precision)) AND ((t2.geom)::geography && _st_expand((t1.centroid)::geography, '1000'::double precision)) AND _st_dwithin((t1.centroid)::geography, (t2.geom)::geography, '1000'::double precision, true))
-> Parallel Seq Scan on germany_buildings_centroid t1 (cost=0.00..1304445.57 rows=11660458 width=56)
-> Seq Scan on germany_poi_classes_transformed t2 (cost=0.00..265650.37 rows=7031637 width=56)
Indexes on my tables:
table_name | index_name | column_name
---------------------------------+----------------------------------------+-------------
germany_buildings_centroid | idx_german_building_centroid_centroid | centroid
germany_buildings_centroid | idx_german_building_centroid_geom | geom
germany_poi_classes_transformed | idx_germany_poi_classes_transformed | geom
After changing the query:
select t1.osm_id as building_osm_id, t2.osm_id as poi_osm_id
from germany_buildings_centroid t1, germany_poi_classes_transformed t2
where ST_DWithin(t1.centroid::geography, t2.geom::geography,1000);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..46609808612882.03 rows=65674991 width=16)
Workers Planned: 2
-> Nested Loop (cost=0.00..46609802044382.93 rows=27364580 width=16)
Join Filter: (((t1.centroid)::geography && _st_expand((t2.geom)::geography, '1000'::double precision)) AND ((t2.geom)::geography && _st_expand((t1.centroid)::geography, '1000'::double precision)) AND _st_dwithin((t1.centroid)::geography, (t2.geom)::geography, '1000'::double precision, true))
-> Parallel Seq Scan on germany_buildings_centroid t1 (cost=0.00..1304522.12 rows=11668112 width=40)
-> Seq Scan on germany_poi_classes_transformed t2 (cost=0.00..265691.35 rows=7035735 width=40)
