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

[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]

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.

[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]

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.

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

[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]

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]

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]

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

[VinceAngelo]

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