An explanation of the background:
We have ambulance data in a sql server relational database. It has longitude and latitude data for emergency incidents and the goal is to bring this data into ArcGIS Pro 3.x as a point feature class. In other words, I want to programmatically query data out of our relational database, the data would flow into a file geodatabase preserving the date time fields, then map the XY fields into a point feature class. The code I wrote serves me well, but I want to create a geoprocessing tool for other users.
The ideal pseudocode would look like:
1) query SQL Server and select longitude, latitude and date/time fields and other data.
2) flow the results of this query to a file geodatabase preserving the date/time fields. In other words, string fields remain as strings, floats remain as floats and date/times remain as date/times. The data is now in a geodatase. Now I can do calculations on the date time stamps such as response intervals.
3) Create a point feature class using this data. The data is now points that can be mapped out. Now I can do spatial analysis on the points such as areas of growth, or clustering analysis.
My current solution is to use PyODBC to query the database, export the results to a comma separated values file, then use arcpy.management.XYTableToPoint() to create the points feature class.
My question is:
1.) First, is PyODBC the best way to do this? (In my research I have found ArcSDE but reading the ESRI documentation makes it seem like SDE is all about geodatabases, and my SQL Server database is not spatially enabled.)
2.) Second, exporting to a .csv file just seems very inefficient, Can I skip this intermediate step? How can the data flow from SQL Server to the file geodatabase and preserve the original date time formatting?
The code I am using is shown below. This gives you an idea of what I am trying to accomplish.
# -*- coding: utf-8 -*-
# ArcGIS Pro 3.1.1
# Python 3.7
import pandas as pd
import pyodbc
import arcpy
cnxn = pyodbc.connect('DRIVER={SQL Server}; SERVER=my_server; DATABASE=my_database; Trusted_Connection=yes')
cursor = cnxn.cursor()
query = """
SELECT
Incident_Primary_Key
, Longitude
, Latitude
, Date_of_Emergency
FROM Emergency_Incidents
WHERE Date_of_Emergency > 'january 1, 2023'
"""
df = pd.read_sql(query, cnxn) # execute the query and put results into a Pandas dataframe.
df.to_csv('c:\\emergency_incidents.csv', sep=',', encoding='utf-8') # export the results of the query to a csv file. How to skip this step?
print(df.head(n=10)) # check the data
in_table = r"C:\emergency_incidents.csv" # open the csv file and use it as input data
out_feature_class = r"C:\Scratch_v2.gdb\emergency_incidents" # my file geodatabase for the project
x_coords = "Longitude"
y_coords = "Latitude"
coordinate_system = 'GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119521E-09;0.001;0.001;IsHighPrecision'
# make points out of the data
arcpy.management.XYTableToPoint(in_table = in_table, out_feature_class = out_feature_class, x_field = x_coords, y_field = y_coords, z_field = None, coordinate_system = coordinate_system)
# close the cursor and connection
