You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Andrei Sereda (Jira)" <ji...@apache.org> on 2020/08/22 18:49:03 UTC
[jira] [Closed] (CALCITE-2160) Spatial grid index, to accelerate
polygon-to-polygon spatial joins
[ https://issues.apache.org/jira/browse/CALCITE-2160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrei Sereda closed CALCITE-2160.
----------------------------------
Released as part of 1.25 ([68b02dfd4af15bc|https://github.com/apache/calcite/commit/68b02dfd4af15bc94a91a0cd2a30655d04439555])
> Spatial grid index, to accelerate polygon-to-polygon spatial joins
> ------------------------------------------------------------------
>
> Key: CALCITE-2160
> URL: https://issues.apache.org/jira/browse/CALCITE-2160
> Project: Calcite
> Issue Type: Bug
> Components: spatial
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.25.0
>
>
> 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
(v8.3.4#803005)