Multipoint to Point

SimonGIS — Sun, 18 Feb 2024 00:32:52 GMT

Pretty sure this is going to be an easy one, but looking for the most efficient way to take a multipoint layer from a public feature service where there is ever only one point in each feature, and convert it to point when loading a dataframe.

url = "https://services6.arcgis.com/GB33F62SbDxJjwEL/arcgis/rest/services/Vicmap_Features_of_Interest/FeatureServer/1" query = "feature_subtype = 'aged care'" aged_care_df = spark.read.format("feature-service").load(url)\ .withColumn("shape", ST.transform("shape", 4326)) \ .filter(query)

This returns a points geometry object:

I have tried using ST_GeometryN to then convert this to a point geometry type.

attempt1 = aged_care_df.select(ST.geometry_n("shape", 1))

But this returns a series of nulls:

Would like to avoid doing any string manipulation in python, and looking for the best way to convert these into point geometry types.

Longer term goal is to end up with a dataframe with an array of points for the setStops parameter in the Create Routes tool.

Re: Multipoint to Point

SBattersby — Sat, 24 Feb 2024 22:25:36 GMT

I'm not sure if this is the most efficient, but it works...

The first thing I noticed was the the feature service you pointed to did actually have some true multipoint features. I checked like this:

# count number of points aged_care_df\ .select(ST.num_geometries("shape").alias("point_count"))\ .sort("point_count", ascending=False)\ .show()

+-----------+ |point_count| +-----------+ | 2| | 2| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| | 1| +-----------+ only showing top 20 rows

And see there are two records that have 2 points each. It's sort of interesting that the points in each have the same coordinates when I filter and print out just these two records...but, still, they are actually multipoint, so we'll have to deal with that to do the conversion

+-----------+--------------------------------------------------------------------------------------------+--------+ |point_count|shape |OBJECTID| +-----------+--------------------------------------------------------------------------------------------+--------+ |2 |{"points":[[145.01641305966146,-36.58713183554724],[145.01641305966146,-36.58713183554724]]}|46169 | |2 |{"points":[[147.59699327673584,-37.09840182302235],[147.59699327673584,-37.09840182302235]]}|46566 | +-----------+--------------------------------------------------------------------------------------------+--------+

Since the multipoint geometries need to be exploded to convert to points (you can't just cast a multipoint to a point), I used ST_Points to create an array of points for each multipoint, then exploded those, and used ST_Point to turn the coordinates back into point geometries.

All in all, it looked like this - note that I drop the point_array that I created as an intermediate step, since I don't need that once I create the point geometry.

aged_care_df2 = aged_care_df.select("*", F.explode(ST.points("shape")).alias("point_array"), ST.point(ST.x("point_array"), ST.y("point_array")).alias("geom_point"))\ .drop("point_array")

That gives me individual records with the original Shape (multipoint) and the new geom_point (point). There will be two records with the same OBJECTID and other attributes since those records happened to really have a multipoint feature.

Example of one record:

-RECORD 0---------------------------------------------------------------------- OBJECTID | 1519 ufi | 67865934 pfi | 1271613 feature_id | 1271613 parent_feature_id | null feature_type | care facility feature_subtype | aged care feature_status | null name | YALLAMBEE LODGE COOMA name_label | Yallambee Lodge Cooma parent_name | null child_exists | null auth_org_code | 110 auth_org_id | null auth_org_verified | 2023-05-31 17:00:00 vmadd_pfi | null vicnames_id | -1959170 vicnames_status_code | 11 theme1 | null theme2 | null state | NSW create_date_pfi | 2021-06-15 04:20:27 superceded_pfi | null feature_ufi | 67865934 feature_create_date_ufi | 2023-06-27 00:37:13 create_date_ufi | 2023-06-27 00:37:13 shape | {"points":[[149.130545553053,-36.220029363958275]]} geom_point | {"x":149.130545553053,"y":-36.220029363958275} only showing top 1 row

I hope this helps with some ideas on how to move forward with your project.

-Sarah.

Re: Multipoint to Point

SimonGIS — Thu, 21 Mar 2024 04:33:23 GMT

Thank you loads Sarah, this works great

topic Re: Multipoint to Point in GeoAnalytics Engine Questions

Multipoint to Point

Re: Multipoint to Point

Re: Multipoint to Point