This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 885a3da76cfd59171624dc569e83e93a5bdaffe3 Author: Julian Hyde <[email protected]> AuthorDate: Tue Jul 21 14:18:37 2020 -0700 [CALCITE-2160] Spatial: Add functions ST_MakeGrid and ST_MakeGridPoints These will be the foundations of a spatial grid index, to accelerate polygon-to-polygon spatial joins. Add "states" and "parks" data sets. Fix lateral references to fields. --- .../calcite/prepare/CalciteCatalogReader.java | 11 +- .../org/apache/calcite/runtime/GeoFunctions.java | 96 ++++++++++++ .../apache/calcite/sql/fun/SqlGeoFunctions.java | 134 ++++++++++++++++ .../sql/fun/SqlLibraryOperatorTableFactory.java | 4 +- .../org/apache/calcite/test/CalciteAssert.java | 35 ++++- .../apache/calcite/test/StatesTableFunction.java | 121 +++++++++++++++ core/src/test/resources/sql/spatial.iq | 96 +++++++++++- file/src/test/resources/geo/states.json | 168 +++++++++++++++++++++ site/_docs/reference.md | 8 +- 9 files changed, 656 insertions(+), 17 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java index 74fe767..f77679c 100644 --- a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java +++ b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java @@ -272,15 +272,18 @@ public class CalciteCatalogReader implements Prepare.CatalogReader { .forEachOrdered(operatorList::add); } - /** Creates an operator table that contains functions in the given class. + /** Creates an operator table that contains functions in the given class + * or classes. * * @see ModelHandler#addFunctions */ - public static SqlOperatorTable operatorTable(String className) { + public static SqlOperatorTable operatorTable(String... classNames) { // Dummy schema to collect the functions final CalciteSchema schema = CalciteSchema.createRootSchema(false, false); - ModelHandler.addFunctions(schema.plus(), null, ImmutableList.of(), - className, "*", true); + for (String className : classNames) { + ModelHandler.addFunctions(schema.plus(), null, ImmutableList.of(), + className, "*", true); + } // The following is technical debt; see [CALCITE-2082] Remove // RelDataTypeFactory argument from SqlUserDefinedAggFunction constructor diff --git a/core/src/main/java/org/apache/calcite/runtime/GeoFunctions.java b/core/src/main/java/org/apache/calcite/runtime/GeoFunctions.java index 1918436..9732a0c 100644 --- a/core/src/main/java/org/apache/calcite/runtime/GeoFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/GeoFunctions.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.runtime; +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerator; import org.apache.calcite.linq4j.function.Deterministic; import org.apache.calcite.linq4j.function.Experimental; import org.apache.calcite.linq4j.function.SemiStrict; @@ -179,6 +181,22 @@ public class GeoFunctions { // Geometry creation functions ============================================== + /** Calculates a regular grid of polygons based on {@code geom}. */ + private static void ST_MakeGrid(final Geom geom, + final BigDecimal deltaX, final BigDecimal deltaY) { + // This is a dummy function. We cannot include table functions in this + // package, because they have too many dependencies. See the real definition + // in SqlGeoFunctions. + } + + /** Calculates a regular grid of points based on {@code geom}. */ + private static void ST_MakeGridPoints(final Geom geom, + final BigDecimal deltaX, final BigDecimal deltaY) { + // This is a dummy function. We cannot include table functions in this + // package, because they have too many dependencies. See the real definition + // in SqlGeoFunctions. + } + /** Creates a line-string from the given POINTs (or MULTIPOINTs). */ public static Geom ST_MakeLine(Geom geom1, Geom geom2) { return makeLine(geom1, geom2); @@ -655,4 +673,82 @@ public class GeoFunctions { this.code = code; } } + + /** Used at run time by the {@link #ST_MakeGrid} and + * {@link #ST_MakeGridPoints} functions. */ + public static class GridEnumerable extends AbstractEnumerable<Object[]> { + private final Envelope envelope; + private final boolean point; + private final double deltaX; + private final double deltaY; + private final double minX; + private final double minY; + private final int baseX; + private final int baseY; + private final int spanX; + private final int spanY; + private final int area; + + public GridEnumerable(Envelope envelope, BigDecimal deltaX, + BigDecimal deltaY, boolean point) { + this.envelope = envelope; + this.deltaX = deltaX.doubleValue(); + this.deltaY = deltaY.doubleValue(); + this.point = point; + this.spanX = (int) Math.floor((envelope.getXMax() - envelope.getXMin()) + / this.deltaX) + 1; + this.baseX = (int) Math.floor(envelope.getXMin() / this.deltaX); + this.minX = this.deltaX * baseX; + this.spanY = (int) Math.floor((envelope.getYMax() - envelope.getYMin()) + / this.deltaY) + 1; + this.baseY = (int) Math.floor(envelope.getYMin() / this.deltaY); + this.minY = this.deltaY * baseY; + this.area = this.spanX * this.spanY; + } + + @Override public Enumerator<Object[]> enumerator() { + return new Enumerator<Object[]>() { + int id = -1; + + @Override public Object[] current() { + final Geom geom; + final int x = id % spanX; + final int y = id / spanX; + if (point) { + final double xCurrent = minX + (x + 0.5D) * deltaX; + final double yCurrent = minY + (y + 0.5D) * deltaY; + geom = ST_MakePoint(BigDecimal.valueOf(xCurrent), + BigDecimal.valueOf(yCurrent)); + } else { + final Polygon polygon = new Polygon(); + final double left = minX + x * deltaX; + final double right = left + deltaX; + final double bottom = minY + y * deltaY; + final double top = bottom + deltaY; + + final Polyline polyline = new Polyline(); + polyline.addSegment(new Line(left, bottom, right, bottom), true); + polyline.addSegment(new Line(right, bottom, right, top), false); + polyline.addSegment(new Line(right, top, left, top), false); + polyline.addSegment(new Line(left, top, left, bottom), false); + polygon.add(polyline, false); + geom = new SimpleGeom(polygon); + } + return new Object[] {geom, id, x + 1, y + 1, baseX + x, baseY + y}; + } + + public boolean moveNext() { + return ++id < area; + } + + public void reset() { + id = -1; + } + + public void close() { + } + }; + } + } + } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGeoFunctions.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGeoFunctions.java new file mode 100644 index 0000000..dd4a411 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGeoFunctions.java @@ -0,0 +1,134 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.sql.fun; + +import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Linq4j; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.runtime.GeoFunctions; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; + +import com.esri.core.geometry.Envelope; +import com.esri.core.geometry.Geometry; +import com.google.common.collect.ImmutableList; + +import java.math.BigDecimal; + +/** + * Utilities for Geo/Spatial functions. + * + * <p>Includes some table functions, and may in future include other functions + * that have dependencies beyond the {@code org.apache.calcite.runtime} package. + */ +public class SqlGeoFunctions { + private SqlGeoFunctions() {} + + // Geometry table functions ================================================= + + /** Calculates a regular grid of polygons based on {@code geom}. + * + * @see GeoFunctions#ST_MakeGrid */ + @SuppressWarnings({"WeakerAccess", "unused"}) + public static ScannableTable ST_MakeGrid(final GeoFunctions.Geom geom, + final BigDecimal deltaX, final BigDecimal deltaY) { + return new GridTable(geom, deltaX, deltaY, false); + } + + /** Calculates a regular grid of points based on {@code geom}. + * + * @see GeoFunctions#ST_MakeGridPoints */ + @SuppressWarnings({"WeakerAccess", "unused"}) + public static ScannableTable ST_MakeGridPoints(final GeoFunctions.Geom geom, + final BigDecimal deltaX, final BigDecimal deltaY) { + return new GridTable(geom, deltaX, deltaY, true); + } + + /** Returns the points or rectangles in a grid that covers a given + * geometry. */ + public static class GridTable implements ScannableTable { + private final GeoFunctions.Geom geom; + private final BigDecimal deltaX; + private final BigDecimal deltaY; + private boolean point; + + GridTable(GeoFunctions.Geom geom, BigDecimal deltaX, BigDecimal deltaY, + boolean point) { + this.geom = geom; + this.deltaX = deltaX; + this.deltaY = deltaY; + this.point = point; + } + + @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + // a point (for ST_MakeGridPoints) or a rectangle (for ST_MakeGrid) + .add("THE_GEOM", SqlTypeName.GEOMETRY) + // in [0, width * height) + .add("ID", SqlTypeName.INTEGER) + // in [1, width] + .add("ID_COL", SqlTypeName.INTEGER) + // in [1, height] + .add("ID_ROW", SqlTypeName.INTEGER) + // absolute column, with 0 somewhere near the origin; not standard + .add("ABS_COL", SqlTypeName.INTEGER) + // absolute row, with 0 somewhere near the origin; not standard + .add("ABS_ROW", SqlTypeName.INTEGER) + .build(); + } + + public Enumerable<Object[]> scan(DataContext root) { + if (geom != null && deltaX != null && deltaY != null) { + final Geometry geometry = geom.g(); + final Envelope envelope = new Envelope(); + geometry.queryEnvelope(envelope); + if (deltaX.compareTo(BigDecimal.ZERO) > 0 + && deltaY.compareTo(BigDecimal.ZERO) > 0) { + return new GeoFunctions.GridEnumerable(envelope, deltaX, deltaY, + point); + } + } + return Linq4j.emptyEnumerable(); + } + + public Statistic getStatistic() { + return Statistics.of(100d, ImmutableList.of(ImmutableBitSet.of(0, 1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.OTHER; + } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return false; + } + } +} diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java index 85dec50..0444d16 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java @@ -86,8 +86,8 @@ public class SqlLibraryOperatorTableFactory { break; case SPATIAL: list.addAll( - CalciteCatalogReader.operatorTable(GeoFunctions.class.getName()) - .getOperatorList()); + CalciteCatalogReader.operatorTable(GeoFunctions.class.getName(), + SqlGeoFunctions.class.getName()).getOperatorList()); break; default: custom = true; diff --git a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java index 26e96f6..a801c05 100644 --- a/core/src/test/java/org/apache/calcite/test/CalciteAssert.java +++ b/core/src/test/java/org/apache/calcite/test/CalciteAssert.java @@ -53,6 +53,7 @@ import org.apache.calcite.schema.impl.ViewTable; import org.apache.calcite.schema.impl.ViewTableMacro; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlExplainLevel; +import org.apache.calcite.sql.fun.SqlGeoFunctions; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.validate.SqlConformanceEnum; import org.apache.calcite.sql.validate.SqlValidatorException; @@ -750,6 +751,7 @@ public class CalciteAssert { final SchemaPlus scott; final ConnectionSpec cs; final DataSource dataSource; + final ImmutableList<String> emptyPath = ImmutableList.of(); switch (schema) { case REFLECTIVE_FOODMART: return rootSchema.add(schema.schemaName, @@ -793,16 +795,37 @@ public class CalciteAssert { foodmart = addSchemaIfNotExists(rootSchema, SchemaSpec.JDBC_FOODMART); return rootSchema.add("foodmart2", new CloneSchema(foodmart)); case GEO: - ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(), + ModelHandler.addFunctions(rootSchema, null, emptyPath, GeoFunctions.class.getName(), "*", true); + ModelHandler.addFunctions(rootSchema, null, emptyPath, + SqlGeoFunctions.class.getName(), "*", true); final SchemaPlus s = rootSchema.add(schema.schemaName, new AbstractSchema()); - ModelHandler.addFunctions(s, "countries", ImmutableList.of(), + ModelHandler.addFunctions(s, "countries", emptyPath, CountriesTableFunction.class.getName(), null, false); final String sql = "select * from table(\"countries\"(true))"; final ViewTableMacro viewMacro = ViewTable.viewMacro(rootSchema, sql, - ImmutableList.of("GEO"), ImmutableList.of(), false); + ImmutableList.of("GEO"), emptyPath, false); s.add("countries", viewMacro); + + ModelHandler.addFunctions(s, "states", emptyPath, + StatesTableFunction.class.getName(), "states", false); + final String sql2 = "select \"name\",\n" + + " ST_PolyFromText(\"geom\") as \"geom\"\n" + + "from table(\"states\"(true))"; + final ViewTableMacro viewMacro2 = ViewTable.viewMacro(rootSchema, sql2, + ImmutableList.of("GEO"), emptyPath, false); + s.add("states", viewMacro2); + + ModelHandler.addFunctions(s, "parks", emptyPath, + StatesTableFunction.class.getName(), "parks", false); + final String sql3 = "select \"name\",\n" + + " ST_PolyFromText(\"geom\") as \"geom\"\n" + + "from table(\"parks\"(true))"; + final ViewTableMacro viewMacro3 = ViewTable.viewMacro(rootSchema, sql3, + ImmutableList.of("GEO"), emptyPath, false); + s.add("parks", viewMacro3); + return s; case HR: return rootSchema.add(schema.schemaName, @@ -835,7 +858,7 @@ public class CalciteAssert { + " ('Grace', 60, 'F'),\n" + " ('Wilma', cast(null as integer), 'F'))\n" + " as t(ename, deptno, gender)", - ImmutableList.of(), ImmutableList.of("POST", "EMP"), + emptyPath, ImmutableList.of("POST", "EMP"), null)); post.add("DEPT", ViewTable.viewMacro(post, @@ -844,7 +867,7 @@ public class CalciteAssert { + " (20, 'Marketing'),\n" + " (30, 'Engineering'),\n" + " (40, 'Empty')) as t(deptno, dname)", - ImmutableList.of(), ImmutableList.of("POST", "DEPT"), + emptyPath, ImmutableList.of("POST", "DEPT"), null)); post.add("DEPT30", ViewTable.viewMacro(post, @@ -860,7 +883,7 @@ public class CalciteAssert { + " (120, 'Wilma', 20, 'F', CAST(NULL AS VARCHAR(20)), 1, 5, UNKNOWN, TRUE, DATE '2005-09-07'),\n" + " (130, 'Alice', 40, 'F', 'Vancouver', 2, CAST(NULL AS INT), FALSE, TRUE, DATE '2007-01-01'))\n" + " as t(empno, name, deptno, gender, city, empid, age, slacker, manager, joinedat)", - ImmutableList.of(), ImmutableList.of("POST", "EMPS"), + emptyPath, ImmutableList.of("POST", "EMPS"), null)); post.add("TICKER", ViewTable.viewMacro(post, diff --git a/core/src/test/java/org/apache/calcite/test/StatesTableFunction.java b/core/src/test/java/org/apache/calcite/test/StatesTableFunction.java new file mode 100644 index 0000000..2d72fd0 --- /dev/null +++ b/core/src/test/java/org/apache/calcite/test/StatesTableFunction.java @@ -0,0 +1,121 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.test; + +import org.apache.calcite.DataContext; +import org.apache.calcite.config.CalciteConnectionConfig; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Linq4j; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; + +/** A table function that returns states and their boundaries; also national + * parks. + * + * <p>Has same content as + * <code>file/src/test/resources/geo/states.json</code>. */ +public class StatesTableFunction { + private StatesTableFunction() {} + + private static final Object[][] STATE_ROWS = { + {"NV", "Polygon((-120 42, -114 42, -114 37, -114.75 35.1, -120 39," + + " -120 42))"}, + {"UT", "Polygon((-114 42, -111.05 42, -111.05 41, -109.05 41, -109.05 37," + + " -114 37, -114 42))"}, + {"CA", "Polygon((-124.25 42, -120 42, -120 39, -114.75 35.1," + + " -114.75 32.5, -117.15 32.5, -118.30 33.75, -120.5 34.5," + + " -122.4 37.2, -124.25 42))"}, + {"AZ", "Polygon((-114 37, -109.05 37, -109.05 31.33, -111.07 31.33," + + " -114.75 32.5, -114.75 35.1, -114 37))"}, + {"CO", "Polygon((-109.05 41, -102 41, -102 37, -109.05 37, -109.05 41))"}, + {"OR", "Polygon((-123.9 46.2, -122.7 45.7, -119 46, -117 46, -116.5 45.5," + + " -117.03 44.2, -117.03 42, -124.25 42, -124.6 42.8," + + " -123.9 46.2))"}, + {"WA", "Polygon((-124.80 48.4, -123.2 48.2, -123.2 49, -117 49, -117 46," + + " -119 46, -122.7 45.7, -123.9 46.2, -124.80 48.4))"}, + {"ID", "Polygon((-117 49, -116.05 49, -116.05 48, -114.4 46.6," + + " -112.9 44.45, -111.05 44.45, -111.05 42, -117.03 42," + + " -117.03 44.2, -116.5 45.5, -117 46, -117 49))"}, + {"MT", "Polygon((-116.05 49, -104.05 49, -104.05 45, -111.05 45," + + " -111.05 44.45, -112.9 44.45, -114.4 46.6, -116.05 48," + + " -116.05 49))"}, + {"WY", "Polygon((-111.05 45, -104.05 45, -104.05 41, -111.05 41," + + " -111.05 45))"}, + {"NM", "Polygon((-109.05 37, -103 37, -103 32, -106.65 32, -106.5 31.8," + + " -108.2 31.8, -108.2 31.33, -109.05 31.33, -109.05 37))"} + }; + + private static final Object[][] PARK_ROWS = { + {"Yellowstone NP", "Polygon((-111.2 45.1, -109.30 45.1, -109.30 44.1," + + " -109 43.8, -110 43, -111.2 43.4, -111.2 45.1))"}, + {"Yosemite NP", "Polygon((-120.2 38, -119.30 38.2, -119 37.7," + + " -119.9 37.6, -120.2 38))"}, + {"Death Valley NP", "Polygon((-118.2 37.3, -117 37, -116.3 35.7," + + " -117 35.7, -117.2 36.2, -117.8 36.4, -118.2 37.3))"}, + }; + + public static ScannableTable states(boolean b) { + return eval(STATE_ROWS); + }; + + public static ScannableTable parks(boolean b) { + return eval(PARK_ROWS); + }; + + private static ScannableTable eval(final Object[][] rows) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + return Linq4j.asEnumerable(rows); + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("name", SqlTypeName.VARCHAR) + .add("geom", SqlTypeName.VARCHAR) + .build(); + } + + public Statistic getStatistic() { + return Statistics.of(rows.length, + ImmutableList.of(ImmutableBitSet.of(0))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + + public boolean isRolledUp(String column) { + return false; + } + + public boolean rolledUpColumnValidInsideAgg(String column, SqlCall call, + SqlNode parent, CalciteConnectionConfig config) { + return false; + } + }; + } +} diff --git a/core/src/test/resources/sql/spatial.iq b/core/src/test/resources/sql/spatial.iq index 67e1d46..a9aac81 100644 --- a/core/src/test/resources/sql/spatial.iq +++ b/core/src/test/resources/sql/spatial.iq @@ -24,6 +24,16 @@ C 245 !ok +select count(*) as c from GEO."states"; +C +11 +!ok + +select count(*) as c from GEO."parks"; +C +3 +!ok + #### Geometry conversion functions (2D) # ST_AsBinary(geom) Geometry to Well Known Binary @@ -258,10 +268,28 @@ EXPR$0 # Not implemented # ST_MakeGrid(geom, deltaX, deltaY) Calculates a regular grid of polygons based on *geom* -# Not implemented +SELECT * FROM TABLE(ST_MakeGrid(ST_Point(13.0,22.0), 10.0, 5.0)); +THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW +{"rings":[[[10,20],[20,20],[20,25],[10,25],[10,20]]]}, 0, 1, 1, 1, 4 +!ok # ST_MakeGridPoints(geom, deltaX, deltaY) Calculates a regular grid of points based on *geom* -# Not implemented +SELECT * FROM TABLE(ST_MakeGridPoints(ST_Point(13.0,22.0), 10.0, 5.0)); +THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW +{"x":15,"y":22.5}, 0, 1, 1, 1, 4 +!ok + +# Call ST_MakeGridPoints for each geometry in a set +select "name", "latitude", "longitude", p.* +from GEO."countries" AS c, + lateral table( + ST_MakeGridPoints(ST_MakePoint("latitude", "longitude"), 10.0, 10.0)) as p +ORDER BY "latitude" DESC LIMIT 3; +name, latitude, longitude, THE_GEOM, ID, ID_COL, ID_ROW, ABS_COL, ABS_ROW +Svalbard and Jan Mayen, 77.553604, 23.670272, {"x":75,"y":25}, 0, 1, 1, 7, 2 +Greenland, 71.706936, -42.604303, {"x":75,"y":-45}, 0, 1, 1, 7, -5 +Iceland, 64.963051, -19.020835, {"x":65,"y":-15}, 0, 1, 1, 6, -2 +!ok # ST_MakeLine(point1 [, point ]*) Creates a line-string from the given points (or multi-points) @@ -1143,4 +1171,68 @@ POINT Z (-2 3 1) POINT Z (5 5 5) !ok +# Polygon-to-polygon joins +select * +from GEO."states" as s +order by "name"; +name, geom +AZ, {"rings":[[[-114,37],[-109.05,37],[-109.05,31.33],[-111.07,31.33],[-114.75,32.5],[-114.75,35.1],[-114,37]]]} +CA, {"rings":[[[-124.25,42],[-120,42],[-120,39],[-114.75,35.1],[-114.75,32.5],[-117.15,32.5],[-118.3,33.75],[-120.5,34.5],[-122.4,37.2],[-124.25,42]]]} +CO, {"rings":[[[-109.05,41],[-102,41],[-102,37],[-109.05,37],[-109.05,41]]]} +ID, {"rings":[[[-117,49],[-116.05,49],[-116.05,48],[-114.4,46.6],[-112.9,44.45],[-111.05,44.45],[-111.05,42],[-117.03,42],[-117.03,44.2],[-116.5,45.5],[-117,46],[-117,49]]]} +MT, {"rings":[[[-116.05,49],[-104.05,49],[-104.05,45],[-111.05,45],[-111.05,44.45],[-112.9,44.45],[-114.4,46.6],[-116.05,48],[-116.05,49]]]} +NM, {"rings":[[[-109.05,37],[-103,37],[-103,32],[-106.65,32],[-106.5,31.8],[-108.2,31.8],[-108.2,31.33],[-109.05,31.33],[-109.05,37]]]} +NV, {"rings":[[[-120,42],[-114,42],[-114,37],[-114.75,35.1],[-120,39],[-120,42]]]} +OR, {"rings":[[[-123.9,46.2],[-122.7,45.7],[-119,46],[-117,46],[-116.5,45.5],[-117.03,44.2],[-117.03,42],[-124.25,42],[-124.6,42.8],[-123.9,46.2]]]} +UT, {"rings":[[[-114,42],[-111.05,42],[-111.05,41],[-109.05,41],[-109.05,37],[-114,37],[-114,42]]]} +WA, {"rings":[[[-124.8,48.4],[-123.2,48.2],[-123.2,49],[-117,49],[-117,46],[-119,46],[-122.7,45.7],[-123.9,46.2],[-124.8,48.4]]]} +WY, {"rings":[[[-111.05,45],[-104.05,45],[-104.05,41],[-111.05,41],[-111.05,45]]]} +!ok + +select * +from GEO."parks" as p +order by "name"; +name, geom +Death Valley NP, {"rings":[[[-118.2,37.3],[-117,37],[-116.3,35.7],[-117,35.7],[-117.2,36.2],[-117.8,36.4],[-118.2,37.3]]]} +Yellowstone NP, {"rings":[[[-111.2,45.1],[-109.3,45.1],[-109.3,44.1],[-109,43.8],[-110,43],[-111.2,43.4],[-111.2,45.1]]]} +Yosemite NP, {"rings":[[[-120.2,38],[-119.3,38.2],[-119,37.7],[-119.9,37.6],[-120.2,38]]]} +!ok + +# Parks that may intersect states +select s."name", p."name" +from GEO."states" as s +cross apply table(ST_MakeGrid(s."geom", 5.0, 5.0)) as sg, + GEO."parks" as p +cross apply table(ST_MakeGrid(p."geom", 5.0, 5.0)) as pg +where (sg.abs_col, sg.abs_row) = (pg.abs_col, pg.abs_row) +order by 2, 1; +name, name +CA, Death Valley NP +NV, Death Valley NP +ID, Yellowstone NP +MT, Yellowstone NP +NV, Yellowstone NP +UT, Yellowstone NP +WY, Yellowstone NP +CA, Yosemite NP +!ok + +# Parks that intersect states +select s."name", p."name" +from GEO."states" as s +cross apply table(ST_MakeGrid(s."geom", 5.0, 5.0)) as sg, + GEO."parks" as p +cross apply table(ST_MakeGrid(p."geom", 5.0, 5.0)) as pg +where (sg.abs_col, sg.abs_row) = (pg.abs_col, pg.abs_row) +and ST_Intersects(s."geom", p."geom") +order by 2, 1; +name, name +CA, Death Valley NP +NV, Death Valley NP +ID, Yellowstone NP +MT, Yellowstone NP +WY, Yellowstone NP +CA, Yosemite NP +!ok + # End spatial.iq diff --git a/file/src/test/resources/geo/states.json b/file/src/test/resources/geo/states.json new file mode 100644 index 0000000..0a32aa0 --- /dev/null +++ b/file/src/test/resources/geo/states.json @@ -0,0 +1,168 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * GeoJSON document containing 11 western states and 3 national parks; + * some of the parks cross state boundaries. + * Created using Simple GeoJSON editor, representing most regions as polygons + * with fewer than 10 points, then rounded coordinates to 1/10 or 1/100 degree, + * and aligned the borders of neighboring states. + */ +{ + "type": "FeatureCollection", + "features": [ + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-120, 42], [-114, 42], [-114, 37], [-114.75, 35.1], [-120, 39], [-120, 42]]] + }, + "properties": { + "name": "NV" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-114, 42], [-111.05, 42], [-111.05, 41], [-109.05, 41], [-109.05, 37], [-114, 37], [-114, 42]]] + }, + "properties": { + "name": "UT" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-124.25, 42], [-120, 42], [-120, 39], [-114.75, 35.1], [-114.75, 32.5], [-117.15, 32.5], [-118.30, 33.75], [-120.5, 34.5], [-122.4, 37.2], [-124.25, 42]]] + }, + "properties": { + "name": "CA" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-114, 37], [-109.05, 37], [-109.05, 31.33], [-111.07, 31.33], [-114.75, 32.5], [-114.75, 35.1], [-114, 37]]] + }, + "properties": { + "name": "AZ" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-109.05, 41], [-102, 41], [-102, 37], [-109.05, 37], [-109.05, 41]]] + }, + "properties": { + "name": "CO" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-123.9, 46.2], [-122.7, 45.7], [-119, 46], [-117, 46], [-116.5, 45.5], [-117.03, 44.2], [-117.03, 42], [-124.25, 42], [-124.6, 42.8], [-123.9, 46.2]]] + }, + "properties": { + "name": "OR" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-124.80, 48.4], [-123.2, 48.2], [-123.2, 49], [-117, 49], [-117, 46], [-119, 46], [-122.7, 45.7], [-123.9, 46.2], [-124.80, 48.4]]] + }, + "properties": { + "name": "WA" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-117, 49], [-116.05, 49], [-116.05, 48], [-114.4, 46.6], [-112.9, 44.45], [-111.05, 44.45], [-111.05, 42], [-117.03, 42], [-117.03, 44.2], [-116.5, 45.5], [-117, 46], [-117, 49]]] + }, + "properties": { + "name": "ID" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-116.05, 49], [-104.05, 49], [-104.05, 45], [-111.05, 45], [-111.05, 44.45], [-112.9, 44.45], [-114.4, 46.6], [-116.05, 48], [-116.05, 49]] + ] + }, + "properties": { + "name": "MT" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-111.05, 45], [-104.05, 45], [-104.05, 41], [-111.05, 41], [-111.05, 45]]] + }, + "properties": { + "name": "WY" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-109.05, 37], [-103, 37], [-103, 32], [-106.65, 32], [-106.5, 31.8], [-108.2, 31.8], [-108.2, 31.33], [-109.05, 31.33], [-109.05, 37]]] + }, + "properties": { + "name": "NM" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-111.2, 45.1], [-109.30, 45.1], [-109.30, 44.1], [-109, 43.8], [-110, 43], [-111.2, 43.4], [-111.2, 45.1]]] + }, + "properties": { + "name": "Yellowstone NP" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-120.2, 38], [-119.30, 38.2], [-119, 37.7], [-119.9, 37.6], [-120.2, 38]]] + }, + "properties": { + "name": "Yosemite NP" + } + }, + { + "type": "Feature", + "geometry": { + "type": "Polygon", + "coordinates": [[[-118.2, 37.3], [-117, 37], [-116.3, 35.7], [-117, 35.7], [-117.2, 36.2], [-117.8, 36.4], [-118.2, 37.3]]] + }, + "properties": { + "name": "Death Valley NP" + } + } + ] +} diff --git a/site/_docs/reference.md b/site/_docs/reference.md index becd53e..3bf384f 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1962,7 +1962,9 @@ In the "C" (for "compatibility") column, "o" indicates that the function implements the OpenGIS Simple Features Implementation Specification for SQL, [version 1.2.1](https://www.opengeospatial.org/standards/sfs); "p" indicates that the function is a -[PostGIS](https://www.postgis.net/docs/reference.html) extension to OpenGIS. +[PostGIS](https://www.postgis.net/docs/reference.html) extension to OpenGIS; +"h" indicates that the function is an +[H2GIS](http://www.h2gis.org/docs/dev/functions/) extension. #### Geometry conversion functions (2D) @@ -2004,6 +2006,8 @@ Not implemented: | C | Operator syntax | Description |:- |:-------------------- |:----------- +| h | ST_MakeGrid(geom, deltaX, deltaY) | Calculates a regular grid of POLYGONs based on *geom* +| h | ST_MakeGridPoints(geom, deltaX, deltaY) | Calculates a regular grid of points based on *geom* | o | ST_MakeLine(point1 [, point ]*) | Creates a line-string from the given POINTs (or MULTIPOINTs) | p | ST_MakePoint(x, y [, z ]) | Alias for `ST_Point` | o | ST_Point(x, y [, z ]) | Constructs a point from two or three coordinates @@ -2015,8 +2019,6 @@ Not implemented: * ST_Expand(geom, deltaX, deltaY) Expands *geom*'s envelope * ST_MakeEllipse(point, width, height) Constructs an ellipse * ST_MakeEnvelope(xMin, yMin, xMax, yMax [, srid ]) Creates a rectangular POLYGON -* ST_MakeGrid(geom, deltaX, deltaY) Calculates a regular grid of POLYGONs based on *geom* -* ST_MakeGridPoints(geom, deltaX, deltaY) Calculates a regular grid of points based on *geom* * ST_MakePolygon(lineString [, hole ]*) Creates a POLYGON from *lineString* with the given holes (which are required to be closed LINESTRINGs) * ST_MinimumDiameter(geom) Returns the minimum diameter of *geom* * ST_MinimumRectangle(geom) Returns the minimum rectangle enclosing *geom*
