Create polygon from json

[mrj22]

Hey.
I have been importing a very large and complex json file. The last bit is giving me some problems.
I am trying to import a series of coordinates and create a polygon. The following is a representative section:

{
	"name": "Test",
	"type": "OFA",
	"areas": [
		{
			"name": "Area1",
			"type": "AO",
			"areaId": "c797b1ad-0933-4e4c-8876-2a77b85464a5",
			"geometry": {
				"type": "POLYGON",
				"coordinates": [
					[
						[
							-77.29052240438029,
							34.721088784223947
						],
						[
							-77.47444321860393,
							34.6875240918703
						],
						[
							-77.44156351997177,
							34.54401790721537
						],
						[
							-77.29668734787383,
							34.48442345344459
						],
						[
							-77.17520021940087,
							34.547199636055989
						],
						[
							-77.24917954132323,
							34.672210990230329
						],
						[
							-77.29052240438029,
							34.721088784223947
						]
					]
				]
			},
			"description": "test area of ops"
		}
	],
	"start": 1626825600000,
	"mId": "74be7e16-a5d7-4557-a34b-b69fc5e6f59b",
	"description": "Test",
	"mArea": ""
}

The closest I can get is with this:

CALL apoc.load.json("test.json")
yield value as file
UNWIND file.areas as areas

UNWIND areas.name as areaName
UNWIND areas.type as areaType
UNWIND areas.areaId as areaId

UNWIND areas.geometry AS geometry
UNWIND geometry.coordinates as geometryCoordinates
UNWIND geometryCoordinates as geoCoord

WITH areaName, areaType, areaId, collect(point({latitude: toFloat(geoCoord[0]), longitude: toFloat(geoCoord[1])})) as points

MERGE (areas:Areas {wkt:points, name: areaName, type: areaType, areaId: areaId})
RETURN areas.name, areas.type, areas.areaId, areas.wkt

The above is based on previous posting: How to create nodes in nested array of JSON - #14 by ameyasoft

My understanding is that if I am to be looking for points in a poly it should be formatted like so:

create(aor99:AREAS{name:'AOR99', areaId:'c797b1ad-0933-4e4c-8876-2a77b85464a5', type:'AO', wkt:'POLYGON((-77.2905 34.7210, -77.4744 34.6875, -77.4415 34.5440, -77.2966 34.4844, -77.1752 34.5471, -77.2491 34.6722, -77.2905 34.7210))'})

But what I get is more like:

Any suggestions would be greatly appreciated.
Thanks,
Michael

[michael_hunger]

I think yes with the neo4j-spatial library you'd store the polygon as WKT or WKB in the format you described.

So you have to take your point list and turn it into a polygon. Something like this.

Would be nice to have a function for that, feel free to create a GitHub issue in APOC or the spatial library.
Like [apoc.]spatial.wktPoly.fromPoints(points) or similar /cc @craig.taverner

WITH *, 'POLYGON(('+apoc.text.join([x in points | x.latitude+', '+x.longitude],", ") +'))' as poly

don't remember if the last point needs to be repeated at the end in POLYGON

[mrj22 · Accepted solution]

Hey Michael.
Thanks for the reply. I realized that the extra commas and brackets were the first part to tackle.
Here is a snippet of how I solved it:

WITH new_areas, geometryCoordinates, collect([(geoCoord[0]), (geoCoord[1])]) as polys

//convert list of lists to big string
with new_areas, polys, apoc.convert.toString(polys) as polystring

//remove all double brackets (']]') and replace with double parens (')')
with new_areas, polystring, apoc.text.regreplace(polystring, '\]{2}', ')') as output1

//remove all double brackets ('[[') and replace with double parens ('(')
with new_areas, output1, apoc.text.regreplace(output1, '\[{2}', '(') as output2

//remove all commas (,)
with new_areas, output2, apoc.text.regreplace(output2, '\,', '') as output3

//remove all single left brackets ('[')
with new_areas, output3, apoc.text.regreplace(output3, '\[', '') as output4

//remove all single left brackets (']') and replace with commas
with new_areas, output4, apoc.text.regreplace(output4, '\]', ',') as clean_poly
create(areas:AREAS{name: new_areas.name, areaId: new_areas.areaId, type: new_areas.type, wkt: 'POLYGON(' + clean_poly + ')'})

I realize that I could do all the regexes in one line, but I thought seeing the results as I iterated thru the results helped a lot