convert Postgres geometry format to WKT

by

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

More Related Contents:

  1. Getting all Buildings in range of 5 miles from specified coordinates
  2. Refactor a PL/pgSQL function to return the output of various SELECT queries
  3. Any downsides of using data type “text” for storing strings?
  4. Query for array elements inside JSON type
  5. Calculating Cumulative Sum in PostgreSQL
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Is it possible to make a recursive SQL query?
  8. Change PostgreSQL columns used in views
  9. Way to try multiple SELECTs till a result is available?
  10. How to set auto increment primary key in PostgreSQL?
  11. List columns with indexes in PostgreSQL
  12. PostgreSQL IF statement
  13. Split given string and prepare case statement
  14. Column ‘mary’ does not exist
  15. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  16. Get top row(s) with highest value, with ties
  17. Delete parent if it’s not referenced by any other child
  18. PostgreSQL wildcard LIKE for any of a list of words
  19. Query that ignore the spaces
  20. I keep getting the error “relation [TABLE] does not exist”
  21. SQL query to get most recent row for each instance of a given key
  22. Postgres function returning table not returning data in columns
  23. Which datatype should be used for currency?
  24. COPY with dynamic file name
  25. Table or column name cannot start with numeric?
  26. PostgreSQL column ‘foo’ does not exist
  27. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment