GeoSpark->ST_Intersection
2020-12-01 本文已影响0人
一个懒散的人
1、测试数据(area.csv:)
10.5,32.11,30.50,60.21,33.50,60.21,10.5,32.11,china1
9.51,30.11,32.50,62.21,34.50,62.21,9.51,30.11,china2
11.5,32.11,31.50,64.21,33.50,64.21,11.5,32.11,china3
10.5,31.16,32.51,63.21,35.51,63.21,10.5,31.16,china4
11.5,32.11,30.50,59.21,33.50,59.21,11.5,32.11,china5
50.1,60.31,70.10,80.21,60.23,81.12,50.1,60.31,v1
- 测试数据(area2.csv:)
9.3,31.11,29.50,59.21,32.50,59.21,9.3,31.11,childrenchina1
8.51,29.11,32.50,62.21,34.50,62.21,8.51,29.11,childrenchina2
11.5,32.11,31.50,64.21,33.50,64.21,11.5,32.11,childrenchina3
10.5,31.16,32.51,63.21,35.51,63.21,10.5,31.16,childrenchina4
11.5,32.11,30.50,59.21,33.50,59.21,11.5,32.11,childrenchina5
55.1,65.31,70.10,80.21,65.23,81.12,55.1,65.31,v2
- 测试代码
package txt_demo
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.serializer.KryoSerializer
import org.apache.spark.sql.SparkSession
import org.datasyslab.geospark.enums.FileDataSplitter
import org.datasyslab.geospark.serde.GeoSparkKryoRegistrator
import org.datasyslab.geospark.spatialRDD.PolygonRDD
import org.datasyslab.geosparksql.utils.{Adapter, GeoSparkSQLRegistrator}
import org.datasyslab.geosparkviz.core.Serde.GeoSparkVizKryoRegistrator
object area2_demo {
def main(args: Array[String]): Unit = {
val conf = new SparkConf().
setAppName("SpatialRangeQueryApp").setMaster("local[*]").
set("spark.serializer", classOf[KryoSerializer].getName).
set("spark.kryo.registrator", classOf[GeoSparkKryoRegistrator].getName)
implicit val sc = new SparkContext(conf)
var sparkSession = SparkSession.builder()
.master("local[*]") // Delete this if run in cluster mode
.appName("readTestScala") // Change this to a proper name
// Enable GeoSpark custom Kryo serializer
.config("spark.serializer", classOf[KryoSerializer].getName)
.config("spark.kryo.registrator", classOf[GeoSparkKryoRegistrator].getName)
.config("spark.serializer", classOf[KryoSerializer].getName)
.config("spark.kryo.registrator", classOf[GeoSparkVizKryoRegistrator].getName)
.config("spark.sql.crossJoin.enabled","true") // 这个配置很关键
.getOrCreate()
GeoSparkSQLRegistrator.registerAll(sparkSession)
val polygonRDD = createPolygonRDD(sc,path="D:\\idea\\demo_spark\\es_demo\\src\\data\\area.csv")
val chpolygonRDD = createPolygonRDD(sc,path="D:\\idea\\demo_spark\\es_demo\\src\\data\\area2.csv")
val polygonDf = Adapter.toDf(polygonRDD,sparkSession)
val chpolygonDf = Adapter.toDf(chpolygonRDD,sparkSession)
val testDf =polygonDf
polygonDf.createOrReplaceTempView("p_view")
chpolygonDf.createOrReplaceTempView("c_view")
val rslt = sparkSession.sql(
"""
|select p_view._c1,c_view._c1,
|ST_Intersection(ST_GeomFromWKT(p_view.geometry),ST_GeomFromWKT(c_view.geometry)) as intersection
|from p_view, c_view""".stripMargin)
rslt.show(truncate = false)
}
def createPolygonRDD(implicit sc:SparkContext,path:String):PolygonRDD={
val polygonRDDInputLocation = path
val polygonRDDStartOffset = 0
val polygonRDDEndOffset = 7
val polygonRDDSplitter = FileDataSplitter.CSV // or use FileDataSplitter.TSV
val carryOtherAttributes = true
val objectRDD = new PolygonRDD(sc, polygonRDDInputLocation, polygonRDDStartOffset, polygonRDDEndOffset, polygonRDDSplitter, carryOtherAttributes)
objectRDD
}
}
- 测试结果
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|_c1 |_c1 |intersection |
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|china1|childrenchina1|POLYGON ((12.839857651245545 35.397499999999994, 29.78825622775801 59.21, 32.5 59.21, 12.839857651245545 35.397499999999994)) |
|china1|childrenchina2|POLYGON ((31.050453172205437 60.21, 32.92960725075529 60.21, 19.483667409057173 43.08569800845681, 12.109559422351188 34.07646172904645, 31.050453172205437 60.21)) |
|china1|childrenchina3|POLYGON ((18.525000000000016 43.38512500000002, 30.5 60.21, 30.75856697819315 60.21, 16.647377185678607 39.62049125728561, 14.687748156551335 37.22633579126489, 18.525000000000016 43.38512500000002)) |
|china2|childrenchina1|POLYGON ((16.288370938490992 39.574363076361934, 30.351401869158877 59.21, 32.16448598130841 59.21, 26.620985388579093 52.089296957718645, 16.288370938490992 39.574363076361934)) |
|china2|childrenchina2|POLYGON ((11.739694448233537 33.223231482744524, 32.5 62.21, 34.5 62.21, 11.739694448233537 33.223231482744524)) |
|china2|childrenchina3|POLYGON ((15.229774221444291 38.096287625418086, 23.891138802353808 50.18979797979806, 14.685884412215188 36.758494983277615, 13.235433483790018 34.89537074148298, 15.229774221444291 38.096287625418086))|
|china3|childrenchina1|POLYGON ((15.727232924693523 38.894708844133106, 21.132029622772514 47.56940754454988, 29.5 59.21, 30.073208722741438 59.21, 18.215460006323113 41.90846664558963, 15.727232924693523 38.894708844133106)) |
|china3|childrenchina2|POLYGON ((13.937789864115771 36.02265273190581, 16.496160216138215 40.12883714690183, 25.683194441128062 52.804570070918665, 15.855037283533976 38.464395309156394, 13.937789864115771 36.02265273190581)) |
|china3|childrenchina3|POLYGON ((11.5 32.11, 31.5 64.21, 33.5 64.21, 11.5 32.11)) |
|china1|childrenchina4|POLYGON ((29.070540432523607 58.20160930769567, 30.5 60.21, 33.16897035881435 60.21, 26.400040734382703 51.535701940702346, 14.552604302724369 37.06122525680673, 29.070540432523607 58.20160930769567)) |
|china1|childrenchina5|POLYGON ((32.68149466192171 59.21, 17.47203579418344 40.628008948545855, 30.5 59.21, 32.68149466192171 59.21)) |
|china1|v2 |MULTIPOLYGON EMPTY |
|china2|childrenchina4|POLYGON ((16.047796657703277 39.238459013148116, 32.5 62.21, 34.5 62.21, 11.791455695196936 33.04056133716773, 16.047796657703277 39.238459013148116)) |
|china2|childrenchina5|POLYGON ((32.16448598130841 59.21, 15.422247471371925 37.70436349864101, 30.5 59.21, 32.16448598130841 59.21)) |
|china2|v2 |MULTIPOLYGON EMPTY |
|china3|childrenchina4|POLYGON ((12.524650694285667 33.754564364328495, 14.900589124097635 37.5679455441767, 32.51 63.21, 32.81464174454829 63.21, 13.366446311437688 34.833314845324985, 12.524650694285667 33.754564364328495)) |
|china3|childrenchina5|POINT (11.5 32.11) |
|china3|v2 |MULTIPOLYGON EMPTY |
|china4|childrenchina1|POLYGON ((16.22954372836366 39.50311115375081, 29.763042121684865 59.21, 32.3886271450858 59.21, 30.469239069128683 56.75032835528086, 16.22954372836366 39.50311115375081)) |
|china4|childrenchina2|POLYGON ((13.152933215891089 35.023085396152176, 19.604078094600776 44.41696060572262, 32.5 62.21, 34.5 62.21, 13.152933215891089 35.023085396152176)) |
|china4|childrenchina3|POLYGON ((14.900589124097635 37.5679455441767, 32.51 63.21, 32.81464174454829 63.21, 13.366446311437688 34.833314845324985, 12.524650694285667 33.754564364328495, 14.900589124097635 37.5679455441767)) |
|china5|childrenchina1|POLYGON ((19.238662729381986 43.147776840329044, 30.5 59.21, 32.5 59.21, 19.238662729381986 43.147776840329044)) |
|china5|childrenchina2|POLYGON ((16.78949077940845 39.65448421694573, 30.5 59.21, 32.14441087613293 59.21, 16.78949077940845 39.65448421694573)) |
|china5|childrenchina3|POINT (11.5 32.11) |
|v1 |childrenchina1|MULTIPOLYGON EMPTY |
|v1 |childrenchina2|MULTIPOLYGON EMPTY |
|v1 |childrenchina3|MULTIPOLYGON EMPTY |
|china4|childrenchina4|POLYGON ((10.5 31.16, 32.51 63.21, 35.51 63.21, 10.5 31.16)) |
|china4|childrenchina5|POLYGON ((32.3886271450858 59.21, 13.788828991150957 35.37459292948374, 30.5 59.21, 32.3886271450858 59.21)) |
|china4|v2 |MULTIPOLYGON EMPTY |
|china5|childrenchina4|POLYGON ((13.788828991150957 35.37459292948374, 30.5 59.21, 32.3886271450858 59.21, 13.788828991150957 35.37459292948374)) |
|china5|childrenchina5|POLYGON ((11.5 32.11, 30.5 59.21, 33.5 59.21, 11.5 32.11)) |
|china5|v2 |MULTIPOLYGON EMPTY |
|v1 |childrenchina4|MULTIPOLYGON EMPTY |
|v1 |childrenchina5|MULTIPOLYGON EMPTY |
|v1 |v2 |POLYGON ((64.95110212207209 80.6847210809437, 70.1 80.21, 55.1 65.31, 64.95110212207209 80.6847210809437)) |
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
