Problem extracting SQL Server Geometry (or, what is the 0x character?)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Problem extracting SQL Server Geometry (or, what is the 0x character?)

Ben Madin-2
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? 

cheers

Ben





m : +61 448 887 220


10 High Street, Fremantle
Western Australia

on the web: 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problem extracting SQL Server Geometry (or, what is the 0x character?)

Brent Wood-2
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 :-)

Cheers,

  Brent


From: Ben Madin <[hidden email]>
To: PostGIS Users Discussion <[hidden email]>
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? 

cheers

Ben





m : +61 448 887 220


10 High Street, Fremantle
Western Australia

on the web: 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problem extracting SQL Server Geometry (or, what is the 0x character?)

Regina Obe-2

Ben,

 

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

 

https://github.com/pramsey/pgsql-ogr-fdw

 

 

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,

Regina

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Brent Wood
Sent: Friday, October 14, 2016 9:15 PM
To: PostGIS Users Discussion <[hidden email]>; [hidden email]
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 :-)

 

Cheers,

 

  Brent

 


From: Ben Madin <[hidden email]>
To: PostGIS Users Discussion <[hidden email]>
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? 

 

cheers

 

Ben

 

 

 


 

m : +61 448 887 220

 

 

10 High Street, Fremantle

Western Australia

 

on the web: 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Problem extracting SQL Server Geometry (or, what is the 0x character?)

Peter Devoy
In reply to this post by Ben Madin-2
0x is the prefix used in computing to indicate the following number is represented in base 16 (hexadecimal).  WKB is Well Known Binary, you are seeing the binary (base 2) represented in hexadecimal base (base 16).

E.g. 255 decimal = 11111111 binary = 0xFF [hexadecimal]

Kind regards


Peter

3XE
P: 01326 567155
M: 07770 693662
A: 3XE Ltd
Tremough Innovation Centre
PENRYN
TR10 9TA
3XE Ltd · Registered in England and Wales · 9356871

On 15 October 2016 at 01:03, Ben Madin <[hidden email]> wrote:
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? 

cheers

Ben





m : <a href="tel:%2B61%20448%20887%20220" value="+61448887220" target="_blank">+61 448 887 220


10 High Street, Fremantle
Western Australia

on the web: 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Fw: HDF import to Postgis raster

Brent Wood-2
In reply to this post by Brent Wood-2



Hi postgis users!!

I'm exploring the option of importing HDF grids into Postgis, ideally to serve as WMS via Mapserver.

Never having used PG Raster before, or Mapserver to serve them, I figured I'd ask for advice here first!

The HDF files can be read by GDAL, so I'm assuming an ETL bash script could do the upload easily enough.

I've been serving WMS from Postgis vector data for several years now, so should be able to figure that out. I'd prefer to use WMS than WCS if possible, I'm not sure what the ramifications are.

Any advice, comments, examples gratefully received!


Thanks,

Brent Wood





_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fw: HDF import to Postgis raster

Stephen Woodbridge
Brent,

I've never used postgis raster either. Have you seen these:

https://www.google.com/search?q=postgis+raster+data+load+hdf+grid&ie=utf-8&oe=utf-8

https://postgis.net/docs/RT_FAQ.html#idm27746

Hope this helps,
   -Steve W

On 5/24/2017 7:11 PM, Brent Wood wrote:

>
>
>
> Hi postgis users!!
>
> I'm exploring the option of importing HDF grids into Postgis, ideally to
> serve as WMS via Mapserver.
>
> Never having used PG Raster before, or Mapserver to serve them, I
> figured I'd ask for advice here first!
>
> The HDF files can be read by GDAL, so I'm assuming an ETL bash script
> could do the upload easily enough.
>
> I've been serving WMS from Postgis vector data for several years now, so
> should be able to figure that out. I'd prefer to use WMS than WCS if
> possible, I'm not sure what the ramifications are.
>
> Any advice, comments, examples gratefully received!
>
>
> Thanks,
>
> Brent Wood
>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...