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

[MikeMoran]

send me an email and I'll share outside of the forum ..  

mike@thirdrockwater.com

[VinceAngelo]

I'm sorry, but I won't do private consultations in violation of the spirit of open exchange here in GeoNet. I suggest you create similar SQL that exhibits the same behavior, and post that instead. The other alternative, of contracting with Esri for my time, would likely be prohibitively expensive (in dollars and time).

- V

[MikeMoran]

No problem,   I'm just prohibited from sharing in an open forum..  so I understand.

[VinceAngelo]

Most of my projects are under NDA, but I've never had difficulty constructing a representative code sample that captures the spirit of the task without exposing forbidden details.  Strip the CREATE TABLE down to just the geometry and serial columns, manually insert one feature (with whole number coordinate values), then try to access it.  If that fails, you have your representative sample. If it doesn't, you have something to compare against, adding one column with a new datatype at a time until failure.

- V

[MikeMoran]

Literally.. I have 3 tables.. in which one table has both a lat and a long column

create table cdbw (                     create table cdbdc (                             create table cdbdd (

latitude vchar(48),                      company_id  int ai pk,                          worker_id  int ai pk,

longitude vchar(48),                    company_name  varchar (64),             worker_last varchar(64),                            

company_id  int,                          ....                                                         worker_first varchar(64),

worker_id  int,                              )  ;                                                          ....

....                                                                                                              );

) ;

lat/long  are in digital degrees.

select  a.latitude, a.longitude, b.company_name , c.worker_last, c.worker_first .. <remaining 30 fields from a>  from cdbw a

inner join cdbdc b

    on a.company_id = b.company_id

inner join cdbdd c

    on a.worker_id = c.worker_id

where b.company_id is not null and  c.worker_id is not null 

 In both cases,  using mysql_fdw for live updates or static tables in loaded from CSV files,  I can connect to postgresql and

select against the table.  Yet,  ArcMap connects, but doesn't see the tables.

[VinceAngelo]

Latitude and longitude are defined as VARCHAR; if they're degrees they should be NUMERIC.

I'm not sure where you're going with "company_id  int ai pk", since this is non-standard SQL (and fails in PostgreSQL).

The only way this is going to work is if you create real tables in PostgreSQL, and "replicate" from the bridged table simulations to the real tables (once you've done it with tables you can try through materialized views).  Your sample code should include each of the datatypes used in your customer database (ArcGIS won't even look at tables with a 64-bit integer), and you should have a geometry type column (named shape or geom or point), which is defined with the correct SRID (4326), because rendering through virtual geometry columns simulated from text coordinate values will yield evil performance.

- V

[MikeMoran]

Well,  shorthand and interruptions, my apologies   *_id  is an auto_increment primary key.

So from the rest of your comment,  it is not going to work as I don't have any geometry type columns.   

Additionally, when I dump the mysql to csv with 64-bit integers,  ArcGIS seems to read the CSV file just fine and creates all the requisite

data points on the map,  even thought  the lat/long are coming in as varchars.  And no, I didn't design the system, I just got tasked with clean

up they system.

Thanks for the help.    I think I understand my problem, but without a complete redesign there doesn't seem to be any way to make things

work directly in the current environment.

[VinceAngelo]

It may not be as bad as you make it out to be. Obviously, implementing in PostgreSQL from scratch would be better, but if you need a PG instance anyway, using a materialized view that casts the MySQL data into real data tables with PostGIS geometry would be the best option, and should only take a day or two to implement. With that investment of time, you'd get a far more robust implementation than foreign data wrappers would achieve. 

Given the presence of 64-bit integers, you have other issues with Desktop implementation that would need to be addressed as well.

Generally speaking, GIS integration is easiest if it's tackled first, as part of initial design, rather than as an afterthought. I know it wasn't your plan to do it that way, but I have to say I've seen a number of failed projects that were doomed through "deal with that GIS stuff later" design, so any time you have tables with longitude and latitude columns exposed in the bid phase, it would help to make sure you know what sort of GIS integration will be required before submission, just so you aren't burned again.

Good luck.

- V