Internally, PostGIS stores geometries in a binary specification, but it is queried and viewed outside as a hex-encoded string. There are two popular variations of well-known binary (WKB):
- EWKB (via
ST_AsEWKB
) - an extended WKB specification designed by PostGIS.
- OGC WKB (via
ST_AsBinary
) - specified by the OGC and ISO. For a while it was 2D-only, but later extended to support Z
, M
and ZM
geometries.
The two specifications are the same for 2D geometries, but are different for higher-order geometries with Z
, M
and ZM
coordinates.
Older versions of GDAL/OGR (1.x) only understand the EWKB for 3D geometries, so for these I recommend using ST_AsEWKB
. (But if you only have 2D geometries, either format are fine). For example:
import psycopg2
from osgeo import ogr
ogr.UseExceptions()
conn = psycopg2.connect('dbname=postgis user=postgres')
curs = conn.cursor()
curs.execute("select ST_AsEWKB('POINT Z (1 2 3)'::geometry) AS g")
b = bytes(curs.fetchone()[0])
print(b.encode('hex')) # 0101000080000000000000f03f00000000000000400000000000000840
g = ogr.CreateGeometryFromWkb(b)
print(g.ExportToWkt()) # POINT (1 2 3)
curs.execute("select ST_AsBinary('POINT Z (1 2 3)'::geometry) AS g")
b = bytes(curs.fetchone()[0])
print(b.encode('hex')) # 01e9030000000000000000f03f00000000000000400000000000000840
g = ogr.CreateGeometryFromWkb(b)
# RuntimeError: OGR Error: Unsupported geometry type
Also, note that older GDAL/OGR versions do not support M
coordinates, and these will be parsed but ignored.
With GDAL 2.0 and more recent, ISO WKT/WKB is supported. This means that CreateGeometryFromWkb
can read either WKB flavour (without specifying) and ExportToIsoWkt()
shows output with a modern WKT syntax.
import psycopg2
from osgeo import ogr
ogr.UseExceptions()
conn = psycopg2.connect('dbname=postgis user=postgres')
curs = conn.cursor()
curs.execute("select ST_AsEWKB('POINT Z (1 2 3)'::geometry) AS g")
b = bytes(curs.fetchone()[0])
print(b.encode('hex')) # 0101000080000000000000f03f00000000000000400000000000000840
g = ogr.CreateGeometryFromWkb(b)
print(g.ExportToIsoWkt()) # POINT Z (1 2 3)
curs.execute("select ST_AsBinary('POINT Z (1 2 3)'::geometry) AS g")
b = bytes(curs.fetchone()[0])
print(b.encode('hex')) # 01e9030000000000000000f03f00000000000000400000000000000840
g = ogr.CreateGeometryFromWkb(b)
print(g.ExportToIsoWkt()) # POINT Z (1 2 3)
Additionally, GDAL 2.1 or later will create/export WKT/WKB with M
or ZM
coordinates as expected.
Firstly, you're using ST_AsGeoJSON
and giving it a GeoJSON feature as input. ST_AsGeoJSON
produces GeoJSON geometry from a binary geometry. What you probably want is ST_GeomFromGeoJSON
, which takes a string representing GeoJSON geometry, and produces a binary geometry.
Note that ST_GeomFromGeoJSON
only accepts the geometry part of the GeoJSON feature: the properties are another matter.
SELECT st_geomfromgeojson('{
"type": "Polygon",
"coordinates": [
[
[ -86.917595, 32.664169 ],
[ -86.816574, 32.660117 ],
[ -86.713390, 32.661732 ],
[ -86.714219, 32.705694 ],
[ -86.413116, 32.707386 ],
[ -86.411172, 32.409937 ],
[ -86.444721, 32.399841 ],
[ -86.455616, 32.405807 ],
[ -86.462497, 32.378135 ],
[ -86.492772, 32.361587 ],
[ -86.496774, 32.344437 ],
[ -86.532531, 32.338775 ],
[ -86.542537, 32.363517 ],
[ -86.581873, 32.375019 ],
[ -86.595335, 32.361345 ],
[ -86.614841, 32.374266 ],
[ -86.613453, 32.398584 ],
[ -86.619812, 32.406474 ],
[ -86.653419, 32.397247 ],
[ -86.655597, 32.376147 ],
[ -86.683537, 32.353395 ],
[ -86.711337, 32.360767 ],
[ -86.719028, 32.372059 ],
[ -86.717897, 32.402814 ],
[ -86.727181, 32.404497 ],
[ -86.749981, 32.389105 ],
[ -86.778365, 32.394601 ],
[ -86.780447, 32.368600 ],
[ -86.773163, 32.340728 ],
[ -86.798268, 32.308632 ],
[ -86.816107, 32.309970 ],
[ -86.814912, 32.340803 ],
[ -86.812714, 32.365354 ],
[ -86.840846, 32.396434 ],
[ -86.838731, 32.419437 ],
[ -86.843503, 32.433117 ],
[ -86.857402, 32.438654 ],
[ -86.864856, 32.444280 ],
[ -86.870960, 32.465716 ],
[ -86.881754, 32.480556 ],
[ -86.882694, 32.491883 ],
[ -86.890037, 32.508091 ],
[ -86.901357, 32.528950 ],
[ -86.906106, 32.544756 ],
[ -86.899138, 32.564242 ],
[ -86.903935, 32.587564 ],
[ -86.909512, 32.609588 ],
[ -86.911149, 32.627764 ],
[ -86.917970, 32.645370 ],
[ -86.920236, 32.658868 ],
[ -86.917595, 32.664169 ]
]
]
}');
Best Answer
I found out how to do this by concatenating the query so it gets the values from the database object co_ord_string