topic Re: postgresql from mysql_fdw in Developers Questions

postgresql from mysql_fdw

MikeMoran — Fri, 13 Oct 2017 17:44:50 GMT

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

Re: postgresql from mysql_fdw

Asrujit_SenGupta — Sat, 14 Oct 2017 04:25:03 GMT

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‌

Re: postgresql from mysql_fdw

MikeMoran — Sat, 14 Oct 2017 04:37:28 GMT

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

Re: postgresql from mysql_fdw

Asrujit_SenGupta — Sat, 14 Oct 2017 11:03:48 GMT

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?

Re: postgresql from mysql_fdw

MikeMoran — Sat, 14 Oct 2017 14:24:49 GMT

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.

Re: postgresql from mysql_fdw

Asrujit_SenGupta — Sun, 15 Oct 2017 10:55:49 GMT

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.

Re: postgresql from mysql_fdw

Todd_Metzler — Sun, 15 Oct 2017 11:51:12 GMT

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.

Re: postgresql from mysql_fdw

MikeMoran — Sun, 15 Oct 2017 13:57:10 GMT

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

Re: postgresql from mysql_fdw

Asrujit_SenGupta — Sun, 15 Oct 2017 15:51:56 GMT

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.

Re: postgresql from mysql_fdw

MikeMoran — Thu, 19 Oct 2017 18:56:44 GMT

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

Re: postgresql from mysql_fdw

VinceAngelo — Thu, 19 Oct 2017 19:58:03 GMT

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

Re: postgresql from mysql_fdw

MikeMoran — Thu, 19 Oct 2017 20:05:03 GMT

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.

Re: postgresql from mysql_fdw

Todd_Metzler — Fri, 20 Oct 2017 14:51:38 GMT

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.

Re: postgresql from mysql_fdw

MikeMoran — Fri, 20 Oct 2017 20:23:29 GMT

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.

Re: postgresql from mysql_fdw

MikeMoran — Mon, 23 Oct 2017 13:21:13 GMT

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.

Re: postgresql from mysql_fdw

VinceAngelo — Mon, 23 Oct 2017 16:06:45 GMT

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

Re: postgresql from mysql_fdw

MikeMoran — Mon, 23 Oct 2017 16:22:40 GMT

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 ?

Re: postgresql from mysql_fdw

VinceAngelo — Mon, 23 Oct 2017 16:38:33 GMT

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

Re: postgresql from mysql_fdw

MikeMoran — Mon, 23 Oct 2017 17:04:00 GMT

I've tried it 2 different ways.. Initially, I used the mysql_fdw to make the data available to postgresql from mysql .. and later, I just did an sql query to create a csv file

to import into postgresql. In both cases, the arcmap use owned the tables and usage/select on all three tables.

In both cases, ArcGIS Desktop did not see any tables in postgresql. Now, the quirk in my environment might be that there are NO enterprise servers anywhere, this is strictly an ArcMap only environment

Re: postgresql from mysql_fdw

VinceAngelo — Mon, 23 Oct 2017 17:15:27 GMT

Now, the quirk in my environment might be that there are NO enterprise servers anywhere, this is strictly an ArcMap only environment

No, that's not it. I've used dozens of different "non-gdb" PG databases on different servers without difficulty.

You'd need to share your table creation SQL. You could be violating table naming rules, or column naming rules, or user naming rules, or any of other "plain vanilla SQL" restrictions (no spaces, no upper case, no leading numeric characters,... -- basically, if it requires a double-quote, it's not going to work), but it's not possible to tell without your code.

- V