（六）Geospark SQL 加载SHP数据

Geospark SQL 加载SHP数据

GeoSpark SQL默认是无法读取Shp和GeoJson格式的矢量数据的，必须要通过RDD读取，然后利用GeoSpark提供的Adapter在RDD和DataFrame之间互转。

接下来我们还是利用我们上一节的公园的数据来学习。

初始化一个SparkSession

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

GeoSparkSQLRegistrator.registerAll(spark);
GeoSparkVizRegistrator.registerAll(spark);

利用RDD读取shp

JavaSparkContext context = new JavaSparkContext(spark.sparkContext());
String inputPath = Learn06.class.getResource("/parks").toString();
SpatialRDD<Geometry> rdd = ShapefileReader.readToGeometryRDD(new JavaSparkContext(spark.sparkContext()), inputPath);

将RDD转为DataFrame

Dataset<Row> rawDF = Adapter.toDf(rdd, spark);
rawDF.createOrReplaceTempView("park");
rawDF.show();
rawDF.printSchema();

+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
|            geometry|ParkId|RefParkId|            ParkName|    Neighborho|       EWStreet|       NSStreet|DogPark|Washrooms|SpecialFea|
+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
|POLYGON ((-123.15...|     1|    -9999|                    |     Kitsilano|               |               |      N|         |          |
|POLYGON ((-123.15...|     2|      208| Rosemary Brown Park|     Kitsilano|  W 11th Avenue|    Vine Street|      N|        N|         N|
|MULTIPOLYGON (((-...|     3|      141|      Tea Swamp Park|Mount Pleasant|  E 15th Avenue|  Sophia Street|      N|        N|         N|
|MULTIPOLYGON (((-...|     4|    -9999|                    |    Strathcona|               |               |      N|         |          |
|MULTIPOLYGON (((-...|     5|      202|         Morton Park|      West End|  Morton Avenue|  Denman Street|      N|        N|         N|
|MULTIPOLYGON (((-...|     6|    -9999|        Mcbride Park|     Kitsilano|               |               |      N|         |          |
|MULTIPOLYGON (((-...|     7|    -9999|      Granville Park|      Fairview|               |               |      N|         |          |
|MULTIPOLYGON (((-...|     8|    -9999|                    |Mount Pleasant|               |               |      N|         |          |
|MULTIPOLYGON (((-...|     9|       15|      Creekside Park|Mount Pleasant|Terminal Avenue|  Quebec Street|      N|        N|         Y|
|MULTIPOLYGON (((-...|    10|      134|China Creek South...|Mount Pleasant|  E 10th Avenue|    Clark Drive|      N|        N|         N|
|MULTIPOLYGON (((-...|    11|      200|Barclay Heritage ...|      West End| Barclay Street|  Nicola Street|      N|        Y|         N|
|POLYGON ((-123.15...|    12|      233|Arbutus Greenway ...|     Kitsilano|  W 11th Avenue| Arbutus Street|      N|        N|         N|
|POLYGON ((-123.18...|    13|      106|         Almond Park|     Kitsilano|  W 12th Avenue|  Dunbar Street|      N|        N|         N|
|POLYGON ((-123.15...|    14|      109|       Delamont Park|     Kitsilano|   W 7th Avenue| Arbutus Street|      N|        N|         N|
|POLYGON ((-123.15...|    15|    -9999|                    |     Kitsilano|               |               |      N|         |          |
|POLYGON ((-123.14...|    16|      118| Seaforth Peace Park|     Kitsilano|Cornwall Avenue|Chestnut Street|      N|        N|         N|
|POLYGON ((-123.10...|    17|      139| Mount Pleasant Park|Mount Pleasant|  W 16th Avenue| Ontario Street|      N|        N|         N|
|POLYGON ((-123.10...|    18|      138| Major Matthews Park|Mount Pleasant|  W 11th Avenue|Manitoba Street|      N|        N|         N|
|POLYGON ((-123.10...|    19|      137|Jonathan Rogers Park|Mount Pleasant|   W 7th Avenue|Manitoba Street|      N|        Y|         N|
|POLYGON ((-123.09...|    20|      183|       Thornton Park|    Strathcona|Terminal Avenue|    Main Street|      N|        N|         N|
+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
only showing top 20 rows

root
 |-- geometry: string (nullable = true)
 |-- ParkId: string (nullable = true)
 |-- RefParkId: string (nullable = true)
 |-- ParkName: string (nullable = true)
 |-- Neighborho: string (nullable = true)
 |-- EWStreet: string (nullable = true)
 |-- NSStreet: string (nullable = true)
 |-- DogPark: string (nullable = true)
 |-- Washrooms: string (nullable = true)
 |-- SpecialFea: string (nullable = true)

从输出结果中可以看到，转为dataframe后，默认是没有Geometry的，需要我们自己构建。

// 构建几何图形(Geometry)
String sqlText = "select ST_GeomFromWKT(geometry) as shape, * from park";
rawDF = spark.sql(sqlText);
rawDF.createOrReplaceTempView("park");
rawDF.show();
rawDF.printSchema();

+--------------------+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
|               shape|            geometry|ParkId|RefParkId|            ParkName|    Neighborho|       EWStreet|       NSStreet|DogPark|Washrooms|SpecialFea|
+--------------------+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
|POLYGON ((-123.15...|POLYGON ((-123.15...|     1|    -9999|                    |     Kitsilano|               |               |      N|         |          |
|POLYGON ((-123.15...|POLYGON ((-123.15...|     2|      208| Rosemary Brown Park|     Kitsilano|  W 11th Avenue|    Vine Street|      N|        N|         N|
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     3|      141|      Tea Swamp Park|Mount Pleasant|  E 15th Avenue|  Sophia Street|      N|        N|         N|
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     4|    -9999|                    |    Strathcona|               |               |      N|         |          |
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     5|      202|         Morton Park|      West End|  Morton Avenue|  Denman Street|      N|        N|         N|
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     6|    -9999|        Mcbride Park|     Kitsilano|               |               |      N|         |          |
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     7|    -9999|      Granville Park|      Fairview|               |               |      N|         |          |
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     8|    -9999|                    |Mount Pleasant|               |               |      N|         |          |
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|     9|       15|      Creekside Park|Mount Pleasant|Terminal Avenue|  Quebec Street|      N|        N|         Y|
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|    10|      134|China Creek South...|Mount Pleasant|  E 10th Avenue|    Clark Drive|      N|        N|         N|
|MULTIPOLYGON (((-...|MULTIPOLYGON (((-...|    11|      200|Barclay Heritage ...|      West End| Barclay Street|  Nicola Street|      N|        Y|         N|
|POLYGON ((-123.15...|POLYGON ((-123.15...|    12|      233|Arbutus Greenway ...|     Kitsilano|  W 11th Avenue| Arbutus Street|      N|        N|         N|
|POLYGON ((-123.18...|POLYGON ((-123.18...|    13|      106|         Almond Park|     Kitsilano|  W 12th Avenue|  Dunbar Street|      N|        N|         N|
|POLYGON ((-123.15...|POLYGON ((-123.15...|    14|      109|       Delamont Park|     Kitsilano|   W 7th Avenue| Arbutus Street|      N|        N|         N|
|POLYGON ((-123.15...|POLYGON ((-123.15...|    15|    -9999|                    |     Kitsilano|               |               |      N|         |          |
|POLYGON ((-123.14...|POLYGON ((-123.14...|    16|      118| Seaforth Peace Park|     Kitsilano|Cornwall Avenue|Chestnut Street|      N|        N|         N|
|POLYGON ((-123.10...|POLYGON ((-123.10...|    17|      139| Mount Pleasant Park|Mount Pleasant|  W 16th Avenue| Ontario Street|      N|        N|         N|
|POLYGON ((-123.10...|POLYGON ((-123.10...|    18|      138| Major Matthews Park|Mount Pleasant|  W 11th Avenue|Manitoba Street|      N|        N|         N|
|POLYGON ((-123.10...|POLYGON ((-123.10...|    19|      137|Jonathan Rogers Park|Mount Pleasant|   W 7th Avenue|Manitoba Street|      N|        Y|         N|
|POLYGON ((-123.09...|POLYGON ((-123.09...|    20|      183|       Thornton Park|    Strathcona|Terminal Avenue|    Main Street|      N|        N|         N|
+--------------------+--------------------+------+---------+--------------------+--------------+---------------+---------------+-------+---------+----------+
only showing top 20 rows

root
 |-- shape: geometry (nullable = false)
 |-- geometry: string (nullable = true)
 |-- ParkId: string (nullable = true)
 |-- RefParkId: string (nullable = true)
 |-- ParkName: string (nullable = true)
 |-- Neighborho: string (nullable = true)
 |-- EWStreet: string (nullable = true)
 |-- NSStreet: string (nullable = true)
 |-- DogPark: string (nullable = true)
 |-- Washrooms: string (nullable = true)
 |-- SpecialFea: string (nullable = true)

渲染

方法参考上一小节。

// 转为像素
sqlText = "select ST_Envelope_Aggr(shape) as boundary from park";
rawDF = spark.sql(sqlText);
rawDF.createOrReplaceTempView("bound");

sqlText = "select ST_Pixelize(shape, 256, 256, (select boundary from bound)) as pixel, shape from park ";
rawDF = spark.sql(sqlText);
rawDF.createOrReplaceTempView("pixels");
rawDF.show(false);
// 选择颜色
sqlText = "select pixel, shape, ST_Colorize(1, 1, 'red') as color from pixels";
rawDF = spark.sql(sqlText);
rawDF.createOrReplaceTempView("pixels");
rawDF.show();
// 渲染
sqlText = "select ST_Render(pixel, color) as image from pixels";
rawDF = spark.sql(sqlText);
rawDF.createOrReplaceTempView("images");
rawDF.show();
// 保存
Dataset<org.apache.spark.sql.Row> images = spark.table("images");
Row[] take = (Row[])images.take(1);
ImageSerializableWrapper image = (ImageSerializableWrapper)take[0].get(0);
new ImageGenerator().SaveRasterImageAsLocalFile(image.getImage(),System.getProperty("user.home") + "/park", ImageType.PNG);
park.png
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,937评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,503评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,712评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,668评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,677评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,601评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,975评论 3赞 396
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,637评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,881评论 1赞 298
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,621评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,710评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,387评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,971评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,947评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,189评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,805评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,449评论 2赞 342

推荐阅读更多精彩内容