title: How to find intersecting geographies between two tables recursively
body: <p>I'm running Postgres 9.6.1 and PostGIS 2.3.0 r15146 and have two tables.<br>
  <code>geographies</code> may have 150,000,000 rows, <code>paths</code> may have 10,000,000 rows:</p>
  
  <pre><code>CREATE TABLE paths (id uuid NOT NULL, path path NOT NULL, PRIMARY KEY (id))
  CREATE TABLE geographies (id uuid NOT NULL, geography geography NOT NULL, PRIMARY KEY (id))
  </code></pre>
  
  <p>Given an array/set of <code>ids</code> for table <code>geographies</code>, what is the "best" way of finding all intersecting paths and geometries?</p>
  
  <p>In other words, if an initial <code>geography</code> has a corresponding intersecting <code>path</code> we need to also find all <em>other</em> <code>geographies</code> that this <code>path</code> intersects. From there, we need to find all other <code>paths</code> that these newly found <code>geographies</code> intersect, and so on until we've found all possible intersections.</p>
  
  <p>The initial geography ids (our input) may be anywhere from 0 to 700. With an  average around 40.<br>
  Minimum intersections will be 0, max will be about 1000. Average likely around 20, typically less than 100 connected.</p>
  
  <p>I've created a function that does this, but I'm new to GIS in PostGIS, and Postgres in general. I've posted <a href="https://stackoverflow.com/questions/41899565/how-to-find-all-connected-geospatial-intersections-with-postgis/42032466#42032466">my solution as an answer to this question</a>.</p>
  
  <p>I feel like there should be a more eloquent and faster way of doing this than what I've come up with.</p>