postgresql from mysql_fdw

[MikeMoran]

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

[Asrujit_SenGupta]

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...‌

[MikeMoran]

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

[Asrujit_SenGupta]

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?

[MikeMoran]

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.

[Asrujit_SenGupta]

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.

[Todd_Metzler]

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.

[MikeMoran]

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

[Asrujit_SenGupta]

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.

[MikeMoran]

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