PostGIS – How to Convert Bytea to Geometry Column

geometryopenstreetmappostgispostgresqlwell-known-binary

I am trying to create a geometry column from bytea.

I imported OSM data using ogr2ogr using the command

ogr2ogr -f PostgreSQL -nln bahamas_multipolygon PG:"dbname=openstreetmap user=postgres password=xxx" bahamas-latest.osm.pbf multipolygons

The output is when running the select query is fine

select wkb_geometry, osm_id, ogc_fid, "natural"
from public.bahamas_multipolygon as sd
where sd.natural = 'water';

Now I want to convert wkb_geometry to a WKT string representation (or) geometry column.

I tried with ST_GeomFromWKB, ST_GeomFromEWKB and tried explicit casting the column to ::geometry but all resulted in the same error.

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I looked at various similar answers but none of them helped. What is it with bytea that I am missing to help me convert to geometry?

Best Answer

Verify that you have the PostGIS extension installed, or if the containing schema is added to your search_path!

Run

SELECT PostGIS_Full_Version();

to verify the installation - if that function does not exist, the extension is not installed.

Related Question