[postgis-users] Help needed with ogr_fdw to access mysql

[Brent Wood]

Hi,
I have a VRT file that allows a non-spatial MySQL database to be accessed directly with QGIS. This works very well.
I'm now trying to use this as the basis to create an FDW in a Postgres db that maps the same data extract as a local table. 

I can't find a set of docs or example that I can follow to get this working, hence this "Please help!!" email.
The XML for the VRT is:
<OGRVRTDataSource>
  <OGRVRTLayer name="collectionObjects_360">
  <SrcDataSource>MYSQL:xxxxx,user=xxxxx,password=xxxxxhost=xxxxx,port=xxxx</SrcDataSource> 
  <SrcSQL>select catalognumber, latitude1 AS y, case when longitude1 < 0 then 360+longitude1 else longitude1 end AS x,
localityName as StationID, startDate, latitude1, longitude1, latitude2, longitude2, 
maxElevation, minElevation, taxon.fullname as TaxonName, prefT.fullname as PreferredName
from collectionobject
INNER JOIN collectingevent ON collectionobject.collectingeventid = collectingevent.collectingeventid
INNER JOIN locality on collectingevent.localityid = locality.localityid
LEFT JOIN determination on collectionobject.collectionobjectid = determination.collectionobjectid
LEFT JOIN taxon on determination.taxonid = taxon.taxonid
LEFT JOIN taxon prefT on determination.preferredtaxonid = prefT.taxonid
WHERE Latitude1 is not null and longitude1 is not null and determination.iscurrent = 1 and catalognumber is not null
ORDER BY Catalognumber</SrcSQL> 
  <GeometryType>wkbPoint</GeometryType>
  <GeometryField encoding="PointFromColumns" x="x" y="y"/> 
  <LayerSRS>EPSG:4326</LayerSRS>
</OGRVRTLayer>
</OGRVRTDataSource>
I need to convert this to the SQL's to create the same query using fdw in a Postgis db. 

I figure I can use the VRT file as the connection directly in ogr_fdw, or create a mysql_fdw SQL to achieve the desired result. I'm happy with either, any advise appreciated.

If necessary I can retrieve just the coords & use a view to create the postgis geometries in the Postgres db, as the source db is non-spatial (just numeric x & y coords (decimal degrees).
I have postgres FDW tables working fine, it is the "create server ..." and "import ..." SQL statements to connect to a mysql db or OGR VRT file that I'm having trouble with.


Thanks,
Brent Wood
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210609/bf2db11e/attachment.html>