[jira] [Created] (CALCITE-2160) Spatial grid index

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-2160:
------------------------------------

             Summary: Spatial grid index
                 Key: CALCITE-2160
                 URL: https://issues.apache.org/jira/browse/CALCITE-2160
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde
            Assignee: Julian Hyde


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|http://www.h2gis.org/docs/dev/ST_MakeGrid] and [ST_MakeGridPoints|http://www.h2gis.org/docs/dev/ST_MakeGridPoints] functions.

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

{code}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{code}

and a similar materialized view ParkGrids on Parks, the query

{code}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{code}

should return

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

and should semi-join to the {{StateGrids}} and {{ParkGrids}} tables to reduce the size of the input before applying St_Overlaps: {code}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){code}

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



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)