[postgis-users] Problem extracting SQL Server Geometry (or, what is the 0x character?)

Regina Obe lr at pcorp.us
Fri Oct 14 18:37:51 PDT 2016



Have you tried using ogr_fdw.  Might have better luck with that since it's designed with spatial in mind.





That said I don't have any data in SQL Server with spatial in it so have only been using it for non-spatial SQL Server (and even then mostly with PostgreSQL on windows) and shapefiles, spreadsheets etc.


Hope that helps,



From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Brent Wood
Sent: Friday, October 14, 2016 9:15 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>; ben at ausvet.com.au
Subject: Re: [postgis-users] Problem extracting SQL Server Geometry (or, what is the 0x character?)


Hey Ben...


Long time!


In the absence of a more elegant approach...


You could try the FDW query going via WKT rather than WKB - so deconstruct then reconstruct?


Minor overhead for a one-off transfer, perhaps more of an issue for a working query.


I use both fdw & OGR virtual data source to access data from Specify (MySQL) in QGIS. Both work fine. Different sorts of issues with each. So fdw is not just used for data transfers :-)







From: Ben Madin <ben at ausvet.com.au <mailto:ben at ausvet.com.au> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> > 
Sent: Saturday, October 15, 2016 1:03 PM
Subject: [postgis-users] Problem extracting SQL Server Geometry (or, what is the 0x character?)


G'day all,


I hope a simple case of something I've missed, but we are trying to extract data from a SQL Server database into PostGIS use tds_fdw... the data in SQL Server appears to be in WKB - but when when connect to this field we have a precursor 0x. I can't find any references to anyone else suffering this problem, but that could be because I'm trying a lazy approach to automate retrieval of hundreds of tables using the FDW (that's what it is for, right?)


I'm left with a sense that it is an encoding error between the two systems? I've tried making the fdw column text instead of geometry, but I can't get rid of the 0x, and no amount of trying to cajole the text to any other form makes it any happier. 


To complicate it, for testing I'm going from SQL Server 2014 (running in Windows 8.1 in a VM) to PostgreSQL 9.4 on a Mac (El Capitan) using tds_fdw compiled on the same mac. POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r0" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.7.8" LIBJSON="0.12.1" RASTER


Any ideas gratefully received? 









m : +61 448 887 220


e : ben at ausvet.com.au <mailto:ben at ausvet.com.au> 


10 High Street, Fremantle

Western Australia


on the web: www.ausvet.com.au <http://www.ausvet.com.au/> 



This transmission is for the intended for a mailing list and is clearly never going to be confidential information. If you have received this transmission in error, apologies! The contents of this email are the likely ill-educated opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Thanks for reading. An even bigger thanks for any help you can provide.

postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161014/13341c17/attachment.html>

More information about the postgis-users mailing list