（四）Geospark SQL简介

GeoSpark SQL简介

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

  1. ST_GeomFromWKT (Wkt:string, UUID1, UUID2, ...)：从给出的WKT语句中构造Geometry
  2. ST_Transform (A:geometry, SourceCRS:string, TargetCRS:string, [Optional] UseLongitudeLatitudeOrder:Boolean, [Optional] DisableError):默认情况下，ST_Transform 假设经度为x，维度为y，如果你的数据恰好相反， 那么设置UseLongitudeLatitudeOrder为 false.
  3. ST_Contains (A:geometry, B:geometry) : A是否完全包含B
  4. ST_PolygonFromEnvelope (MinX:decimal, MinY:decimal, MaxX:decimal, MaxY:decimal, UUID1, UUID2, ...)： 从给出的MinX，MinY，MinY、MaxY构造Polygon。
  5. 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|
+----------------+-----------------+
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,544评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,430评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,764评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,193评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,216评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,182评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,063评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,917评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,329评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,543评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,722评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,425评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,019评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,671评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,825评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,729评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,614评论 2赞 353

推荐阅读更多精彩内容