nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

VinceAngelo

I have actual tables in a PostgreSQL database using PostGIS, not a wrapper, emulating that function. I also conform to ownership requirements (ArcGIS doesn't permit tables to be owned by groups), and GRANT my connection user the minimum access required (SELECT, in my case), with a different user and schema for table creation.

The details of your replication implementation need to be reviewed, since there's a dozen different possible ways to achieve "replication", and not all of them will behave the same with an ArcGIS client.

- V

MikeMoran

That's essentially what I was doing. I could see all the data needed in standard PostgreSQL tables and query the tables from the command line as the ArcMap user, but when the ArcMap client connected to PostgreSQL, it didn't see the tables and the message in the error log said "Can't connect to geodatabase".

So how are you getting it to work ?

VinceAngelo

So replicating 3 tables from MySQL into PostgreSQL to display via ArcMap will never work.

No, that's not correct. Since the 10.4.0 release, PostgreSQL is special in that you can access PostGIS tables read/write without an enterprise geodatabase license. I do this all the time at client sites. Sure, you don't get full geodatabase functionality, but you can create feature classes in a PostgreSQL database via Desktop (Standard or higher) or Python (ArcPy), and you can insert to tables created via SQL with ArcPy cursors. The key is having a simple database design and utilizing the serial datatype to allocate rowid columns, to preserve 32-bit integer identifiers in the tables for Query Layer access.

- V

MikeMoran

Based on an answer to another question I asked in a different group, what I'm trying to do is impossible.

In order for ArcGIS Desktop (aka ArcMap) to connect to PostgreSQL, I need to create the geospatial database in PostgreSQL, which has to be via ArcGIS Desktop. The kicker is, for it to work you need at least 1 Enterprise level version of ArcGIS. I don't have one and neither does my customer.

So replicating 3 tables from MySQL into PostgreSQL to display via ArcMap will never work.

MikeMoran

Any pointers to documents talking about such a division? At this point, the data consists of a lat/long pair, plus details on the installation at that location.

Todd_Metzler

Good luck Mike. May I suggest as a factor in your re-engineering your team considers that any object that contains place in any form should live in a spatial data set. In our enterprise, I am continually working with business stakeholders that have, in Ignorance through no fault of their own, "buried" spatial data in business stores. I spend a fair amount of time educating these business users about the power of place and time, harvesting those data and getting it where it belongs: In spatial space.

MikeMoran

At this point, just trying to get a sign off on the last item on the statement of work. Once that is done and we get paid, it will be time to re-engineer the entire solution. Already have a team working on an estimate to see how much code will have to be changed to support PostgreSQL instead of MySQL. Part of the problem is we really don't have any PostgreSQL experts on the team.

VinceAngelo

Mike -

I'm afraid you may be courting sorrow and woe.

Let's say you've ordered a minivan to tow a small "teardrop" camper, then at the last minute, your spouse mentions that an 8-ton "fifth wheel" camper was ordered instead. Even if you can locate and install a fifth-wheel tow-hitch trailer, and paint a picture of a monster truck on the window of the camper, it's unlikely you're going to be able to visit Rocky Mountain National Park.

Right now you're still trying to get the wiring on the fifth-wheel cart correct, but I worry that you may not get this contraption street-legal. If you do get the wiring in place, and if you're only headed to a state park 5 miles away, maybe this will work for a while, but I don't think you'd be able to get warranty service on the minivan engine or transmission (or a refund on the camper).

You may need to start an "expectation management" campaign, because the performance characteristics and reliability of your solution may not meet requirements. Even if you store the geometry in PostgreSQL and link to the foreign attributes through views, you're likely to have a temperamental and sluggish application.

- V

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

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

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

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

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

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

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

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-2cef07552a9a&searchIndex=0‌ https://community.esri.com/community/gis/managing-data?sr=search&searchId=b00ba705-f7c7-4ff7-a915-742e61cfc58b&searchIndex=0‌

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.