MegPeterson

Symbolize point based on related table record entry using Python.

Discussion created by MegPeterson on Apr 18, 2012
Latest reply on Apr 20, 2012 by MegPeterson
Hi all - I am a newbie to Sql and Python and am trying to convert some sql statements that a consultant put together for our old IMS/SQL set up, to work with our new ArcSDE/SQL set up and am wondering what the right approach is. At the end of this post are the original Sql statements for reference. I feel like this is an easier solution through Python and ArcMap (V.10) etc, but I'm just not putting it together.

The situation is:

I have a point file that has a 1:Many relationship with a non-spatial table. As an example: Point #1 has 3 different maintenance issues that need fixing at that location.

The symbology of the points are based on the completion of the issues:
???When nothing has been fixed, the point is symbolized as a red circle.
???When some things have been fixed, the point turns to a green, half circle.
???When all 3 issues have been fixed, the point turns to a green circle.

In the past, this was accomplished by basing the symbology on a "STATUS" field that referenced the 'CompletedDate' field in the related table. Whenever a date was entered in the 'CompletedDate' field the 'Status' field will automatically update and voila(!), the point color will change.

???If the date was null (ie: nothing had been worked on), the Status field would = 0, and the point color = Red.
???If the date was not null, Status = 1 (and the point would be half green), and
???If the CompletedDate (as a DateCount) in the related table was equal to the number of issues in the RecordCount field, the point would turn green.

I'm wondering if this is something that I can run from inside the MXD through Python and the Field Calculator instead of running it in the acutal SQL database. We're using Enterprise v.10, SQL Server 2008, Flex 4.0 (where the editing will happen). We are not using versioned tables.

Any words of wisdom, or suggestions on how to do this in Python using the Field Calculator somehow with if and else statements?

Thank you, Meg

____________________________________
STORED PROCEDURE

CREATE PROCEDURE [sql_data].[sp_Update_ADASTATS]
AS
truncate table sql_data.ADASTATS;
INSERT INTO sql_data.ADASTATS SELECT DISTINCT FACILITYZONEID UNIQCODE, '0', COUNT(*) FROM sql_data.ADA_Actions GROUP BY FACILITYZONEID;
UPDATE sql_data.ADASTATS SET STATUS=1 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT FACILITYZONEID UNIQCODE FROM sql_data.ADA_Actions WHERE CompletedDate IS NOT NULL);
UPDATE sql_data.ADASTATS SET STATUS=2 WHERE sql_data.ADASTATS.UNIQCODE IN (SELECT DISTINCT UNIQCODE FROM sql_data.vw_ADASTATS WHERE DateCount = RecCount);
GO

____________________________________
VIEW

SELECT Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT, COUNT(Sql_data.ADA_Actions.CompletedDate) AS DateCount
FROM Sql_data.ADA_Actions INNER JOIN
Sql_data.ADASTATS ON Sql_data.ADA_Actions.FacilityZoneID = Sql_data.ADASTATS.UNIQCODE
WHERE (Sql_data.ADA_Actions.CompletedDate IS NOT NULL)
GROUP BY Sql_data.ADASTATS.UNIQCODE, Sql_data.ADASTATS.RECCOUNT

Outcomes