Can I rank spatial data?

[Johnny_H]

Hi all,

I have a set of spatial data (X,Y,Z) that I wish to rank in excel. The aim is to be able to list the data points from East to West, North to South (and if possible North-East to South-West but this is less important for now). An added difficulty with this is that not all data points are unique, some points have the same X and Y with only a difference in Z value, in this instance I would want the data point with the highest Z value to be ranked the highest.

Is there some sort of spatial indexing tool that I can use to do this?

Hope all this is clear!

Cheers,

Jon

 

[shg]

It's just a sorting problem, right?

Ignoring Z for the moment, how would you sort a list of x,y values to get the order you want?

 

[Johnny_H]

I have a few different situations but I want to sort from East to West as well as from North to South...

E.g. Using just X and Y, if I have data spatially arranged as below (x marks the point) I want to rank them to the following numbers in ''.

x'3' x'1'

x'4' x'2'

x'5'

 

[shg]

You can sort as many ways as you like, but you have to choose an order. It's conceptually impossible have a list that is sorted both E-W and N-S. To pick the simplest example, how should this be sorted?

	A​	B​
1​	x​	y​
2​	1​	2​
3​	2​	1​

 

[Johnny_H]

Thanks shg, just to be clear is your top row the excel column letters and your first column the excel row numbers? X and Y therefore being the title row of the table? If that is the case then the numbers would be sorted 2, 1, 1, 2, that is to say B2, B3, A2, A3 in terms of excel cell references.

Could a solution be...

-Rank my x column from largest to smallest
-In the case of a duplicate result in the X column have some kind of if statement that if the value in the cell below is the same as the target cell then it looks to the corresponding values in the Y column. The overall rank would then be applied to the Highest X value with the highest Y Value.

	A	B		C
1	X	Y		Desired Rank
2	192558.3	57642.097		2
3	192558.3	57647.097		1
4	192550.8	57534.597		3
5	192550.8	57524.597		4

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

In the above example the X values in the A column are sorted from largest to smallest.

 

[shg]

For your example, do a two-way sort, descending by x and descending by y.