topic Re: Python script querying SQL database and displaying in ArcGIS Pro in Python Questions

Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 13:50:27 GMT

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?

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 14:00:00 GMT

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.

Re: Python script querying SQL database and displaying in ArcGIS Pro

BlakeTerhune — Mon, 01 Feb 2021 14:17:41 GMT

As @Anonymous User 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"

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 22:38:06 GMT

Thank you @Anonymous User 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)

Re: Python script querying SQL database and displaying in ArcGIS Pro

BlakeTerhune — Mon, 01 Feb 2021 22:42:37 GMT

What about ArcSDESQLExecute?

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 22:51:28 GMT

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?

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 23:16:01 GMT

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

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Mon, 01 Feb 2021 23:23:19 GMT

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.

Re: Python script querying SQL database and displaying in ArcGIS Pro

Anonymous User — Tue, 02 Feb 2021 13:13:55 GMT

Great, thank you for your help!