How to create feature class from SQL Server preserving data types?

KulpanowskiDavid — Tue, 30 May 2023 20:47:58 GMT

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

Re: How to create feature class from SQL Server preserving data types?

Anonymous User — Wed, 31 May 2023 13:36:19 GMT

Take a look at sqlalchemy and pandas to convert the sql result to a dataframe that you can then use to create the point featureclass. There are several methods that you can use with the dataframe like the insert cursor or using arcgis spatially enabled dataframe and its to_featureclass method- for example:

import sqlalchemy from sqlalchemy import text import pandas as pd from arcgis.features import GeoAccessor, GeoSeriesAccessor engine = sqlalchemy.create_engine(f"use the sqlalchemy docs for the sql string here") with engine.begin() as conn: tble_data = pd.read_sql_query(sql=text(query), con=conn) # Convert a dataframe (df) to a spatial dataframe (sdf) with point geometrie sdf = pd.DataFrame.spatial.from_xy(df=tble_data, x_column='X', y_column='Y', sr=4364) # Save your feature class in a gdb (or in .shp,...) sdf.spatial.to_featureclass(location=r'C:\MyDB.gdb\MyFeature', overwrite=True)

topic Re: How to create feature class from SQL Server preserving data types? in Python Questions

How to create feature class from SQL Server preserving data types?

Re: How to create feature class from SQL Server preserving data types?