<html><head></head><body><div class="ydp8e56726cyahoo-style-wrap" style="font-family: verdana, helvetica, sans-serif; font-size: 16px;"><div></div></div><div dir="ltr" data-setdir="false">Hi,</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I have a VRT file that allows a non-spatial MySQL database to be accessed directly with QGIS. This works very well.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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. <br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I can't find a set of docs or example that I can follow to get this working, hence this "Please help!!" email.</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">The XML for the VRT is:</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"><div><OGRVRTDataSource><br> <OGRVRTLayer name="collectionObjects_360"><br> <SrcDataSource>MYSQL:xxxxx,user=xxxxx,password=xxxxxhost=xxxxx,port=xxxx</SrcDataSource> <br> <SrcSQL>select catalognumber, latitude1 AS y, case when longitude1 < 0 then 360+longitude1 else longitude1 end AS x,<br>localityName as StationID, startDate, latitude1, longitude1, latitude2, longitude2, <br>maxElevation, minElevation, taxon.fullname as TaxonName, prefT.fullname as PreferredName<br>from collectionobject<br>INNER JOIN collectingevent ON collectionobject.collectingeventid = collectingevent.collectingeventid<br>INNER JOIN locality on collectingevent.localityid = locality.localityid<br>LEFT JOIN determination on collectionobject.collectionobjectid = determination.collectionobjectid<br>LEFT JOIN taxon on determination.taxonid = taxon.taxonid<br>LEFT JOIN taxon prefT on determination.preferredtaxonid = prefT.taxonid<br>WHERE Latitude1 is not null and longitude1 is not null and determination.iscurrent = 1 and catalognumber is not null<br>ORDER BY Catalognumber</SrcSQL> <br> <GeometryType>wkbPoint</GeometryType><br> <GeometryField encoding="PointFromColumns" x="x" y="y"/> <br> <LayerSRS>EPSG:4326</LayerSRS><br></OGRVRTLayer><br><div></OGRVRTDataSource></div><div><br></div><div dir="ltr" data-setdir="false">I need to convert this to the SQL's to create the same query using fdw in a Postgis db. <br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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.<br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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).</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">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.<br></div></div><div><br></div></div><div><br></div><div dir="ltr" data-setdir="false">Thanks,</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Brent Wood<br></div></body></html>