Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2160

Spatial grid index, to accelerate polygon-to-polygon spatial joins

Rank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersConvert to sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.25.0
    • spatial
    • None

    Description

      Recognize materialized views that convert a geometry into a grid of rectangles, and use such materialized views for point-to-polygon and polgyon-to-polygon joins.

      Implement the ST_MakeGrid and ST_MakeGridPoints functions.

      Add a test data set based upon western U.S. states and national parks. Given a materialized view

      CREATE MATERIALIZED VIEW StateGrids (name, x, y, PRIMARY KEY (name)) AS
      SELECT s.name, ST_XMin(g.the_geom), ST_YMin(g.the_geom)
      FROM States AS s
      CROSS APPLY TABLE(ST_MakeGrid(s.geom, 1, 1)) AS g

      and a similar materialized view ParkGrids on Parks, the query

      SELECT p.name AS park, s.name AS state
      FROM Parks AS p
      JOIN States AS s ON ST_Overlaps(s.geom, p.geom)
      ORDER BY 1, 2

      should return

      Park         State
      ============ =====
      Death Valley CA
      Death Valley NV
      Yellowstone  ID
      Yellowstone  MT
      Yellowstone  WY
      Yosemite     CA
      

      and should semi-join to the StateGrids and ParkGrids tables to reduce the size of the input before applying St_Overlaps:

      SELECT p.name AS park, s.name AS state
      FROM Parks AS p
      JOIN States AS s ON ST_Overlaps(s.geom, p.geom)
      WHERE (p.name, s.name) IN (
        SELECT DISTINCT pg.name, sg.name
        FROM ParkGrids AS pg
        JOIN StateGrids AS sg ON pg.id = sg.id)

      Note the semi-join, to remove duplicates in case a park and a state have several cells that overlap.

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            julianhyde Julian Hyde
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment