I have a situation where the application was developed using MySQL and 90% complete, before the customer told us
that they need to be able to generate maps in ArcMap.
I have the data available in PostgreSQL using mysql_fdw. The data is in its own db instance and table. I can connect
to PostgreSQL from the command line and successfully select against the table.
From ArcMap, I can create a database connection ( connect appears in the catalog with no error messages), but no tables appear in ArcMap.
Anyone else using a similar setup or have any clues about what step I missed in setting things up ?
Mike
Start by checking whether you have granted the necessary permissions to the connecting user:
Privileges for geodatabases in PostgreSQL—Help | ArcGIS Desktop
Privileges for using ArcGIS with a PostgreSQL database—Help | ArcGIS Desktop
https://community.esri.com/groups/geodatabase?sr=search&searchId=9bf9d201-8eae-4fc9-a94d-2cef07552a9... https://community.esri.com/community/gis/managing-data?sr=search&searchId=b00ba705-f7c7-4ff7-a915-74...
The docs for ArcMap (ArcGIS Desktop) say it needs CONNECT, USAGE and SELECT which I did.
Basing this on the second link, as the data is a complex set of data, but not geodata.. just lat/long fieldsl
That should be sufficient.
What happens if you create a new Table using PgAdmin OR ArcCatalog? Does that show up in ArcCatalog OR let you create?
pgadmin connects successfully, but see no tables as well. Although, I suspect that pgadmin might not be properly installed as the SQL pane is
inactive.
I feel you should look into the pgAdmin first and make sure that the data shows up correctly there. I am pretty sure, that if the data shows up in pgAdmin, it should show up in ArcGIS Desktop as well.
Note: I am not that familiar with mysql_fdw, so can't recommend\suggest anything related to that.
Hello,
The original mySQL hosted data is now re-hosted in PostgreSQL database. Is this correct? If true then did you set up the PostgreSQL database and enable Enterprise GeoDataBase (EGDB) storage following the steps detailed here: Geodatabases in PostgreSQL—Help | ArcGIS Desktop ? I've normally found it more GIS "friendly" to import business data into a spatial EGDB than the other way around. Your business users won't see any difference in their use of the database, but your GIS users will.
Not quite. The customer application functions against MySQL and handles everything but mapping. At this point, I'm attempting to use a foreign data wrapper (mysql_fdw) to allow Postgresql to access the data from MySQL. From the
command line using psql, I can query all the data via the user that ArcMap is connecting with. ArcMap doesn't see any
available tables (maybe ArcMap only looks for geodatabase tables? ), but, at this point, it is looking more and more like
I don't have Postgresql setup properly as pgadm doesn't see the tables either.
The real noobie question is do I really have a geospatial database. Three tables all interrelated, one of which has a lat/long pair. Interestingly enough, ArcMap is able to digest a CVS file dumped from a view of the three tables.
Mike
Can you test by creating a View in the PostgreSQL database, which pulls data from any one of the tables in the MySQL db.
Then check in ArcCatalog.
Ok.. looks like I've messed up on the roles and grants. ArcMap connects (says it is connected) yet doesn't see the postgres native tables I've created. So at this, point
ArcMap is not able to see either FDW tables nor local tables. Both can be seen when local to the database. Implication being I've messed up with the postgres roles and grants