Details

      Description

      Implement spatial query functionality in Drill to provide location based queries and filtering. It could be similar to PostGIS for Postgres and allow queries like:

      select * from
      (select columns[2] as location, columns[4] as lon, columns[3] as lat,
      ST_DWithin(ST_Point(-121.895, 37.339), ST_Point(columns[4], columns[3]), 0.1) as isWithin
      from dfs.`default`.`/home/k255/drill/sample-data/CA-cities.csv`
      )
      where isWithin = true;

      Working proposal is available at http://github.com/k255 (see drill-gis and drill fork).

        Issue Links

          Activity

          Hide
          githubbot ASF GitHub Bot added a comment -

          GitHub user k255 opened a pull request:

          https://github.com/apache/drill/pull/191

          DRILL-3914: support for geospatial query functionality

          Sample dataset is provided on classpath, after building from fork repository, you can query it like:
          select * from cp.`sample-data/CA-cities.csv` limit 5;

          For details on current geospatial functionality please see:
          https://github.com/k255/drill-gis

          Currently the solution works on common use cases, but is based on varbinary data type which has limitations for more complex geometries (size limit).

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/k255/drill master

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/drill/pull/191.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #191


          commit dc19cb732645b2d168f04eec521848992807cf07
          Author: potocki <k255@gmx.com>
          Date: 2015-10-08T06:10:33Z

          gis contrib module with basic spatial queries functionality


          Show
          githubbot ASF GitHub Bot added a comment - GitHub user k255 opened a pull request: https://github.com/apache/drill/pull/191 DRILL-3914 : support for geospatial query functionality Sample dataset is provided on classpath, after building from fork repository, you can query it like: select * from cp.`sample-data/CA-cities.csv` limit 5; For details on current geospatial functionality please see: https://github.com/k255/drill-gis Currently the solution works on common use cases, but is based on varbinary data type which has limitations for more complex geometries (size limit). You can merge this pull request into a Git repository by running: $ git pull https://github.com/k255/drill master Alternatively you can review and apply these changes as the patch at: https://github.com/apache/drill/pull/191.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #191 commit dc19cb732645b2d168f04eec521848992807cf07 Author: potocki <k255@gmx.com> Date: 2015-10-08T06:10:33Z gis contrib module with basic spatial queries functionality
          Hide
          jnadeau Jacques Nadeau added a comment -

          This is super cool! Thanks for contributing this. I'm not a GIS pro so I wonder if Ted Dunning or Chris Westin would be willing to do the first pass review as I think they have both worked with GIS stuff a bit more?

          Show
          jnadeau Jacques Nadeau added a comment - This is super cool! Thanks for contributing this. I'm not a GIS pro so I wonder if Ted Dunning or Chris Westin would be willing to do the first pass review as I think they have both worked with GIS stuff a bit more?
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user tdunning commented on the pull request:

          https://github.com/apache/drill/pull/191#issuecomment-146638022

          This looks lovely. There isn't much here but a shim layer to the ESRI library, but, hey, connecting the dots is a huge contribution.

          Is there any reasonable kind of test to be done here?

          Show
          githubbot ASF GitHub Bot added a comment - Github user tdunning commented on the pull request: https://github.com/apache/drill/pull/191#issuecomment-146638022 This looks lovely. There isn't much here but a shim layer to the ESRI library, but, hey, connecting the dots is a huge contribution. Is there any reasonable kind of test to be done here?
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user k255 commented on the pull request:

          https://github.com/apache/drill/pull/191#issuecomment-146679625

          I added some general tests to check if geometry functions work as expected.

          I'm happy that you like it. Currently it's quite simple but it can grow.
          One direction is to take care of limited size of varbinary (introduce new type or extend size of existing one) because it limits geometry to just simple shapes.

          Show
          githubbot ASF GitHub Bot added a comment - Github user k255 commented on the pull request: https://github.com/apache/drill/pull/191#issuecomment-146679625 I added some general tests to check if geometry functions work as expected. I'm happy that you like it. Currently it's quite simple but it can grow. One direction is to take care of limited size of varbinary (introduce new type or extend size of existing one) because it limits geometry to just simple shapes.
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user k255 commented on the pull request:

          https://github.com/apache/drill/pull/191#issuecomment-148369490

          Fixed bug with complex geometries caused by to small buffer. Now it's possible to build more complex geometries.Would be nice if somebody could check the way I handled it (buffer reallocation).
          I also have some progress on integration with gis tools as shown here: http://bit.ly/1Rcvrjd

          Show
          githubbot ASF GitHub Bot added a comment - Github user k255 commented on the pull request: https://github.com/apache/drill/pull/191#issuecomment-148369490 Fixed bug with complex geometries caused by to small buffer. Now it's possible to build more complex geometries.Would be nice if somebody could check the way I handled it (buffer reallocation). I also have some progress on integration with gis tools as shown here: http://bit.ly/1Rcvrjd
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user jacques-n commented on a diff in the pull request:

          https://github.com/apache/drill/pull/191#discussion_r42191021

          — Diff: contrib/gis/src/main/java/org/apache/drill/exec/expr/fn/impl/gis/STAsText.java —
          @@ -0,0 +1,58 @@
          +/**
          + * 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.drill.exec.expr.fn.impl.gis;
          +
          +import javax.inject.Inject;
          +
          +import org.apache.drill.exec.expr.DrillSimpleFunc;
          +import org.apache.drill.exec.expr.annotations.FunctionTemplate;
          +import org.apache.drill.exec.expr.annotations.Output;
          +import org.apache.drill.exec.expr.annotations.Param;
          +import org.apache.drill.exec.expr.holders.VarBinaryHolder;
          +import org.apache.drill.exec.expr.holders.VarCharHolder;
          +
          +import io.netty.buffer.DrillBuf;
          +
          +@FunctionTemplate(name = "st_astext", scope = FunctionTemplate.FunctionScope.SIMPLE,
          + nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          +public class STAsText implements DrillSimpleFunc {
          + @Param
          + VarBinaryHolder geom1Param;
          +
          + @Output
          + VarCharHolder out;
          +
          + @Inject
          + DrillBuf buffer;
          +
          + public void setup()

          { + }

          +
          + public void eval() {
          + com.esri.core.geometry.ogc.OGCGeometry geom1 = com.esri.core.geometry.ogc.OGCGeometry
          + .fromBinary(geom1Param.buffer.nioBuffer(geom1Param.start, geom1Param.end));
          +
          + String geomWKT = geom1.asText();
          +
          + int outputSize = geomWKT.getBytes().length;
          + out.buffer = buffer.reallocIfNeeded(outputSize);
          — End diff –

          needs to be:

          buffer = out.buffer = buffer.reallocIfNeeded(outputSize);

          basically, you also need to make sure that the original field is point at the right buffer for the next function execution.

          Show
          githubbot ASF GitHub Bot added a comment - Github user jacques-n commented on a diff in the pull request: https://github.com/apache/drill/pull/191#discussion_r42191021 — Diff: contrib/gis/src/main/java/org/apache/drill/exec/expr/fn/impl/gis/STAsText.java — @@ -0,0 +1,58 @@ +/** + * 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.drill.exec.expr.fn.impl.gis; + +import javax.inject.Inject; + +import org.apache.drill.exec.expr.DrillSimpleFunc; +import org.apache.drill.exec.expr.annotations.FunctionTemplate; +import org.apache.drill.exec.expr.annotations.Output; +import org.apache.drill.exec.expr.annotations.Param; +import org.apache.drill.exec.expr.holders.VarBinaryHolder; +import org.apache.drill.exec.expr.holders.VarCharHolder; + +import io.netty.buffer.DrillBuf; + +@FunctionTemplate(name = "st_astext", scope = FunctionTemplate.FunctionScope.SIMPLE, + nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) +public class STAsText implements DrillSimpleFunc { + @Param + VarBinaryHolder geom1Param; + + @Output + VarCharHolder out; + + @Inject + DrillBuf buffer; + + public void setup() { + } + + public void eval() { + com.esri.core.geometry.ogc.OGCGeometry geom1 = com.esri.core.geometry.ogc.OGCGeometry + .fromBinary(geom1Param.buffer.nioBuffer(geom1Param.start, geom1Param.end)); + + String geomWKT = geom1.asText(); + + int outputSize = geomWKT.getBytes().length; + out.buffer = buffer.reallocIfNeeded(outputSize); — End diff – needs to be: buffer = out.buffer = buffer.reallocIfNeeded(outputSize); basically, you also need to make sure that the original field is point at the right buffer for the next function execution.
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user k255 commented on a diff in the pull request:

          https://github.com/apache/drill/pull/191#discussion_r42217546

          — Diff: contrib/gis/src/main/java/org/apache/drill/exec/expr/fn/impl/gis/STAsText.java —
          @@ -0,0 +1,58 @@
          +/**
          + * 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.drill.exec.expr.fn.impl.gis;
          +
          +import javax.inject.Inject;
          +
          +import org.apache.drill.exec.expr.DrillSimpleFunc;
          +import org.apache.drill.exec.expr.annotations.FunctionTemplate;
          +import org.apache.drill.exec.expr.annotations.Output;
          +import org.apache.drill.exec.expr.annotations.Param;
          +import org.apache.drill.exec.expr.holders.VarBinaryHolder;
          +import org.apache.drill.exec.expr.holders.VarCharHolder;
          +
          +import io.netty.buffer.DrillBuf;
          +
          +@FunctionTemplate(name = "st_astext", scope = FunctionTemplate.FunctionScope.SIMPLE,
          + nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          +public class STAsText implements DrillSimpleFunc {
          + @Param
          + VarBinaryHolder geom1Param;
          +
          + @Output
          + VarCharHolder out;
          +
          + @Inject
          + DrillBuf buffer;
          +
          + public void setup()

          { + }

          +
          + public void eval() {
          + com.esri.core.geometry.ogc.OGCGeometry geom1 = com.esri.core.geometry.ogc.OGCGeometry
          + .fromBinary(geom1Param.buffer.nioBuffer(geom1Param.start, geom1Param.end));
          +
          + String geomWKT = geom1.asText();
          +
          + int outputSize = geomWKT.getBytes().length;
          + out.buffer = buffer.reallocIfNeeded(outputSize);
          — End diff –

          Thanks, this was helpful. You're right the next executions failed with "Tried to remove unmanaged buffer.". Now it's fixed. Is it also valid to use BufferManager.getManagedBuffer(size) somehow (maybe instead of injecting the DrillBuf)?

          Show
          githubbot ASF GitHub Bot added a comment - Github user k255 commented on a diff in the pull request: https://github.com/apache/drill/pull/191#discussion_r42217546 — Diff: contrib/gis/src/main/java/org/apache/drill/exec/expr/fn/impl/gis/STAsText.java — @@ -0,0 +1,58 @@ +/** + * 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.drill.exec.expr.fn.impl.gis; + +import javax.inject.Inject; + +import org.apache.drill.exec.expr.DrillSimpleFunc; +import org.apache.drill.exec.expr.annotations.FunctionTemplate; +import org.apache.drill.exec.expr.annotations.Output; +import org.apache.drill.exec.expr.annotations.Param; +import org.apache.drill.exec.expr.holders.VarBinaryHolder; +import org.apache.drill.exec.expr.holders.VarCharHolder; + +import io.netty.buffer.DrillBuf; + +@FunctionTemplate(name = "st_astext", scope = FunctionTemplate.FunctionScope.SIMPLE, + nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) +public class STAsText implements DrillSimpleFunc { + @Param + VarBinaryHolder geom1Param; + + @Output + VarCharHolder out; + + @Inject + DrillBuf buffer; + + public void setup() { + } + + public void eval() { + com.esri.core.geometry.ogc.OGCGeometry geom1 = com.esri.core.geometry.ogc.OGCGeometry + .fromBinary(geom1Param.buffer.nioBuffer(geom1Param.start, geom1Param.end)); + + String geomWKT = geom1.asText(); + + int outputSize = geomWKT.getBytes().length; + out.buffer = buffer.reallocIfNeeded(outputSize); — End diff – Thanks, this was helpful. You're right the next executions failed with "Tried to remove unmanaged buffer.". Now it's fixed. Is it also valid to use BufferManager.getManagedBuffer(size) somehow (maybe instead of injecting the DrillBuf)?
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user asfgit closed the pull request at:

          https://github.com/apache/drill/pull/191

          Show
          githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/drill/pull/191
          Hide
          jnadeau Jacques Nadeau added a comment -

          Fixed in 6576123

          Show
          jnadeau Jacques Nadeau added a comment - Fixed in 6576123

            People

            • Assignee:
              Unassigned
              Reporter:
              k255 Karol Potocki
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development