Does PostGIS automatically convert upon inserting WKT?

Question

I'm quite new to PostGIS so bear with me.

Suppose I have a table defined as follows:

CREATE TABLE gtest (name varchar, geom geometry);

At first, to insert, I was doing something like:

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

I then discovered that it still works by only doing this:

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

When I do a query without converting the geom values back into WKT, they are both encoded properly. Same if I convert the column to EWKT, everything displays properly.

Is there a conversion going on behind the scenes? And if I insert without calling ST_GeomFromText(), will all other functions using the column work fine?

Thanks

JGH · Accepted Answer · 2019-02-07T18:20:10.810

3

There are several automatic cast to and from the geometry type.

You can type \dC in PostgreSQL and you will see all available casts, including:

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

This information is also available in the doc or in this tutorial.

Since the cast in implicit, it means you don't have to specify it to use it. Note that you can "force" it using ::geometry:

select st_asText('SRID=4326;POLYGON((0 0,1 0,1 1,0 1,0 0))'::geometry);
           st_astext
--------------------------------
 POLYGON((0 0,1 0,1 1,0 1,0 0))
(1 row)

Regarding the column usability, the column is of type geometry so anything that is in this column is a geometry and can be used by any function requiring a geometry. How the data got there (automatic cast, conversion, extraction from another geometry etc) is not relevant anymore.

edited Feb 07 '19 at 18:20

answered Feb 07 '19 at 14:02

JGH

15,928
4
31
48

Thanks for the good answer. Do you know if it's possible to forse the cast in the other direction? So by doing `SELECT geom FROM gtest;` the results will automatically be in the text form `POLYGON((0 0,1 0,1 1,0 1,0 0))`? – robbieperry22 Feb 07 '19 at 18:48
@robbieperry22 no, it would return the WKB as text. You can however have a view that contains a column `st_asText(geom)` – JGH Feb 07 '19 at 18:56
I was wondering however if I could set an implicit cast when converting from geometry. I tried doing `CREATE CAST (geometry AS text) WITH FUNCTION st_astext(geometry) AS IMPLICIT;` but it wouldn't allow it, as there already exists a cast like that, but instead using the `text` function. I can't drop it either because postgis requires it. Any workarounds you can think of that don't involve having a view? – robbieperry22 Feb 07 '19 at 19:18
most of the time one can just use the geometry, so for the few times a printout is required, I would personally use `st_asText(geom)` and wouldn't bother with implicit casts – JGH Feb 07 '19 at 19:24

Does PostGIS automatically convert upon inserting WKT?

1 Answers1

Linked