4

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)
  • 1
    I think you will need to be a lot more specific about what kind of queries you are running before anyone can intelligently answer that. – John Powell Jul 19 '18 at 12:51
  • Okay, I'm sorry. I added this information to the post. – Ogofo Jul 19 '18 at 12:57
  • right now I run postgres with postgis on a windows go daddy server and there is wicked lack connecting to it and running queries – ziggy Jul 19 '18 at 12:59
  • 1
    How big are your tables? You might be able to optimise what you have rather than going to the cloud. You can,of course, run Postgres in the cloud. There is also geomesa, which supports a lot of functionality and sits on top of BigTable, Cassandra, etc – John Powell Jul 19 '18 at 13:13
  • 2
    That doesn't seem so big. It may well be that you just need some optimizations. Could you post an example query and the Explain output. I'm not against the cloud, just trying to avoid unnecessary time and money – John Powell Jul 19 '18 at 13:26
2

AWS Athena supports some GIS functionality: https://docs.aws.amazon.com/athena/latest/ug/querying-geospatial-data.html

BigQuery just announced Alpha of its GIS functionality at GCP Next '18. The announcement and link to Alpha signup form is here: https://cloud.google.com/blog/products/gcp/bridging-the-gap-between-data-and-insights They also blogged about a task solved using similar ST_DWithin join: https://towardsdatascience.com/using-bigquerys-new-geospatial-functions-to-interpolate-temperatures-d9363ff19446

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.