Convert Postgres Geometry Format to Wkt

convert Postgres geometry format to WKT

Have you tried this?

SELECT ST_AsText(your_geom_column) FROM your_table

In the following examples I'll show you a few ways to serialise your geometries. Here is sample data with two points encoded as 4326 (WGS84):

CREATE TEMPORARY TABLE tmp 
  (geom GEOMETRY);
INSERT INTO tmp VALUES 
  ('SRID=4326;POINT(1 2)'),
  ('SRID=4326;POINT(2 4)');

Geometries as WKB (Well-Known Binary, default):

SELECT geom FROM tmp;

 geom                        
----------------------------------------------------
 0101000020E6100000000000000000F03F0000000000000040
 0101000020E610000000000000000000400000000000001040

Geometries as WKT (Well-Known Text) and EWKT (WKT with an explicit Spatial Reference System):

SELECT ST_AsText(geom), ST_AsEWKT(geom) FROM tmp;

 st_astext  | st_asewkt       
------------+----------------------
 POINT(1 2) | SRID=4326;POINT(1 2)
 POINT(2 4) | SRID=4326;POINT(2 4)

In case you fancy GeoJSON

SELECT ST_AsGeoJSON(geom) FROM tmp;

 st_asgeojson             
--------------------------------------
 {"type":"Point","coordinates":[1,2]}
 {"type":"Point","coordinates":[2,4]}

Or even GML

SELECT ST_AsGML(geom) FROM tmp;

 st_asgml                                      
-----------------------------------------------------------------------------------
 <gml:Point srsName="EPSG:4326"><gml:coordinates>1,2</gml:coordinates></gml:Point>
 <gml:Point srsName="EPSG:4326"><gml:coordinates>2,4</gml:coordinates></gml:Point>

The Google Earth enthusiasts also have their fun! Geometries as KML

SELECT ST_AsKML(geom) FROM tmp;

 st_askml                    
-----------------------------------------------
 <Point><coordinates>1,2</coordinates></Point>
 <Point><coordinates>2,4</coordinates></Point>

And the list goes on! In the PostGIS documentation there are other fancy ways to serialise geometry.

Demo: db<>fiddle

Implicitly convert Geometry to WKT string in PostGIS

When a datum is converted to a string or sent to the client in text mode, the type output function is called. No casts are applied in that case.

This function is written in C, and you'd have to hack PostGIS in order to change it. Moreover, you'd also have to change the type input function to accept the text format.

I hope you did that experiment in a test machine, because that ALTER EXTENSION has mutilated the PostGIS extension.

Postgis geometry format manipulation

To check if a geometry is valid (regardless of format) you can use ST_IsValid - or ST_IsValidReason in case you wanna know why it fails.

SELECT ST_IsValid('0101000020110F0000000000003087094100000080B6415741');
 st_isvalid 
------------
 t
(1 Zeile)

If for whatever reason you want to test if the geometry is encoded in a certain format, try the ST_GeomFrom* functions and catch the error message in case it fails, e.g.

SELECT ST_GeomFromWKB('POINT(1 2)');
FEHLER:  Invalid endian flag value encountered.

SELECT ST_GeomFromText('POINT(1 2)');
              st_geomfromtext               
--------------------------------------------
 0101000000000000000000F03F0000000000000040
(1 Zeile)

To convert it to EWKB try ST_AsEWKB

SELECT ST_AsEWKB('0101000020110F0000000000003087094100000080B6415741');
                      st_asewkb                       
------------------------------------------------------
 \x0101000020110f0000000000003087094100000080b6415741
(1 Zeile)

Further reading: Convert geometry to various formats

Convert .geojson to .wkt | extract 'coordinates'

To convert a series of geometries in GeoJSON files to WKT, the shape() function can convert the GeoJSON geometry to a shapely object which then can be formatted as WKT and/or projected to a different coordinate reference system.

If want to access the coordinates of polygon once it's in a shapely object, use
x,y = geo.exterior.xy.

If just want to convert a series of GeoJSON files into one .wkt file per GeoJSON file then try this:

from pathlib import Path
import json
from shapely.geometry import shape

ROOT = Path('path')

for f in ROOT.glob('*.geojson'):
    with open(f) as fin, open(f.with_suffix(".wkt"), "w") as fout:
        features = json.load(fin)["features"]
        for feature in features:
            geo = shape(feature["geometry"])
            # format geometry coordinates as WKT
            wkt = geo.wkt
            print(wkt)
            fout.write(wkt + "\n")

This output uses your example my.geojson file.

Output:

POLYGON ((19372 2373, 19322 2423, ...
POLYGON ((28108 25855, 27755 26057, ...

If need to convert the coordinates to EPSG:4327 (WGS-84) (e.g. 23.314208, 37.768469), you can use pyproj.

Full code to convert collection of GeoJSON files to a new GeoJSON file in WGS-84.

from pathlib import Path
import json
import geojson
from shapely.geometry import shape, Point
from shapely.ops import transform
from pyproj import Transformer

ROOT = Path('wkt/')
features = []

# assume we're converting from 3857 to 4327
# and center point is at lon=23, lat=37

c = Point(23.676757000000002, 37.9914205)

local_azimuthal_projection = f"+proj=aeqd +R=6371000 +units=m +lat_0={c.y} +lon_0={c.x}"
aeqd_to_wgs84 = Transformer.from_proj(local_azimuthal_projection,
                                      '+proj=longlat +datum=WGS84 +no_defs')

for f in ROOT.glob('*.geojson'):
    with open(f) as fin:
        features = json.load(fin)["features"]
        for feature in features:
            geo = shape(feature["geometry"])
            poly_wgs84 = transform(aeqd_to_wgs84.transform, geo)
            features.append(geojson.Feature(geometry=poly_wgs84))

# Output new GeoJSON file
with open("out.geojson", "w") as fp:
    fc = geojson.FeatureCollection(features)
    fp.write(geojson.dumps(fc))

Assuming the conversion is from EPSG:3857 to EPSG:4327 and center point is at lon=23, lat=37, the output GeoJSON file will look like this:

{"features": [{"type": "Polygon", "geometry": {"coordinates": [[[23.897879, 38.012554], ...

Conversion of wkt to wkb in postgresql

WKT and WKT are two formats that represent Geometry (or Geography) type.

To convert between various formats, you need to construct Geometry type and then export it to desired format. In this case ST_AsBinary(ST_GeomFromText(wkt_text)) should generally work.

If you want to produce WKB directly from lat/lng - something like ST_AsBinary(ST_MakePoint(lng, lat)) should work too. Pay attention to argument order, SQL functions use lng / lat order.

Inserting and selecting PostGIS Geometry with Gorm

Another solution, which I ended up using was with go-geos, as I discovered I needed to use the GEOS C library. With that, I am able to convert the struct into WKT for inserting (as postgis accepts it as regular text) and convert from WKB when scanning.

type Geometry4326 *geos.Geometry

// Value converts the given Geometry4326 struct into WKT such that it can be stored in a 
// database. Implements Valuer interface for use with database operations.
func (g Geometry4326) Value() (driver.Value, error) {

    str, err := g.ToWKT()
    if err != nil {
        return nil, err
    }

    return "SRID=4326;" + str, nil
}

// Scan converts the hexadecimal representation of geometry into the given Geometry4326 
// struct. Implements Scanner interface for use with database operations.
func (g *Geometry4326) Scan(value interface{}) error {

    bytes, ok := value.([]byte)
    if !ok {
        return errors.New("cannot convert database value to geometry")
    }

    str := string(bytes)

    geom, err := geos.FromHex(str)
    if err != nil {
        return errors.Wrap(err, "cannot get geometry from hex")
    }

    geometry := Geometry4326(geom)
    *g = geometry

    return nil
}

This solution might not be ideal for everyone as not everyone needs to use the GEOS C library, which can be a pain to get working on windows. I'm sure though that the same thing can be accomplished using different libraries.

I additionally implemented UnmarshalJSON() and MarshalJSON() on the struct so that it can automatically Marshal/Unmarshal GeoJSON, and then save/get from the database seamlessly. I accomplished this using geojson-go to convert GeoJSON to/from a struct, and then geojson-geos-go to convert said struct into the go-geos struct I was using. A little convoluted, yes, but it works.

How to send PostGiS Geometry and Point data types through Spring Boot Rest API

You need to register a Jackson Module that provides appropriate serializers and deserializers for JTS Geometry objects. A good candidate is jackson-datatype-jts.

If you want an example on how to do this, you could check out this blog post. (I wrote it using an alternative geometry library, but the approach should work with JTS and the jackson-datatype-jts module).

---

Related Topics