topic Re: Connect to .SDE via Python and read table in Python Questions

Connect to .SDE via Python and read table

GIS_Rookie — Tue, 07 Mar 2023 09:47:39 GMT

Well hello here,

I wanted to know how to make a python script that reads the attribute table from .sde feature class? In my case that feature class is inside feature dataset. At the moment I know how to make it happen with excel, but wanted to reconstruct script a little bit to miss that part where you need to export excel file out.

So, here is a code snippet of how I'm reading latitude and longitude from a excel file:

file_path = r"D:\coordinates\coordinates_excel.xls" dataframe = pd.read_excel(file_path) for i in range(0,len(dataframe)): lat2 = dataframe['lat'][i] lon2 = dataframe['lon'][i] distance = haversine(lat, lon, lat2, lon2) distance_nxt.append(distance)

The goal is to read attribute table (lat, lon) from a shapefile which is in .SDE, and to do further tasks. Right now, this script is reading LAT and LON from excel file. Maybe someone have an idea of how to shoot it right away from .sde database? How to read shapefile attribute table like excel table?

Re: Connect to .SDE via Python and read table

JakeSkinner — Tue, 07 Mar 2023 12:45:45 GMT

Hi @GIS_Rookie ,

Here is an example:

import arcpy # Variables fc = r"C:\Projects\GIS.sde\Crimes" # Path to SDE feature class latField = 'latitude' # Latitude field name longField = 'longitude' # Longitude field name # Create search cursor with arcpy.da.SearchCursor(fc, [latField, longField]) as cursor: for row in cursor: latitude = row[0] longitude = row[1] print(latitude, longitude) del cursor

Re: Connect to .SDE via Python and read table

jcarlson — Tue, 07 Mar 2023 14:37:18 GMT

You might want to check out the ArcGIS Python API, too. You can query an SDE table straight into a dataframe, then use the GeoAccessor class to convert that to a spatial dataframe, and continue working with it from there.

from arcgis.features import GeoAccessor import pandas as pd df = pd.read_sql( 'some_table', 'database_connection_string' ) sdf = GeoAccessor.from_xy( df, 'lon', 'lat', 'spatial_reference' )

Re: Connect to .SDE via Python and read table

GIS_Rookie — Wed, 08 Mar 2023 12:37:30 GMT

Okay, thank you both! Managed this.

The next problem is that X and Y coordinates are into, I guess, nested list?

How to make those XY coordinates as variables again?

For excel table that would be easy:

path = r"G:\FOLDER\TEST_excel.xls" df = pd.read_excel(path) for i in range(0,len(df)): lat3 = df['lat'][i] lon3 = df['lon'][i] distance = haversine(lat, lon, lat2, lon2) dist.append(distance)

In list those XY values look's something like this [(11111111.1111, 22222222.2222) , (44444.4242, 818182388.1239), (etc...)]

pole_xy = [] dist = [] def haversine(lon1, lat1, lon2, lat2): etc... shp = os.path.join(path,r"C:\Users\name\Documents\GIS\PRJ\TEST\DB.sde\DB.NetworkData\DB.FeatureClass") with arcpy.da.SearchCursor(shp, "SHAPE@XY") as rows: for row in rows: pole_xy.append(row[0]) for i in range(0,len(pole_xy)): lat3 = pole_xy['SHAPE@X'] lon3 = pole_xy['SHAPE@Y'] distance = haversine(lat, lon, lat3, lon3) dist.append(distance)

how to get lat3 = all X values and into lon3 = all Y values?