map with a statistics table

Question

Today I had a little fun in playing ArcGIS Pro to create a map with an inserted table. The data source of the table was create using SQL (specifically select ... group by ...). The data processing and map creation are a automation using a python script file. Cheer.

CityParcels_2021-12-30.pdf

JianwuChen · Answer

Hi Amelia,

My bad I didn't look at the requirements on post. Please relocate the post to wherever is appropriate. thanks,

JianwuChen · Answer

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:	emp\CityParcels\data\statistics.csv"
aprx_file = r"C:	emp\CityParcels\map.aprx"
pdf_path = r"C:	emp\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!")

AmeliaBradshaw · Answer

Hello @JianwuChen thank you for posting this! This script and the result you posted do not fit under the category of 'Ideas', as we have them configured for Esri Community. Ideas request improvements to be made to Esri software applications, rather than novel scripts or workflows user create on their own using existing functionality in an application such as ArcGIS Pro. Please review the Community Idea Exchange Guidelines for future reference. Let me check to see what would be the best board for you or other users to post scripts and workflows to, as 'Questions' does not seem to fit either. I will get back to you shortly on this.

Thank you and have a happy New Year!

Amelia