Connect to .SDE via Python and read table

Question

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?

GIS_Rookie · Answer

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
ame\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?

jcarlson · Answer

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'
)

jskinner · Answer

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