Python script querying SQL database and displaying in ArcGIS Pro

1097
8
Jump to solution
02-01-2021 05:50 AM
MargaretLawrimore
New Contributor

I have python code that is querying an SQL database and want to visualize this data using ArcGIS Pro. The data in the database has latitude and longitude information, I want to query the data in the database then display the points and lines between connected point. What would be the best way to do this? 

0 Kudos
1 Solution

Accepted Solutions
JeffK
by MVP Regular Contributor
MVP Regular Contributor

If you haven't already, you may need to install the sql server ODBC driver. You can get them from this link, under the Install the ODBC drivers for SQl Server.   connect-sqlserver and the follow the rest of the steps to create the connection.  If you get stuck on any of them, let us know and we can try to help. 

Once you have the connection made, you can reference it in the code like you would other database connections. 

sqlCon = r"Database Connections\yoursqlconnectionfilename"

If you are using Pro, its the full path to where your connection file is in your project.

View solution in original post

8 Replies
JeffK
by MVP Regular Contributor
MVP Regular Contributor

This can be done, but there are a few questions about the underlying data-  Creating points can be done fairly easy, but connecting the points may be a little tricky.  How can we tell what points should be connected?  Is there anything in the data that can be filtered on to say these should be one feature and these should be another?  Are they polyogons, or polylines?  If you can, copy in your code so we can see what you have and can offer better suggestions.

0 Kudos
BlakeTerhune
MVP Regular Contributor

As @JeffK mentioned, you need the table attributes to support this operation. Check out XY To Line and notice the id_field parameter. This tool only creates single line segments so you'll need to use dissolve to join longer segments together based on the id_field or some other field that you can include using attributes="ATTRIBUTES"

MargaretLawrimore
New Contributor

Thank you @JeffK and @BlakeTerhune. To tell if the points are connected there is a table that can be queried with categories for the ID's and lat/long of the points to-from to form the line (not polygon or polyline). I understand how to display the points and lines, but am unsure of how to query the SQL database from within ArcGIS. (i.e how to call a python script through Pro/ how to connect to the SQL database in arcpy)

0 Kudos
BlakeTerhune
MVP Regular Contributor

What about ArcSDESQLExecute?

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor
There are several ways, like creating a connection file through the database connections in arc and referencing the connection file in the script, or creating the connection string in Python and using a cursor to access the info. Which method do you prefer?
0 Kudos
MargaretLawrimore
New Contributor

@JeffK, I think a connection file would be the best.

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

If you haven't already, you may need to install the sql server ODBC driver. You can get them from this link, under the Install the ODBC drivers for SQl Server.   connect-sqlserver and the follow the rest of the steps to create the connection.  If you get stuck on any of them, let us know and we can try to help. 

Once you have the connection made, you can reference it in the code like you would other database connections. 

sqlCon = r"Database Connections\yoursqlconnectionfilename"

If you are using Pro, its the full path to where your connection file is in your project.

MargaretLawrimore
New Contributor

Great, thank you for your help!

0 Kudos