Basically have 2 tables. 1 is buildings (1809555 rows called oregon). Another is flood zone (164 rows called oregonslr1). Want to see where they intersect to get number of buildings flooded. I am doing:

FROM oregon, oregonslr1
WHERE ST_Intersects(oregonslr1.geom, oregon.wkb_geometry);

I want a count of the geometry in oregon that intersects with the oregonslr1 geom. Problem is it is taking forever. Ran for like 90 minutes got nowhere. The reason I switched to PostGIS was because this data was too big to work with in QGIS. I'm wondering if I am going about this wrong or need to make so optimizations because I may have to scale this for many more states and it would be incredibly time consuming at the rate it is going? Also using pgAdmin, not sure if makes slower....

Added results from EXPLAIN, couldn't run explain analyze too long Added results from EXPLAIN, couldn't run explain analyze too long

Subdivided stuff with this command:

create table oregonslr2_sub as select st_subdivide(geom, 25) as geom from oregonslr2;

  • sometimes the problem may be that you are not working with a simple type (polygon), but with a complex type (multipolygon), check it too ... – Cyril Mar 12 at 10:43
  • 3
    It is best practice to always use a JOIN on your query, with the smaller table in the FROM. You should always include the query plan in any question about query performance -- see EXPLAIN. – Vince Mar 12 at 10:54

ST_intersects is generally considered a fast option but see PostGIS docs on getting intersections faster for example queries. ST_within is another option to try as well as per the above link especially in conjunction with using building centroids per Steven Kay's suggestion (less geometry to consider).

However, all this said, there are a number of other possible factors that are not related to the syntax of your query but to the setup of Postgres and your database, which might be contributing significantly to the slowness:

  • You need to have a spatial index on your tables (you don't mention indexes and the output of EXPLAIN as Vince suggests in his comment would be revealing here and help refine answers)
  • Spatial clustering of your data based on the spatial index can help where you have a very large amount of data (reduces look-up times - clustering can be slow but is there for all subsequent queries and you'd probably only need to cluster your buildings)
  • You need to tune Postgres to work with spatial data and not just use it out-of-the-box. There's a lot of information on this out there but see here for starters

These three factors together can increase the speed of many queries by orders of magnitude, especially on large tables.

  • 1
    Spatial index is only required on the JOINed table, not both. – Vince Mar 12 at 14:32
  • ( im op) So I created the indicies and made some adjustments to the settings(postgis.net/workshops/postgis-intro/tuning.html) and unfortunately it is still slow af. Gave up after 30+ minutes, don't feel like it should be this long. To create the indicies I used CREATE INDEX oregon_geom_index ON oregon USING gist (wkb_geometry); and CREATE INDEX oregonslr1_geom_index ON oregonslr1 USING gist (geom); Do I need to activate the indices or explicitly use them in my query somehow or can I use the same query I used in my post b/c looking at examples online, don't seem like it? – user10271755 Mar 12 at 22:45
  • @user10271755 try running VACUUM ANALYZE <tablename> on both tables – ThingumaBob Mar 13 at 2:02

There are a couple of techniques you can use to speed up this query. you can use either one, or both together.

  • test overlap of the building centroids rather than polygons. this may be faster, it also stops any buildings being double-counted if they fall into two areas - which may be good or bad, depending on your requirements.
  • use st_subdivide on your flood zones. it seems counter-intuitive that splitting your zones into lots of new rows would speed things up, but by using smaller pieces it makes the spatial index work much better - especially if your flood zones are large and have complex boundaries.

One thing I like to do when working with big tables like this while trying to figure out the right query is to create a smaller table to test the query on first.

for example:

--drop table if exists test;

create table test as 
select * from oregon
limit 500;

then do some query fandangling with explain analyze to figure out which query is fastest for your situation

and then remove the test table

drop table if exists test;

  • 1
    don't...for 500 rows a seq scan is likely the fastest lookup, for 500k it possibly is the index; the planner will choose completely different approaches based on the table size! – ThingumaBob Mar 26 at 20:15

If you consider using other systems for this, I would try BigQuery GIS. Given the number of polygons, I think it should be able to handle this join in reasonable time. Follow the BigQuery GIS performance hints in this blog, but you don't need any index (nor does BigQuery allow explicit indexes).

Your Answer

