GeoSpark转换SQL函数失败

Question

我使用的是GeoSpark 1.3.1，我试图找到圆中包含的所有地理点，给定圆心和半径(以米为单位)。要做到这一点，我不希望将中心从度数转换到米数，创建圆(使用ST_Buffer)，然后将返回的多边形转换回度，然后在与所有地理点的连接中应用ST_Contains函数。请参阅下面的SQL：

WITH point_data AS (
 SELECT
   ST_Point(CAST(c.lon as Decimal(24,20)), CAST(c.lat as Decimal(24,20))) as geo_point
 FROM point_data_view as c
)
SELECT * FROM point_data as pd
WHERE ST_Contains(ST_Transform(ST_Buffer(ST_Transform(ST_Point(<LON>, <LAT>), 'epsg:4326', 'epsg:3857'), 1000.0), 'epsg:3857', 'epsg:4326'), pd.geo_point) = true

然而，当我按照GeoSpark指南将依赖项添加到我的pom文件中并创建一个提交的uber jar (使用spark2-submit)时，我得到了以下错误(仅当使用ST_Transform函数时)

java.lang.NoSuchMethodError: org.hsqldb.DatabaseURL.parseURL(Ljava/lang/String;ZZ)Lorg/hsqldb/persist/HsqlProperties;
        at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
        at org.hsqldb.jdbc.JDBCDataSource.getConnection(Unknown Source)
        at org.hsqldb.jdbc.JDBCDataSource.getConnection(Unknown Source)
        at org.geotools.referencing.factory.epsg.DirectEpsgFactory.getConnection(DirectEpsgFactory.java:3302)
        at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:436)
        at org.geotools.referencing.factory.DeferredAuthorityFactory.getBackingStore(DeferredAuthorityFactory.java:133)
        at org.geotools.referencing.factory.BufferedAuthorityFactory.isAvailable(BufferedAuthorityFactory.java:235)
        at org.geotools.referencing.factory.DeferredAuthorityFactory.isAvailable(DeferredAuthorityFactory.java:119)
        at org.geotools.factory.FactoryRegistry.isAvailable(FactoryRegistry.java:667)
        at org.geotools.factory.FactoryRegistry.isAcceptable(FactoryRegistry.java:501)
        at org.geotools.factory.FactoryRegistry.getServiceImplementation(FactoryRegistry.java:437)
        at org.geotools.factory.FactoryRegistry.getServiceProvider(FactoryRegistry.java:365)
        at org.geotools.factory.FactoryCreator.getServiceProvider(FactoryCreator.java:145)
        at org.geotools.referencing.ReferencingFactoryFinder.getAuthorityFactory(ReferencingFactoryFinder.java:220)
        at org.geotools.referencing.ReferencingFactoryFinder.getCRSAuthorityFactory(ReferencingFactoryFinder.java:440)
        at org.geotools.referencing.factory.epsg.LongitudeFirstFactory.createBackingStore(LongitudeFirstFactory.java:192)
        at org.geotools.referencing.factory.DeferredAuthorityFactory.getBackingStore(DeferredAuthorityFactory.java:133)
        at org.geotools.referencing.factory.BufferedAuthorityFactory.isAvailable(BufferedAuthorityFactory.java:235)
        at org.geotools.referencing.factory.DeferredAuthorityFactory.isAvailable(DeferredAuthorityFactory.java:119)
        at org.geotools.factory.FactoryRegistry.isAvailable(FactoryRegistry.java:667)
        at org.geotools.factory.FactoryRegistry.isAcceptable(FactoryRegistry.java:501)
        at org.geotools.factory.FactoryRegistry$1.filter(FactoryRegistry.java:192)
        at javax.imageio.spi.FilterIterator.advance(ServiceRegistry.java:834)
        at javax.imageio.spi.FilterIterator.<init>(ServiceRegistry.java:828)
        at javax.imageio.spi.ServiceRegistry.getServiceProviders(ServiceRegistry.java:519)
        at org.geotools.factory.FactoryRegistry.getServiceProviders(FactoryRegistry.java:197)
        at org.geotools.referencing.ReferencingFactoryFinder.getFactories(ReferencingFactoryFinder.java:180)
        at org.geotools.referencing.ReferencingFactoryFinder.getCRSAuthorityFactories(ReferencingFactoryFinder.java:455)
        at org.geotools.referencing.DefaultAuthorityFactory.getBackingFactory(DefaultAuthorityFactory.java:89)
        at org.geotools.referencing.DefaultAuthorityFactory.<init>(DefaultAuthorityFactory.java:69)
        at org.geotools.referencing.CRS.getAuthorityFactory(CRS.java:263)
        at org.geotools.referencing.CRS.decode(CRS.java:525)
        at org.geotools.referencing.CRS.decode(CRS.java:453)
        at org.apache.spark.sql.geosparksql.expressions.ST_Transform.eval(Functions.scala:237)

我试图在我的构建中对"org.hsqldb“进行着色和重新定位，但这并没有改变任何事情，并且没有在uber jar中包含GeoSpark jar，但是将它们作为spark2-submit的一部分加载，我得到了相同的错误。我真的找不到解决这个问题的方法，而且只有在我使用ST_Transform方法时才会发生这种情况？

看起来my Spark platform的org.hsqldb版本比GeoSpark提供的旧版本！

我的着色如下所示：

<properties>
    <geoSparkVersion>1.3.1</geoSparkVersion>
    <sparkVersion>2.3.0</sparkVersion>
    <clouderaPackage>cloudera2</clouderaPackage>
    <scalaVersion>2.11.0</scalaVersion>
    <scalaBinaryVersion>2.11</scalaBinaryVersion>
</properties>

<dependencies>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_${scalaBinaryVersion}</artifactId>
        <version>${sparkVersion}.${clouderaPackage}</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_${scalaBinaryVersion}</artifactId>
        <version>${sparkVersion}.${clouderaPackage}</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.datasyslab</groupId>
        <artifactId>geospark</artifactId>
        <version>${geoSparkVersion}</version>
    </dependency>
    <dependency>
        <groupId>org.datasyslab</groupId>
        <artifactId>geospark-sql_2.3</artifactId>
        <version>${geoSparkVersion}</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-shade-plugin</artifactId>
            <version>3.2.1</version>
            <configuration>
                <shadedArtifactAttached>true</shadedArtifactAttached>
                <finalName>${artifactId}-${version}-${jarNameWithDependencies}</finalName>
                <filters>
                    <filter>
                        <artifact>*:*</artifact>
                        <excludes>
                            <exclude>META-INF/*.SF</exclude>
                            <exclude>META-INF/*.DSA</exclude>
                            <exclude>META-INF/*.RSA</exclude>
                        </excludes>
                    </filter>
                </filters>
                <relocations>
                    <relocation>
                        <pattern>org.hsqldb</pattern>
                        <shadedPattern>shaded.org.hsqldb</shadedPattern>
                    </relocation>
                </relocations>
            </configuration>
            <executions>
                <execution>
                    <phase>package</phase>
                    <goals>
                        <goal>shade</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

Answer 1

看起来像是典型的依赖问题，可能你的ubber jar容器不同版本的org.hsqldb库，你应该尝试从你的依赖中排除org.hsqldb.*，或者对其进行着色。我猜你在uber jar上用maven-shaded-plugin？如果您这样做了，您可以在这里查看如何排除依赖：https://maven.apache.org/plugins/maven-shade-plugin/examples/includes-excludes.html