2

Suppose I have a table defined as follows:

CREATE TABLE gtest (name varchar, geom geometry);

To insert, I am able to simply do this:

INSERT INTO gtest
VALUES (
    'Polygon',
    'SRID=4326;POLYGON((0 0,1 0,1 1,0 1,0 0))'
);

I don't have to wrap the WKT string in the function ST_GeomFromText() because PostGIS has an implicit cast that does so. This is nicely explained by @JGH here

By using the PostgreSQL command \dC, the defined casts can be listed, including:

                                     List of casts
     Source type         |         Target type         |      Function      |   Implicit?
-------------------------+-----------------------------+--------------------+---------------
text                     | geometry                    | geometry           | yes
geometry                 | text                        | text               | yes

I'd like to make it, so I can simply do a SELECT * FROM gtest, and have the results of the geometry column be implicitly converted into WKT. Currently, it will just display them as WKB.

First, I tried creating a new cast as follows:

CREATE CAST (geometry AS text) WITH FUNCTION st_astext(geometry) AS IMPLICIT;

This returned an error, since a cast from geometry to text already exists (as seen in the table).

I then tried ALTER EXTENSION postgis DROP CAST (geometry as text); and then DROP CAST (geometry as text);, and was then able to create the new cast:

                                     List of casts
     Source type         |         Target type         |      Function      |   Implicit?
-------------------------+-----------------------------+--------------------+---------------
geometry                 | text                        | st_astext          | yes

This still didn't work however, as when I do a select, I still get the results in WKB.

Firstly, is this possible? Am I just doing something wrong? Secondly, would any geometry functions break by adding this implicit cast?

  • Does this function work? postgis.net/docs/ST_AsEWKT.html – DPSSpatial Feb 7 at 22:32
  • Can you be a bit more specific as to what the end goal is? I see that you want to run "SELECT * FROM table_name;" and have it display the WKT format, but is that for a later export? or just to view it on the screen? the CREATE CAST function is not for what you have in mind here. your geometry data-type column must stay data-type geometry to perform any GIS with it. – Marshall Feb 8 at 0:07
  • @Marshall Basically I'm trying to retrieve data with a database driver in golang. Ideally, I'd like to just be able to query the column itself, without having to wrap it in a function, and have the result already be formatted in WKT format. – robbieperry22 Feb 8 at 0:22
  • 1
    If you wish to query the column with no conversions, the column will have to be in text format. This means no more geometry functions. The only clean way I see of doing this is creating a new column of type text that stores a string of what was read from st_asText(geom) - basically a duplicate column that only stores strings of text. I'm not sure if this helps your goal, but I use the database GUI DBeaver and it automatically displays WTK with no prompting in its dataview tables. – Marshall Feb 8 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.