Search code examples
postgresqlpostgis

Postgis geometry format manipulation

Hello I have 2 questions about postgis geometry format:

How can I determine what is this geometry format (ewkb, hexewkb ...)?

"0101000020110F0000000000003087094100000080B6415741"

How to convert wkt to EWKB?

Solution

  • 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