Select to view content in your preferred language

Connecting to Postgres database

1765
9
06-20-2022 06:23 AM
TheGamer
Regular Contributor

Hi, is there an easy way to connect to a postgres database (hosted through AWS) to ArcGIS Online?

0 Kudos
9 Replies
George_Thompson
Esri Notable Contributor

You have to publish data to ArcGIS Online. There is not a way to connect an "external" DB (as far as I know). The published data is hosted in ArcGIS Online.

If you need this DB to be published, then I would recommend using ArcGIS Enterprise and referencing the services in ArcGIS Online.

--- George T.
TheGamer
Regular Contributor

@George_ThompsonI am making a ModelBuilder model which will use a table from the PostgreSQL and perform some calculations (tabulate intersection, joins, etc).  If data changes in the database, will the changes automatically appear in ArcGIS Pro? If not, how can I make sure changes appear without reconnecting the database

 

0 Kudos
George_Thompson
Esri Notable Contributor

You would need to create and publish a DB view (via ArcGIS Enterprise or ArcGIS Server) if you want the changes to show up.

If you use a model, the data will be updated after the model is run, unless you script it to look for updates.

--- George T.
0 Kudos
TheGamer
Regular Contributor

@George_Thompson can you tell me what libraries I would need to connect/update the database in ArcGIS Pro notebook? Also, I'm no longer use a model builder instead I will be connecting the table from the database with a feature class

0 Kudos
George_Thompson
Esri Notable Contributor

the client libraries for Postgres are included with the Pro client. What version of Pro are you using?

I have not had to install Postgres libraries in a while.....

--- George T.
0 Kudos
TheGamer
Regular Contributor

@George_Thompson  I'm currently using Version 2.9 and for my script, all i want to do is connect to the Postgres database (hosted in AWS server), keep data updated and use the table from the database to join it with feature class. Also, my ArcGIS Pro is through AWS Server as well

0 Kudos
George_Thompson
Esri Notable Contributor

I would that would work. Can you make a connection to the Postgres DB on AWS?

https://pro.arcgis.com/en/pro-app/latest/help/data/databases/connect-postgresql.htm 

Are you getting an error?

If it is not an Enterprise Geodatabase, I do not think that you can edit the data in the table. You can delete / create new spatial (or non-spatial) tables.

--- George T.
0 Kudos
TheGamer
Regular Contributor

@George_ThompsonI don't want to edit the data in the table, I basically just want to use the data from the database and join it to the shapefile. Below is the code, I'm implementing for connecting to the database

import arcpy

path = r"C:\Users\Administrator\Documents\ArcGIS\Projects\Oakville\MappingModel.gdb"
username = ""
password = ""
instance = “<region_id>.rds.amazonaws.com"
arcpy.management.CreateDatabaseConnection(path, "Dataset.sde", "POSTGRESQL", instance,
"DATABASE_AUTH", username, password, "SAVE_USERNAME", {database})

Both the ArcGIS Server and Database are in EC2 instances

0 Kudos
George_Thompson
Esri Notable Contributor

are you trying to save the connection into the file geodatabase?

that is what it looks like with your path variable. Can you move that up one level?

Are you able to create a connection in Pro, not in arcpy?

--- George T.
0 Kudos