nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

using Shape@ in where clause

PaulDavidson1

I saw a posting from 2012 that answered the question if SHAPE@X or SHAPE@Y or SHAPE@XY could be used in forming the where_clause in an arcpy.da.XCursor (search, update or insert)

Now that it's 2015, I'm wondering if that's still correct.

We have some data with NULLS in the ShapePoint data and I'm flipping them all to 0,0 in our state plane projection by:

    fields = ['OID@', 'SHAPE@X', 'SHAPE@Y','SP_ID' ]
    with arcpy.da.UpdateCursor(myFC, fields) as cursor :
        for row in cursor:
            if row[1] == None or row[1] == 0.0 or row[2] == None or row[2] == 0.0 :
                row[1] = 0.0
                row[2] = 0.0
                cursor.updateRow(row)

I thought it would be cleaner to have a query string in the UpdateCursor but so far I've had no luck with

queryString = '"SHAPE@X" = NULL or "SHAPE@X" = 0 or "SHAPE@Y" = NULL or "SHAPE@Y" = 0'
or
queryString = 'SHAPE@X = NULL or SHAPE@X = 0 or SHAPE@Y = NULL or SHAPE@Y = 0'

or other variations thereof.

I've concluded that using the SHAPE part of a geodatabase in a da.Cursor query string is just not possible.

Is that correct?

Any suggestions?

Find more posts tagged with

Accepted answers

ModyBuchbinder

I would try to use the Calculate Geometry to create some numeric field from some property of the geometry.

Then try to use this field in the expression.

I am not sure what will be the values in your case but it should be easy to find after running a test.

You will have to recalc the attribute after each geometry change.

You can use the Add Geometry Attributes if you want everything in one script.

Have fun

Mody

All comments

PaulDavidson1

Thanks Joshua...

I guess the situation of NULL interpretation is what I'm up against.

When I create a query using SHAPE IS NULL, it returns no records.

But I find >300 records that have SHAPE@X = None when i iterate through the rows in arcpy.

I guess my current method of reading all records and examining the two XY fields is the best way to go.

JoshuaBixby

If you want to see if a SHAPE field has NULL, the syntax is simply "SHAPE IS NULL". Whether a database table with a shape field contains a NULL is not a spatial question, it is simply a question of checking for data in a field, regardless of the data type.

One has to be careful, though, when talking about NULLs because some parts of ArcGIS software actually insert empty geometries in place of NULL when there is a business record without accompanying spatial information. Unlike NULL which isn't a valid geometry, or even data of any type, empty geometries are valid geometries. Depending upon whether a field gets populated with NULL or an empty geometry determines whether a "SHAPE IS NULL" query will find the records of interest. If you have or need to find empty geometries, then the situation gets more involved.

Unfortunately, Esri has different parts of the ArcGIS platform handle the situation differently, so you are never quite sure what you are getting when you start querying for records with "missing" spatial data.

When using the ArcPy Data Access cursors, they treat NULLs and empty geometries as None, i.e., they don't distinguish between the two when returning shape field information. Personally, I think it is a joke and have submitted an enhancement request, but I haven't been able to gain traction with it.

PaulDavidson1

Thanks Mody, that's a good idea.

I'll probably just stick with the process of looping through the data looking for nulls (none) since it works and is written.

I have done the Add XY before to see the data and so I will play with that.

it would add a number of steps to a script, have to remove any existing XY columns and Add them back in and then use a where clause. But I suspect that would save no time over just looping through the points and might even be slower.

I can envision why we don't have access to SHAPE info in the where clause since each database type (Oracle, MS SQL, etc...) probably has a very different and complex geo part of the geodatabase. (Try following links through Oracle tables!) And for anything beyond a Point layer, we're into lots of complexity.

But still, it would be nice to have that access in the where clause.

PaulDavidson1

Totally agree that what we're seeing is very odd.

I was not aware that arcpy gives a null shape an X,Y of 0,0.

I'll have to look for those posts. Thought I'd searched for info on Nulls, Nones, etc... but obviously didn't hit the right search combo.

Here's where it gets even stranger.

These (None,None) points are not visible on the screen and cannot be selected in ArcMap via the map with the exception of doing an inverted selection. i.e. Select everything in our organization's boundary and then invert the selection and we can pick up the Nones that way.

We're working in a Harn State plane projection for New Mexico.

We can select the points from within the attribute table and if we zoom to a point, it will jump the map to a location that is basically about halfway between our 0,0 point and our boundary.

I suspect that's just an artifact of trying to locate a null.

I am pretty sure these points are coming in via an arcpy script/process that I inherited.

Still wading through it. In this case, if the points without an XY would be put to 0,0, that would be useful to us as that is how the process was supposedly designed. New, unplaced points go to 0,0 and an analyst can then pick them up and move them to where they belong.

Thanks for the info and help.

DanPatterson_Retired

if you create a null shape using arcpy, the shape is sadly given an x and y value of 0,0. This is discussed in several blog posts on geonet by myself and others. A mulitipart shape uses None or its equivalent as a separator to separate the parts to test whether the next object in a point list is a point. the None, None sounds strange for X and Y values for a point's x and y.

A simple test, for point data would be to manually select the points on screen and check which records got selected in the table. If you have squirrley points there should be a few records unselected, unless you are working at the equator.

PaulDavidson1

Actually Dan, the issue here is we have some squirrely Shape:Point data.

For the Nulls, I can “probably” just check the X value, but given what I’ve seen with the 0 data…

For 0, I’ve come across data of the format (0, yyyyyyyy) and (xyz,0)

How those values got into the data, I have no idea. I believe they have possibly been there for years.

Hence the check for X=0 and Y=0 and if I’m doing that and finding those cases, I figurred might as well verify we don’t have some odd stuff with (None,Y)

Or (X,None) I haven’t come across any like that but we’re still working on tracking down how this got in there in the first place.

While this might be contrived (no argument there) the contrivance is in production data and needs to be cleaned out.

My initial code did query first.

When you say query the field manually, I’m guessing you mean do an Add XY Coordinates via ToolBox.

Did that a day or so back. Interestingly, it seemed to have missed the one (0,0) point and returned it as None,None.

Since that makes no sense, it’s more likely I just missed it in the large array or was off by one in an index.

DanPatterson_Retired

I agree but I would also query the field manually first as a test. There is no reason to test for Y since if X meets the condition, Y will as well for anything other than contrived data. The shape will either be None ( ie for a multipart shape separator) or its X ( ie your row[0].X ) will be 0

ModyBuchbinder

I would try to use the Calculate Geometry to create some numeric field from some property of the geometry.

Then try to use this field in the expression.

I am not sure what will be the values in your case but it should be easy to find after running a test.

You will have to recalc the attribute after each geometry change.

You can use the Add Geometry Attributes if you want everything in one script.

Have fun

Mody

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.