<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="post-text" itemprop="text">
<p>Hi all, <br>
</p>
<p>Does anyone have any thoughts to the problem below?<br>
</p>
<p>In MSSQL Server, casting geometry to varbinary(max) produces a
string that looks like this:</p>
<pre style="" class="lang-sql prettyprint prettyprinted"><code><span class="lit">0x3D0B0000010CA0F3BE3B5CE9474100EC414E3AF93941</span></code></pre>
<p>When you insert that string into a geometry column, a geometry
object is built.</p>
<p>However, I can't seem to find out what the equivalent data
format is in PostGIS / PostgreSQL in order to bring that same
varbinary format out of a PostGIS geometry column (for the
purpose of loading it into an MSSQL Server Geometry object).</p>
<p>I've read that the 'bytea' format does the same as varbinary,
but the bytea version of a PostGIS geometry object looks like
this:</p>
<pre style="" class="lang-sql prettyprint prettyprinted"><code><span class="pun">\</span><span class="lit">001</span><span class="pun">\</span><span class="lit">001</span><span class="pun">\</span><span class="lit">000</span><span class="pun">\</span><span class="lit">000</span><span class="pln"> </span><span class="pun">\</span><span class="lit">346</span><span class="pun">\</span><span class="lit">020</span><span class="pun">\</span><span class="lit">000</span><span class="pun">\</span><span class="lit">000</span><span class="pun">\</span><span class="lit">263</span><span class="pun">\</span><span class="lit">234</span><span class="pun">\</span><span class="lit">021</span><span class="pln">o</span><span class="pun">\</span><span class="lit">224</span><span class="pun">?</span><span class="pln">Z</span><span class="pun">\</span><span class="lit">300</span><span class="pun">-\</span><span class="lit">265</span><span class="pun">\</span><span class="lit">373</span><span class="
pun">^\</span><span class="lit">322</span><span class="pun">\</span><span class="lit">334</span><span class="pln">C</span><span class="pun">@</span></code></pre>
<p>Is there a way to get the same string that the MSSQL varbinary
format produces from PostGIS / PostgreSQL?</p>
<p>Some background into this: I'm developing my own ETL tools
using Python...</p>
<p><strong>psycopg2</strong> works well with PostgreSQL (PostGIS)
database conversion - and PostgreSQL casts geometry to varchar
in a SQL statement in my python script, which inserts nicely
back into a PostGIS geometry column - PostGIS ETL done!</p>
<p><strong>pypyodbc</strong> is working great for the receiving
end of a psycopg2 query, but as the problem above states, I
can't find the correct format to cast the PostGIS geometry to in
the SQL in the python script... otherwise, I can read other
columns from PostgreSQL and write them to MSSQL just fine.<br>
</p>
<p>I've posted this to GIS.SE too... (hope that's ok...)<br>
</p>
<p><a class="moz-txt-link-freetext" href="http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql">http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql</a><br>
</p>
<p>Thanks in advance for any thoughts or suggestions!<br>
</p>
<p>-Matt Baker<br>
Denver Public Schools<br>
Denver, CO<br>
<a class="moz-txt-link-abbreviated" href="mailto:mattbaker@gmail.com">mattbaker@gmail.com</a><br>
</p>
<p><br>
</p>
<p><br>
</p>
</div>
</body>
</html>