（四）Geospark SQL简介

作者: Scially | 来源:发表于2019-05-29 16:35 被阅读0次

GeoSpark SQL简介

Spark提供DataFrame数据集，并且可以通过SQL语句来操作。GeoSpark在这个基础上实现了一些空间上的函数，可以用于SQL语句中，以下是本节要用到的5个，其余的函数参考GeoSpark SQL

ST_GeomFromWKT (Wkt:string, UUID1, UUID2, ...)：从给出的WKT语句中构造Geometry
ST_Transform (A:geometry, SourceCRS:string, TargetCRS:string, [Optional] UseLongitudeLatitudeOrder:Boolean, [Optional] DisableError):默认情况下，ST_Transform 假设经度为x，维度为y，如果你的数据恰好相反，那么设置UseLongitudeLatitudeOrder为 false.
ST_Contains (A:geometry, B:geometry) : A是否完全包含B
ST_PolygonFromEnvelope (MinX:decimal, MinY:decimal, MaxX:decimal, MaxY:decimal, UUID1, UUID2, ...)：从给出的MinX，MinY，MinY、MaxY构造Polygon。
ST_PointFromText (Text:string, Delimiter:char, UUID1, UUID2, ...)：从给定的坐标中构建Point

构建数据集，加载CSV文件

SparkSession spark = SparkSession.builder().
        config("spark.serializer","org.apache.spark.serializer.KryoSerializer").
        config("spark.kryo.registrator", "org.datasyslab.geospark.serde.GeoSparkKryoRegistrator").
        master("local[*]").appName("Learn04").getOrCreate();

GeoSparkSQLRegistrator.registerAll(spark);
// 加载CSV文件,CSV中的第一列为WKT格式
String inputCSVPath = Learn04.class.getResource("/county_small.tsv").toString();
Dataset rawDF = spark.read().format("csv").
        option("delimiter", "\t").
        option("header", "false").
        load(inputCSVPath);
rawDF.createOrReplaceTempView("rawdf");
rawDF.show(10);

此时Spark仅仅是构建了这么一个DataFrame，对于_c0来说，他不是Geometry，仅仅是字符串，所以我们需要增加一列geom

+--------------------+---+---+--------+-----+-----------+--------------------+---+---+-----+----+-----+----+----+----------+--------+-----------+------------+
|                 _c0|_c1|_c2|     _c3|  _c4|        _c5|                 _c6|_c7|_c8|  _c9|_c10| _c11|_c12|_c13|      _c14|    _c15|       _c16|        _c17|
+--------------------+---+---+--------+-----+-----------+--------------------+---+---+-----+----+-----+----+----+----------+--------+-----------+------------+
|POLYGON ((-97.019...| 31|039|00835841|31039|     Cuming|       Cuming County| 06| H1|G4020|null| null|null|   A|1477895811|10447360|+41.9158651|-096.7885168|
|POLYGON ((-123.43...| 53|069|01513275|53069|  Wahkiakum|    Wahkiakum County| 06| H1|G4020|null| null|null|   A| 682138871|61658258|+46.2946377|-123.4244583|
|POLYGON ((-104.56...| 35|011|00933054|35011|    De Baca|      De Baca County| 06| H1|G4020|null| null|null|   A|6015539696|29159492|+34.3592729|-104.3686961|
|POLYGON ((-96.910...| 31|109|00835876|31109|  Lancaster|    Lancaster County| 06| H1|G4020| 339|30700|null|   A|2169240202|22877180|+40.7835474|-096.6886584|
|POLYGON ((-98.273...| 31|129|00835886|31129|   Nuckolls|     Nuckolls County| 06| H1|G4020|null| null|null|   A|1489645187| 1718484|+40.1764918|-098.0468422|
|POLYGON ((-65.910...| 72|085|01804523|72085|Las Piedras|Las Piedras Munic...| 13| H1|G4020| 490|41980|null|   A|  87748363|   32509|+18.1871483|-065.8711890|
|POLYGON ((-97.129...| 46|099|01265772|46099|  Minnehaha|    Minnehaha County| 06| H1|G4020|null|43620|null|   A|2090540341|17349847|+43.6674723|-096.7957261|
|POLYGON ((-99.821...| 48|327|01383949|48327|     Menard|       Menard County| 06| H1|G4020|null| null|null|   A|2336245914|  613559|+30.8843655|-099.8539896|
|POLYGON ((-120.65...| 06|091|00277310|06091|     Sierra|       Sierra County| 06| H1|G4020|null| null|null|   A|2468686374|23299110|+39.5769252|-120.5219926|
|POLYGON ((-85.239...| 21|053|00516873|21053|    Clinton|      Clinton County| 06| H1|G4020|null| null|null|   A| 510864252|21164150|+36.7288647|-085.1534262|
|POLYGON ((-83.880...| 39|063|01074044|39063|    Hancock|      Hancock County| 06| H1|G4020| 248|22300|null|   A|1376210232| 5959837|+41.0004711|-083.6660335|
|POLYGON ((-102.08...| 48|189|01383880|48189|       Hale|         Hale County| 06| H1|G4020|null|38380|null|   A|2602115649|  246678|+34.0684364|-101.8228879|
|POLYGON ((-85.978...| 01|027|00161539|01027|       Clay|         Clay County| 06| H1|G4020|null| null|null|   A|1564252367| 5284573|+33.2703999|-085.8635254|
|POLYGON ((-101.62...| 48|011|01383791|48011|  Armstrong|    Armstrong County| 06| H1|G4020| 108|11100|null|   A|2354581764|12219587|+34.9641790|-101.3566363|
|POLYGON ((-84.397...| 39|003|01074015|39003|      Allen|        Allen County| 06| H1|G4020| 338|30620|null|   A|1042470093|11266164|+40.7716274|-084.1061032|
|POLYGON ((-82.449...| 13|189|00348794|13189|   McDuffie|     McDuffie County| 06| H1|G4020|null|12260|null|   A| 666816637|23116292|+33.4824637|-082.4731880|
|POLYGON ((-90.191...| 55|111|01581115|55111|       Sauk|         Sauk County| 06| H1|G4020| 357|12660|null|   A|2152007753|45296336|+43.4279976|-089.9433290|
|POLYGON ((-92.415...| 05|137|00069902|05137|      Stone|        Stone County| 06| H1|G4020|null| null|null|   A|1570579427| 7841929|+35.8570312|-092.1405728|
|POLYGON ((-117.74...| 41|063|01155135|41063|    Wallowa|      Wallowa County| 06| H1|G4020|null| null|null|   A|8148602810|14199330|+45.5937530|-117.1855796|
|POLYGON ((-80.518...| 42|007|01214112|42007|     Beaver|       Beaver County| 06| H1|G4020| 430|38300|null|   A|1125901160|24165972|+40.6841401|-080.3507209|
+--------------------+---+---+--------+-----+-----------+--------------------+---+---+-----+----+-----+----+----+----------+--------+-----------+------------+

构建空间要素

这里用到了ST_GeomFromWKT这个函数，他读取一个WKT字符串，然后将其转为Geometry

String sqlText = "select ST_GeomFromWKT(_c0) AS countyshape, _c1, _c2, _c6 from rawdf";
Dataset spatialDf  = spark.sql(sqlText);
spatialDf.createOrReplaceTempView("spatialdf");
spatialDf.show(10);
spatialDf.printSchema();

+--------------------+---+---+
|         countyshape|_c1|_c2|
+--------------------+---+---+
|POLYGON ((-97.019...| 31|039|
|POLYGON ((-123.43...| 53|069|
|POLYGON ((-104.56...| 35|011|
|POLYGON ((-96.910...| 31|109|
|POLYGON ((-98.273...| 31|129|
|POLYGON ((-65.910...| 72|085|
|POLYGON ((-97.129...| 46|099|
|POLYGON ((-99.821...| 48|327|
|POLYGON ((-120.65...| 06|091|
|POLYGON ((-85.239...| 21|053|
+--------------------+---+---+

我们调用spatialDf.printSchema()输出每一列的元信息，检验一下是否转换成功，可以看到countyshape那列已经是geometry了。

root
 |-- countyshape: geometry (nullable = false)
 |-- _c1: string (nullable = true)
 |-- _c2: string (nullable = true)

坐标系转换

我们来尝试使用第一个GeoSpark提供的函数ST_Transform来完成一个简单的操作:坐标转换。

sqlText = "select ST_Transform(countyshape, 'epsg:4326', 'epsg:3857') as newcountyshape, countyshape, _c1, _c2, _c6 from spatialdf";
spatialDf = spark.sql(sqlText);
spatialDf.createOrReplaceTempView("spatialdf");
spatialDf.show(10);

newcountyshape是转换后的，可以看到坐标已经发生了变化

+--------------------+--------------------+---+---+
|      newcountyshape|         countyshape|_c1|_c2|
+--------------------+--------------------+---+---+
|POLYGON ((-108001...|POLYGON ((-97.019...| 31|039|
|POLYGON ((-137408...|POLYGON ((-123.43...| 53|069|
|POLYGON ((-116403...|POLYGON ((-104.56...| 35|011|
|POLYGON ((-107880...|POLYGON ((-96.910...| 31|109|
|POLYGON ((-109397...|POLYGON ((-98.273...| 31|129|
|POLYGON ((-733712...|POLYGON ((-65.910...| 72|085|
|POLYGON ((-108123...|POLYGON ((-97.129...| 46|099|
|POLYGON ((-111121...|POLYGON ((-99.821...| 48|327|
|POLYGON ((-134313...|POLYGON ((-120.65...| 06|091|
|POLYGON ((-948877...|POLYGON ((-85.239...| 21|053|
+--------------------+--------------------+---+---+

范围查询

接下来我们尝试使用ST_contains来进行范围查询，我们首先通过ST_PolygonFromEnvelope构建一个查询范围，当然你也可以根据自己的需要使用GeoSpark提供的其他SQL函数来构建Polygon、Point、Line等。

sqlText = "select * from spatialdf where ST_contains(ST_PolygonFromEnvelope(-98.0,-97.0,111.0,111.0), countyshape)";
spatialDf = spark.sql(sqlText);
spatialDf.createOrReplaceTempView("spatialdf");
spatialDf.show(10);

查询结果：

+--------------------+--------------------+---+---+
|      newcountyshape|         countyshape|_c1|_c2|
+--------------------+--------------------+---+---+
|POLYGON ((-108001...|POLYGON ((-97.019...| 31|039|
|POLYGON ((-107880...|POLYGON ((-96.910...| 31|109|
|POLYGON ((-733712...|POLYGON ((-65.910...| 72|085|
|POLYGON ((-108123...|POLYGON ((-97.129...| 46|099|
|POLYGON ((-948877...|POLYGON ((-85.239...| 21|053|
|POLYGON ((-933756...|POLYGON ((-83.880...| 39|063|
|POLYGON ((-957111...|POLYGON ((-85.978...| 01|027|
|POLYGON ((-939505...|POLYGON ((-84.397...| 39|003|
|POLYGON ((-917828...|POLYGON ((-82.449...| 13|189|
|POLYGON ((-100401...|POLYGON ((-90.191...| 55|111|
+--------------------+--------------------+---+---+

KNN临近查询

接下来，我们来使用SQL完成上一节中的KNN临近查询，我们直接对距离排序，然后取出前K个即可。

sqlText = "select _c6 AS countyname, ST_Distance(ST_PointFromText('-98.0,111.0',','), countyshape) AS distance " +
        "from spatialdf " +
        "order by distance ASC " +
        "LIMIT 5";
spatialDf = spark.sql(sqlText);
spatialDf.createOrReplaceTempView("spatialdf");
spatialDf.show();

查询结果如下：

+----------------+-----------------+
|      countyname|         distance|
+----------------+-----------------+
|Marquette County|64.55326972977753|
|Menominee County|65.83787128210902|
| Leelanau County|  66.425519281423|
|Minnehaha County|67.15767429101942|
|   Alpena County|67.29568076938793|
+----------------+-----------------+

（四）Geospark SQL简介

GeoSpark SQL简介

构建数据集，加载CSV文件

构建空间要素

坐标系转换

范围查询

KNN临近查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读