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
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
