# How do you make a SQL join query for a NULL in a search cursor in ArcPy?

I am trying to learn how to do some error catching/avoiding in ArcPy. Here is a simplified version of what I would like to do. I would like to take some points, and then check to see if there is a line in a line feature class with a matching ID to the point, and the same thing to match each point to a polygon. If each point doesn't have a matching line and polygon, I want to skip that point and move on to the next. Can someone tell me what I'm doing wrong? Here is an example script showing how I am trying to do this:

# Import arcpy module
import sys, os, time, arcpy, smtplib
from arcpy import env
from arcpy.sa import *

env.overwriteOutput = True

env.workspace = r"D:\WorkingDirectory\Geoprocessing\Work.gdb"
PointFC = r"D:\WorkingDirectory\Geoprocessing\Work.gdb\points"
LineFC = r"D:\WorkingDirectory\Geoprocessing\Work.gdb\lines"
PolygonFC = r"D:\WorkingDirectory\Geoprocessing\Work.gdb\points"

try:
rows = arcpy.SearchCursor(PointFC)
for row in rows:
Pt_id = row.pt_id
PTpoly_id = row.poly_id
PTline_id = row.line_id

#This section if for skipping null values to help prevent crashes
arcpy.MakeFeatureLayer_management (PolygonFC,  "Polylyr")
#This is where I get an error - it doesn't like the IS
PolyIDnull = Poly_idField IS NULL
arcpy.AddMessage('\nThis site does not have a polygon : ' + str(row.pt_id))
if PolyIDnull:
continue

arcpy.RemoveJoin_manaement(row, "Polylyr")

arcpy.MakeFeatureLayer_management (LineFC,  "Linelyr")
LineIDnull = Line_idField IS NULL
arcpy.AddMessage('\nThis site does not have a line : ' + str(row.pt_id))
if LineIDnull:
continue
arcpy.RemoveJoin_manaement(row, "Linelyr")

except Exception, e:
import traceback
arcpy.AddError(str(e))

-
Not sure if this is affecting your stated error, but your PolygonFC variable (line 11) is pointing to your point feature class. –  KevinMayall May 26 '11 at 18:38

I see two things straight away that are going to make your interpreter spit grits at you:

# This is where I get an error - it doesn't like the IS
PolyIDnull = Poly_idField IS NULL


You need a test here, not a statement. What you did is not a test for a value, but actually setting the value. Test like so:

# I'm unsure of this. NULL in Python is None, but a null value may actually be an
# empty string in this case (if PTpoly_id = "") .
if (PTpoly_id = None):
arcpy.AddMessage('\nThis site does not have a polygon : ' + str(row.pt_id))


The other problem I already addressed in the above code. IF` statements should (usually) be followed by a colon and a line break, with the conditional statements indented inside of it.

After taking a careful look at your intended process and the code, I must admit that I am baffled as to why you are going about it as you are. Given what you wish to do, I would recommend doing two inner joins (keep only matching records)- once from the point file to the line layer, and again from that resulting file to the polygon layer. That should give you only the points which appear in both. However, if you only wish to see which appear in one OR the other, simply do two left joins (keeping all records) instead. What you have here is quite a confusing workflow that I cannot help but feel is unnecessary.

-
I was told that doing geoprocessing on joined records causes your processes to run VERY slowly. I was trying to figure out a way to just check if a matching record exists in the line or polygon feature classes and if it doesn't, skip that point. I see now that I got some indents and colons wrong - I was trying to edit my actual code to just show a simplified version and I guess I didn't get it in the text box very well. I guess what I really want to know is--what is the best way to skip a record if it doesn't have the input features from another feature class for geoprocessing? –  Christy-Ann Archuleta May 26 '11 at 16:56
Also thought I should mention, if my logic is "baffling" it is because I am a beginner, and I am doing my best. That is why I am asking for help;-) –  Christy-Ann Archuleta May 26 '11 at 17:01
Nothing wrong with being a beginniner! Just wanted to let you know that my answers may be off considering my initial confusion :).If you want to avoid performing geoprocessing on joined records (I would not do so, either), simply export the feature class with a join to a new feature class. Presto chango, the resulting set no longer has a join on it, and can be dealt with as if it was any other feature class. I am rather certain this will be faster than any other sort of geoprocessing, especially involving cursors. –  Nathanus May 26 '11 at 17:18
Thank you so much for your suggestions! I had at first considered just exporting the joins to a new feature class, but I was really hoping there was some way to just "check" with the code. At my point in learning Python, I'm really trying to learn how to do error catching, and make my scripts more "automated". I was thinking that if I give my script to someone else, that would be the cleanest way for it to function for them. Maybe not. I switched to joining the tables before the loop and putting marker fields indicating matches and then setting up the search cursor with a def-qry for that. –  Christy-Ann Archuleta May 26 '11 at 21:40
Adding a Key field was going to be my next suggestion, but it involved doing a join first as well, so wasn't sure if that would really be a solution for you. What you could do is create an inner join that drops all unmatched records and run an update cursor on the remaining records to update the key field with a marker for a successful join. –  Nathanus May 26 '11 at 22:47