Querying Netezza via SquirrelSQL returns WKT geometry in unknown encoding

Question

I am using SquirrelSQL to write and execute SQL queries on a Netezza database. Using Netezza's spatial capabilities (which are essentially the same as those of PostGIS) I've executed a query and returned a single result that contains a geometry. Here's the query, for reference:

SELECT t.SHAPE
FROM (SELECT * FROM OS_AB_PLUS..E12_ADDRESSBASE WHERE POSTCODE = 'RH1 6NE' 
AND PAO_START_NUMBER = '14') as a, OS_TOPO..TOPOGRAPHICAREA as t
WHERE inza..ST_Within(a.shape, t.shape) = TRUE

My issue is that the geometry field, which should contain the polygon coordinates represented as Well-Known Text (WKT), looks instead like this:

g¹ AË Affff¬0AÍÌÌÌî0AÒ 3333Ê AÍÌÌÌî0A» Aë0Afffæ» AffffÒ0A¹ AÒ0A333³¹ A3333¿0AŒ AffffÀ0AÍÌÌLŒ Affff¬0AË A¯0AëQ8Ê A3333í0A3333Ê AÍÌÌÌî0A

I can't seem to find anywhere in SquirrelSQL to specify the encoding of VARCHAR columns, and I've seen the column returned without encoding issues in Aginity (another SQL client). Any suggestions on how to proceed would be much appreciated.

score 3 · Accepted Answer · answered Mar 12 '13 at 09:58

Turns out my issue was not really related to encoding at all. The human-readable version of the geometry in a PostGIS-like database will only be returned when ST_AsText is used in the select statement. So my SQL query becomes:

SELECT inza..ST_AsText(t.SHAPE)
FROM (SELECT * FROM OS_AB_PLUS..E12_ADDRESSBASE WHERE POSTCODE = 'RH1 6NE' 
AND PAO_START_NUMBER = '14') as a, OS_TOPO..TOPOGRAPHICAREA as t
WHERE inza..ST_Within(a.shape, t.shape) = TRUE

Which returns. as intended:

POLYGON ((526696.15 148931.9, 526703.94 148932.34, 526703.8 148935.2, 526705.5 148935.3, 526705.4 148937.8, 526695.9 148937.35, 526696.15 148931.9))

Querying Netezza via SquirrelSQL returns WKT geometry in unknown encoding

1 Answers1