1.地理空间数据概念

地理空间数据又称为几何数据，可用来表示物体的位置、形态、大小分布等各方面的信息，是对现实世界中存在的具有定位意义的事物和现象的定量描述。通常，地理空间数据以点、线、面、体的形式表示。通过对地理空间数据的查询，可以获得被查询对象的面积、长度、空间关系等。

2.DLI 支持的地理空间数据类型

Point（点），LineString（线），Polygon（面），MultiPoint（多点），MultiLineString（多线）， MultiPolygon（多面）

3.应用场景

地理空间查询用于统计某空间范围内兴趣点的个数，检查两个区域是否重叠、两个地点之间的距离等。

4.环境准备

4.1pom文件

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>CustomAreaPeople</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <hadoop.version>2.6.0</hadoop.version>
        <spark.version>2.4.0</spark.version>
        <version>1.0-SNAPSHOT</version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-core_2.11</artifactId>
            <version>${spark.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.11</artifactId>
            <version>${spark.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.sedona</groupId>
            <artifactId>sedona-core-2.4_2.11</artifactId>
            <version>1.0.0-incubating</version>
        </dependency>
        <dependency>
            <groupId>org.apache.sedona</groupId>
            <artifactId>sedona-sql-2.4_2.11</artifactId>
            <version>1.0.0-incubating</version>
        </dependency>

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-main</artifactId>
            <version>24.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.geotools/gt-referencing -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-referencing</artifactId>
            <version>24.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.geotools/gt-epsg-hsql -->
        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-epsg-hsql</artifactId>
            <version>24.0</version>
        </dependency>

        <dependency>
            <groupId>org.wololo</groupId>
            <artifactId>jts2geojson</artifactId>
            <version>0.14.3</version>
        </dependency>
        <dependency>
            <groupId>org.locationtech.jts</groupId>
            <artifactId>jts-core</artifactId>
            <version>1.18.0</version>
        </dependency>
<!--        <dependency>-->
<!--        <groupId>org.apache.spark</groupId>-->
<!--        <artifactId>spark-hive_${scala.version}</artifactId>-->
<!--        <version>${spark.version}</version>-->
<!--        </dependency>-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
    </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>${hadoop.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>${hadoop.version}</version>
            <exclusions>
                <exclusion>
                    <artifactId>httpclient</artifactId>
                    <groupId>org.apache.httpcomponents</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- elasticsearch 依赖 2.x 的 log4j -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.0</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.9</version>
        </dependency>

        <dependency>
            <groupId>commons-net</groupId>
            <artifactId>commons-net</artifactId>
            <version>3.4</version>
        </dependency>
    </dependencies>
    <repositories>
        <repository>
            <id>maven2-repository.dev.java.net</id>
            <name>Java.net repository</name>
            <url>https://download.java.net/maven/2</url>
        </repository>
        <repository>
            <id>osgeo</id>
            <name>OSGeo Release Repository</name>
            <url>https://repo.osgeo.org/repository/release/</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
            <releases>
                <enabled>true</enabled>
            </releases>
        </repository>
    </repositories>

    <build>
        <plugins>
            <!-- 该插件用于将Scala代码编译成class文件 -->
            <plugin>
                <groupId>net.alchim31.maven</groupId>
                <artifactId>scala-maven-plugin</artifactId>
                <version>3.2.2</version>
                <configuration>
                    <recompileMode>incremental</recompileMode>
                    <addScalacArgs>-target:jvm-1.8</addScalacArgs>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <!--                            <goal>testCompile</goal>-->
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>3.0.0</version>
                <configuration>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

4.2 spark-submit启动脚本参数

--conf spark.serializer=org.apache.spark.serializer.KryoSerializer \
--conf spark.kryo.registrator=org.apache.sedona.core.serde.SedonaKryoRegistrator \
--conf spark.sql.extensions=org.apache.sedona.sql.SedonaSqlExtensions \

4.3 Spark会话

// 新建SparkSession
    val sqlSession = SparkSession
      .builder()
      .appName(appName)
      .master(GV.masterName)
      .getOrCreate()
    GV.logger.warn("================>>>>>> 指定区域用户数初始化成功！")
    //将sedona-sql_2.4中的所有函数注册到sqlSession
    SedonaSQLRegistrator.registerAll(sqlSession)
    //查询地理空间函数是否注册成功
    GV.logger.warn("=========>>>> 函数注册查询！" + sqlSession.catalog.getFunction("ST_Geomfromtext"))
    GV.logger.warn("=========>>>> 函数注册查询！" + sqlSession.catalog.getFunction("ST_Intersects"))
    GV.logger.warn("=========>>>> 函数注册查询！" + sqlSession.catalog.getFunction("ST_Point"))
    GV.logger.warn("=========>>>> 函数注册查询！" + sqlSession.catalog.getFunction("ST_PolygonFromEnvelope"))

5.地理空间函数使用示例

5.1 ST_GeomFromGeoHash

返回与Geohash字符串geohash（base-32编码）对应的边界框的Geometry，其精度
为prec位。 有关GeoHashes的更多信息，请参阅Geohash。

Format: ST_GeomFromGeoHash(geohash: string, precision: int)
查询命令：
select ST_AsText(ST_GeomFromGeoHash('ssf17',25))

5.2 ST_GeomFromWKT (Wkt:string)

从给定的已知文本标记语言的二进制表示（WKB）创建Geometry。

Format: ST_GeomFromWKT (Wkt:string)
select
ST_AsText((ST_GeomFromWKT(ST_AsEWKB(ST_GeomFromText('MULTIPOLYGON (((30 20,
45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))')))))

5.3 ST_GeomFromWKT

根据给定的已知文本标记语言（WKT）创建Geometry。
ST_GeomFromText是ST_GeomFromWKT的别称

Format: ST_GeomFromWKT (Wkt:string)
select ST_AsText((ST_GeomFromText('POLYGON ((30 10, 40 40,
20 40, 10 20, 30 10))')))

5.4 ST_Point

返回具有给定坐标值的point。
Format: ST_Point (X:decimal, Y:decimal) Format: ST_Point (X:decimal, Y:decimal, Z:decimal)

5.5 ST_Intersects

如果a和b在2D中空间相交（即共享空间的任何部分），则返回true。 相当于NOT
st_disjoint（a，b）。
Format: ST_Intersects (A:geometry, B:geometry)

5.6 ST_PolygonFromEnvelope

从MinX，MinY，MaxX，MaxY构建一个矩形。
Format: ST_PolygonFromEnvelope (MinX:decimal, MinY:decimal, MaxX:decimal, MaxY:decimal)

5.7 ST_Buffer

返回一个几何图形/地理位置，它表示与该几何图形/地理位置的距离小于或等于距离的所有点。
Format: ST_Buffer (A:geometry, buffer: Double)

6.注意事项

经纬度需要转换成Decimal格式

7.完整SQL示例

//使用圆形类型自定义区域的id关联出基站编码
    val basestation_1 = sqlSession.sql(
      """
        |with ta as(
        |select * from type1
        |where lon1 is not null and lat1 is not null
        |),
        |tb as(
        |select * from
        |p_ecgiTempView
        |where LAT is not null and LON is not null
        |),
        |tc as (
        |select tb.LAT,tb.LON,tb.CELLSN,ta.area_id,ta.lat1,ta.lon1,ta.app_secret_id from ta
        |inner join tb
        |where
        |1 = 1
        |and ST_Intersects(ST_Buffer(ST_Point(ta.lon1,ta.lat1),ta.radius/100000),ST_Point(tb.LON,tb.LAT))
        |)
        |select tc.area_id,tc.CELLSN,tc.app_secret_id from tc
        |""".stripMargin)

图上需求是：根据中心点获取半径范围内的所有基站

//使用矩形类型自定义区域的id关联出基站编码
    val basestation_2 = sqlSession.sql(
      """
        |with ta as (
        |select * from type2
        |where lon1 is not null and lat1 is not null and lon2 is not null and lat2 is not null
        |),
        |tb as (
        |select * from
        |p_ecgiTempView
        |where LAT is not null and LON is not null
        |),
        |tc as (
        |select tb.LAT,tb.LON,tb.CELLSN,ta.area_id,ta.lat1,ta.lon1,ta.lat2,ta.lon2,ta.app_secret_id
        |from ta
        |inner join tb
        |where
        |1 = 1
        |and ST_Intersects(ST_PolygonFromEnvelope(ta.lon1,ta.lon2,ta.lat1,ta.lat2), ST_Point(tb.LON,tb.LAT))
        |)
        |select tc.area_id,tc.CELLSN,tc.app_secret_id from tc
        |""".stripMargin)

图上需求是：根据矩形四个点获取矩形内的所有基站

    val basestation_3 = sqlSession.sql(
      """
        |with ta as (
        |select * from type3
        |where points is not null
        |),
        |tb as (
        |select * from
        |p_ecgiTempView
        |where LAT is not null and LON is not null
        |),
        |tc as (
        |select tb.LAT,tb.LON,tb.CELLSN,ta.area_id,ta.points,ta.app_secret_id
        |from ta
        |inner join tb
        |where
        |1 = 1
        |and ST_Intersects(ST_GeomFromText(points), ST_Point(tb.LON,tb.LAT))
        |)
        |select area_id,CELLSN,ta.app_secret_id from tc
        |""".stripMargin)

图上需求是：根据自定义边框的多个点获取区域内的所有基站

补充

计算多边形质心

ST_Centroid
SQL示例：

SELECT ST_AsText(ST_Centroid(ST_GeomFromText('polygon ((116.389060369857 39.8805385985325,116.389149003366 39.8793549988737,116.389226226988 39.8793093165453,116.390195405884 39.8793281494735,116.390262646471 39.8793685904133,116.390294516148 39.8794955959993,116.390218462185 39.8805449250846,116.390083322732 39.8805946022267,116.389060369857 39.8805385985325))')));