Hi Bert,
Below is the source code of python script file. Please note that feature class Parcels and TIF_Districts are in database schema gio in a sde-schema geodatabase in a SQL Server database. I expect the users to know SQL and python and ArcGIS Pro.
"""
This python script finds property parcels inside the City's limit, in a TIF district, and calculates their statistics.
Author: Jianwu Chen - jianwu.chen@cityofrockhill.com
Date: 01/03/2022
"""
import os
import pyodbc
import pandas
import datetime
import arcpy
statistics_csv = r"C:\temp\CityParcels\data\statistics.csv"
aprx_file = r"C:\temp\CityParcels\map.aprx"
pdf_path = r"C:\temp\CityParcels\PDF"
pdf_name = "CityParcels_" + datetime.date.today().isoformat() + ".pdf" # CityParcels_2021-01-01.pdf
pdf = os.path.join(pdf_path, pdf_name)
resolution = 400
image_quality = "BEST"
compress_vector_graphics = False
image_compression = "ADAPTIVE"
embed_fonts = True
layers_attributes = "LAYERS_ONLY"
georef_info = True
jpeg_compression_quality = 80
clip_to_elements = False
output_as_image = False
embed_color_profile = False
connection_str = """
Driver={ODBC Driver 17 for SQl Server};
Server=YOUR_DATABASE_SERVER;
Database=YOUR_GEODATABASE;
Uid=YOUR_LOGIN;
Pwd=YOUR_PASSWORD;
"""
sql_find_city_parcels = """
SET NOCOUNT ON
SELECT TaxMapID, OwnerName, OwnerAddress, OwnerCityStateZIP, PreviousOwner, PropertyAddress, LandUse, ImprovedStatus, BuildingType,
BuildingCount, FinishedSQFT, SaleLandUse, SaleDate, SalePrice, AprLandVal, AprBldgVal, AprMiscVal, AprTotVal, TaxLandVal, TaxBldgVal, TaxMiscVal,
TaxAgCredit, TaxTotVal, AsdLandVal, AsdBldgVal, AsdMiscVal, AsdTotVal, Shape.STArea()*0.0000229568 AS 'Acreage', Jurisdiction
INTO #prcl
FROM gio.Parcels
WHERE Jurisdiction = 'ROCK HILL'
ALTER TABLE #prcl ADD TIF_District varchar(20)
UPDATE #prcl
SET TIF_District = t.Name
FROM (
SELECT tif.NAME, p.TaxMapID
FROM gio.TIF_Districts AS tif
INNER JOIN gio.Parcels AS p ON p.Shape.STCentroid().STWithin(tif.Shape) = 1 AND p.Jurisdiction = 'ROCK HILL'
) AS t
WHERE t.TaxMapID = #prcl.TaxMapID
SELECT *
INTO #statistics
FROM (
SELECT CASE WHEN TIF_District IS NULL THEN 'Non-TIF District' ELSE TIF_District END AS 'Measurement\District',
COUNT(TaxMapID) AS 'Number of Parcels',
FORMAT(SUM(Acreage), '#,##0.00') AS 'Parcel Acreage',
FORMAT(SUM(AprLandVal), '#,##0') AS 'Market Land Value($)',
FORMAT(SUM(AprBldgVal), '#,##0') AS 'Market Building Value($)',
FORMAT(SUM(AprMiscVal), '#,##0') AS 'Market Misc Value($)',
FORMAT(SUM(AprTotVal), '#,##0') AS 'Market Total Value($)',
FORMAT(SUM(TaxLandVal), '#,##0') AS 'Taxable Land Value($)',
FORMAT(SUM(TaxBldgVal), '#,##0') AS 'Taxable Building Value($)',
FORMAT(SUM(TaxMiscVal), '#,##0') AS 'Taxable Misc Value($)',
FORMAT(SUM(TaxAgCredit),'#,##0') AS 'Agriculture Credit($)',
FORMAT(SUM(TaxTotVal), '#,##0') AS 'Taxable Total Value($)',
FORMAT(SUM(AsdLandVal), '#,##0') AS 'Assessed Land Value($)',
FORMAT(SUM(AsdBldgVal), '#,##0') AS 'Assessed Building Value($)',
FORMAT(SUM(AsdMiscVal), '#,##0') AS 'Assessed Misc Value($)',
FORMAT(SUM(AsdTotVal), '#,##0') AS 'Assessed Total Value($)'
FROM #prcl
GROUP BY TIF_District
UNION
SELECT 'City-Wide Total' AS 'Measurement\District',
COUNT(TaxMapID) AS 'Number of Parcels',
FORMAT(SUM(Acreage), '#,##0.00') AS 'Parcel Acreage',
FORMAT(SUM(AprLandVal), '#,##0') AS 'Market Land Value($)',
FORMAT(SUM(AprBldgVal), '#,##0') AS 'Market Building Value($)',
FORMAT(SUM(AprMiscVal), '#,##0') AS 'Market Misc Value($)',
FORMAT(SUM(AprTotVal), '#,##0') AS 'Market Total Value($)',
FORMAT(SUM(TaxLandVal), '#,##0') AS 'Taxable Land Value($)',
FORMAT(SUM(TaxBldgVal), '#,##0') AS 'Taxable Building Value($)',
FORMAT(SUM(TaxMiscVal), '#,##0') AS 'Taxable Misc Value($)',
FORMAT(SUM(TaxAgCredit),'#,##0') AS 'Agriculture Credit($)',
FORMAT(SUM(TaxTotVal), '#,##0') AS 'Taxable Total Value($)',
FORMAT(SUM(AsdLandVal), '#,##0') AS 'Assessed Land Value($)',
FORMAT(SUM(AsdBldgVal), '#,##0') AS 'Assessed Building Value($)',
FORMAT(SUM(AsdMiscVal), '#,##0') AS 'Assessed Misc Value($)',
FORMAT(SUM(AsdTotVal), '#,##0') AS 'Assessed Total Value($)'
FROM #prcl
) AS t
"""
sql_get_city_parcel_statistics = """
SELECT * FROM #statistics ORDER BY 'Number of Parcels'
drop table #prcl
drop table #statistics
"""
# 1. Use the SQL Server database to find parcels inside the City's limit, and calcluate their statistics, and
# export statistics table from database to python's dataframe in local computer's memory.
cnx = pyodbc.connect(connection_str);
cursor = cnx.cursor();
cursor.execute(sql_find_city_parcels)
cnx.commit()
print("Parcels found inside the City's limit.")
df = pandas.read_sql(sql_get_city_parcel_statistics, cnx)
print("Parcels' statistics read into pandas dataframe.")
cursor.close()
del cursor
cnx.close()
# 2. Export the statistics to CSV file.
statistics = df.T
statistics.to_csv(statistics_csv, header=False) # for the table in the PDF map.
print("Export statistics to CSV file.")
# 3. Create a PDF map.
aprx = arcpy.mp.ArcGISProject(aprx_file)
for lyt in aprx.listLayouts():
if lyt.name == "City Parcels":
lyt.exportToPDF(pdf, resolution=resolution, image_quality=image_quality, compress_vector_graphics=compress_vector_graphics,
image_compression=image_compression, embed_fonts=embed_fonts, layers_attributes=layers_attributes, georef_info=georef_info,
jpeg_compression_quality=jpeg_compression_quality, clip_to_elements=clip_to_elements,
output_as_image=output_as_image, embed_color_profile=embed_color_profile)
print("PDF map created.")
print("Job done!")
