topic Re: Spatial Join. Is possible matching output fields ? in Geoprocessing Questions

Spatial Join. Is possible matching output fields ?

PasqualeLanera — Mon, 09 May 2022 10:41:57 GMT

Hello I have ArcGIS 10.3.

I have 2 FC in a GDB, one is a polygon or regular cell of a grid (Grid), the other one is a point feature (PointHabitat). A poygon can contain one or more point. In Point Feature there are two field: AREA, a number that rapresent the surface, and HABITAT, the name of the habitat.

Case 3 point features in a cell

If I do a Spatial Join between Target Feature (Grid) and Join Feature PointHabitat, and choose as Field of Join Features the field AREA Maximum, the output cell have the Field AREA with the value maximum between the 3 point; the Field HABITAT contains the first name of the habitats in alfabetical order.

Instead I want that the output of the Spatial Join will be a cell with the maximum value of the field AREA and for the field HABITAT the name of the habitat of the point feature that has the maximum Area.

I hope it is clear. I'll prove all night but I don't found the solution. Maybe it's need a python script ?

Help me, thanks !

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Mon, 09 May 2022 11:05:15 GMT

I don't have 10.3 to test against, but if you spatial join the points with the polygons to move the cell IDs to the points first (points are the target). Then use a database of query to select the point within each cell that has the highest value for Area (Max query). Then export those points to a new feature class, and then run the spatial join with the GridCells as the target against just the Max value points. This might work without python or a model builder model

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Mon, 09 May 2022 13:30:24 GMT

Thanks Kim

The polygon feature class (FC) "Grid" with over 100,000 cells (each cell is 1 km square) is the target Feature and the point FC "PointHabitats" is the Join Features. Each cell can intersect one or more points.

I don't undestand how select in a new layer within a cell the only point with AREA = max and the corrispondence value of HABITAT. I have to use the command SELECT in ArcGIS ? Or I have to use an external database ?

I prove with the tool SELECT but I don't know the exact SQL statement to write. With: "Target.ID = Target.ID AND AREA is max" does not run. The points within the same cell have the same Target.ID

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Mon, 09 May 2022 17:44:44 GMT

Sorry, just getting back....Quick questions. Is your data in a file geodatabase or an Enterprise GDB (I.E. SQL Server, Post GRE, Oracle, etc.)?

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Mon, 09 May 2022 18:01:51 GMT

In a File Geodatabase.

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Mon, 09 May 2022 21:40:52 GMT

Hmmmm.... finding Max in a file geodatabase seems harder than it should be. Database views are not supported in File GDBs and subqueries on file and personal geodatabases don't seem to support GROUP BY and Summarize cannot tell you which habitat has the Max Area. I am seeing your problem. Looks like Model Builder might be the answer if this is a process you have to repeat frequently. Is this a one time process or will you have to repeat it often?

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Tue, 10 May 2022 08:18:48 GMT

This is a one time process or I will have to repeat some time. But it is very strange that with the Spatial Join I don't solve the problem.

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Tue, 10 May 2022 18:12:41 GMT

I have a model that will create a feature class containing one point for each polygon containing just the Maximum Area and the habitat for that area. You should then be able to join this to your polygons using the polygons unique ID which is also an attribute of each point.

Please keep in mind I tested this in ArcMap 10.8.1 so not 10.3

My test data has different field names and only contains 2 polygons and 13 points. Here are snapshot of the starting tables.

MaxTestPoints:

PolygonTest

Using these two I did a spatial joint using points as the target to move the MyTestValue into the points. The result I called MaxTestPoints_SpatialJoin2

Then I created a model that pulls just one point for each polygon from this feature class and appends it into a new feature class I called "MaxTestPointsFinal". Each point in MaxTestPointsFinal is the point representing the Maximum value for a MyTestValue polygon.... confusing I know, but when you see the output below I think it will make more sense.

The model is pretty simple. The top image is just the model. The images that follow that show important details about the steps:

Iterator on polygon layer, value is the unique ID of the polygon

Select by attribute (gets all records that share max value)

Select by Attribute (with Remove_From_Selection option) to exclude all points that do not fall within the polygon being considered.

The append step just appends the identified point to a feature class of all Max value points.

There might be a MUCH easier way, but given the constraints of file GDB in ArcMap 10.3 I sure couldn't think of it.

Hope this helps.

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 07:10:43 GMT

Thanks Kim,

I an not a expert with Models so it is a little difficult. I have some requests.

1) Why you add the field MyTasteValue in PolygonTest ?

2) MyTasteValue is to identify each polygon, so if there is 4 polygon I will have also TestPoly3 and TestPoly4 ?

3) Why don't use the field ObjectID instead of MyTasteValue

4) Could you add the figure of the Apppend Tool ?

5) Why there is "(2)" or "(3)" after the name of an object in the green box

Could you send me yours GDB or shapefile of the example and the model saved like a tool ?

My email is planera63@gmail.com

Thanks, thanks, thanks

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 08:31:56 GMT

Hello Kim.

Excuse me with wich ESRI platform the solution of my problem will be more easy ?

My polygonal and point feature have over 500,000 records. Do your solution run also if a polygon is empty or if has only one point within ?

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 08:37:20 GMT

Hello.

I askek you to send me the model because it is not possible read the SQL script in the figure 7.

Thanks a lot

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Thu, 12 May 2022 14:18:13 GMT

Rather then sending the data and the model and trying to get them to work on your system I think I can explain a little clearer.

First your questions :

1) I added MyTestValue in PolygonTest to be the unique identifier of the polygon. I am assuming each of your grid cells has a unique ID other than the ObjectID. This is the equivalent of that in the test data.

2) That would be the case in my data if I had added two more polygons. Your data should already have a unique ID assigned to each cell (that is not the ObjectID).

3) ObjectID is a unique Identifier, but Object IDs are not stable and can change when you run analysis (especially analysis that edits or changes the original data) or edit data. Its better to use a stable, user defined, unique ID (like MyTestValue in this case).

4) Here it is. The append tool takes another feature layer as a "Target". I created this as an empty Feature Class to accept the output of the append. I simply created a point Feature Class with columns to accept the values I needed.

5) "(2)" and "(3)" show the same dataset after it has been acted on by a different tool. (3) contains the set of records selected by the first Select by Attribute and (2) contains the set of selected records after the second.

The data I used is simply the 13 points in the original post's image and 2 polygons those points fall within. I created the data in a few minutes just to test the model.

Here is more detail on the model:

The code used in the first Select by Attribute tool (using "New Selection" as the Selection Type)

Value = (SELECT MAX(VALUE) FROM MaxTestPoints_SpatialJoin2 WHERE MyTestValue = '%value%')

The code of the second Select By attribute (Using "Remove from current selection" as the Selection Type)

MyTestValue <> '%value%'

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 15:18:15 GMT

Hello.

Your model RUNNNNNNN with your data!!!!!

Now I have my Feature Class with over 500,000 records.

How can generate values of the field MyTestValue in my Polygon Feature (over 500,000 of records) ?

Can I add the text "TestPoly" or another text to OBJECTID number or to a progressive number using the Field Calculator for my numerous records to obatin an unique ID ? Could you send me the scripts for the Field Calculator to generate this unique ID ?

Another simple solution could be:

copy the OBJECTID into field MyTestValue. Is a good idea ?

Thanks, Thanks Thanks my friend

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Thu, 12 May 2022 15:58:02 GMT

Using the images of the model I created you can create your own model using Model Builder in ArcMap, but customized to user your own data. The images were meant to provide a blue print and the model is not really "plug and play".

You create a model in a tool box you create:

In that tool box you create a new model and edit it:

The names of the tool in the model (for example "Select by Attribute") can be dragged into the model from the "Search" dialog:

The iterator can be added like this:

The code I sent for the tools will have to be customized for your data.

There are some good model builder videos online.

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 16:38:00 GMT

If I want generate the value for the field MyTestValue I would like to use

the Field Calculator from Table:

How can write a script that make a union of a text "TestPoly" and the value of ObjectID or progressive numeber ?

TestPoly4

TestPoly5

...............

TestPoly495054

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Thu, 12 May 2022 17:38:17 GMT

If your Grid Cells do not already have a unique ID other than Object ID I would give them one.

Backup all of your feature classes first and work on copies of those.

Then add a column to your Grid Cells polygon FC called something like GridID (String, 25 length). This will be the equivalent to where I use "MyTestValue".

Use Field Calculator to calculate that field equal to the word "GRID-" with a cell number after it. That will give them each an "unchangeable" ID.... ("Grid-1','Grid-2',.....'Grid-500000')

You have to use the "Python" and "String" options (highlighted)

"Grid-"+ '!OBJECTID!'

This will give you a Grid cell feature class (I'm going to call it "GridCellPolys" here for reference) with a unique ID. That maps to "PolygonTest" in my model.

Spatial join that polygon feature class to your Habitat points to get the equivalent of my MaxTestPoints_SpatialJoin2 feature class (call it something more appropriate like "HabitatPnts_SJ_GridPolys").

Mapping to the model:

PolygonTest = GridCellPolys

MaxTestPoints_SpatialJoin2 = HabitatPnts_SJ_GridPolys

MyTestValue = GridID

Habitat = Habitat

Value = Area

NOTE: The model does not check for if no points fall within a cell. This will likely cause it to fail. Since I don't think Model Builder had an "If Then" condition in ArcMap 10.3 you'll have to do a select by attribute on you Grid Polygons that have at least one point in them and export that out as your new "PolygonTest" (in my model) feature class.

Good luck.

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Thu, 12 May 2022 18:58:24 GMT

Thanks a lot.

I have tested you model also with a polygon without point: no errrors, no fail.

So too work and process to create a point feature with only a point in a polygon: the point with max value and his habitat with ArcGIS 10.3 !!!

Are with ArcGIS PRO or with another platform more easy ?

Thanks very much for the support and excuse me for the time that you spent for me.

Good Bye

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Thu, 12 May 2022 20:29:38 GMT

Your welcome. If my suggestion helped to solve the problem, would you mind also accepting one of my responses as a solution? Thank you.

Re: Spatial Join. Is possible matching output fields ?

PasqualeLanera — Fri, 13 May 2022 13:27:33 GMT

Hello Kim.

Can you expalin two things:

1) Select Layer by attribute and Select by attribute are the same ? If no why you use the first ?

2) In the query: Value = (SELECT MAX(Value) FROM MaxTestPoints_SpatialJoin2 WHERE MyTestValue = '%Value%') why there is '%Value%' and not only Value ?

Re: Spatial Join. Is possible matching output fields ?

KimberlyGarbade — Fri, 13 May 2022 19:09:13 GMT

1) Its a little confusing, but the the first select by attribute selects all points in the HabitatPnts_SJ_GridPolys that have the same value for area as the highest value in the Grid cell you are processing. For instance if the current Grid cell being processed is Grid-25 and the maximum habitat area with this cell is 50 hectares, the first query selects all records in the HabitatPnts_SJ_GridPolys feature class with an Area of 50. That selection set includes the one (or more) points you are interested in returning, plus all other points with an Area of 50. The second select by attribute excludes all points not in Grid-25; leaving you with just the points you want (I.E. those points (one or more) that represent the highest habitat by area in that grid cell).

It is confusing why you have to write two queries when SQL Server could so this no problem in just one, but I think the answer is that ArcMap is limited in its ability to do subqueries, so this was the only way I could get it to work.

2) %Value% is a variable in the model. It is set each time the iterator loops. It is the unique identifier of the polygon you are processing this time through the iterator. It is vital that this be set correctly as the "Field" property of the iterator.