Search code examples
sqlgoogle-bigquerypostgisgeography

How to find all roadway intersections from a large dataset of linestrings stored in BigQuery

I have 40,000 road linestrings stored in a BigQuery table and I want to find all of the unique intersections for them. I found out how to do this in postGIS here (https://gis.stackexchange.com/questions/20835/identifying-road-intersections-using-postgis/151277#151277) but I can't use this code in BigQuery because I can't group by a geography.

SELECT      
    ST_Intersection(a.geom, b.geom),
    Count(Distinct a.gid)
FROM
    roads as a,
    roads as b
WHERE
    ST_Touches(a.geom, b.geom)
    AND a.gid != b.gid
GROUP BY
    ST_Intersection(a.geom, b.geom)



  [1]: https://gis.stackexchange.com/questions/20835/identifying-road-intersections-using-postgis/151277#151277
Solution

  • I'm missing why you need to aggregate the results:

    SELECT ST_Intersection(r1.geom, r2.geom)
FROM roads r1 JOIN
     roads r2
     ON ST_Touches(r1.geom, r2.geom) AND
        r1.gid < r2.gid