how to use `ST_AsMVTGeom` and `ST_AsMVT`

Question

As shown in the below table grid_cell_data, I have geometryOfCellRepresentativeToTreatment and geometryOfCellRepresentativeToBuffer columns, and they contain geometries.

I want to apply both of ST_AsMVTGeom and ST_AsMVT as stated in the PostGIS documentation, ST_AsMVT takes a geometry column. but when I execute the following code:

SELECT ST_AsMVT(grid_cell_data.geometryOfCellRepresentativeToTreatment) 
AS geom
FROM grid_cell_data

I get the following error:

 pgis_asmvt_transfn: parameter row cannot be other than a rowtype

please let me know how to use both of functions ST_AsMVTGeom and ST_AsMVT

image: enter image description here

You mean this `SELECT ST_AsMVT(g) FROM ( SELECT geometryOfCellRepresentativeToTreatment AS geom FROM grid_cell_data) g`? — Jim Jones, Nov 12 '21 at 08:20
i tested the code you have just sent, i receive the error:parse_column_keys: no geometry column found — Amrmsmb, Nov 12 '21 at 08:23
What is the data type of `geometryOfCellRepresentativeToTreatment`? It has to be a geometry — Jim Jones, Nov 12 '21 at 08:24
@JimJones, oh, i checked it in dbeaver, and it is text?!i will alter it — Amrmsmb, Nov 12 '21 at 08:26
It must work, see this fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dbe014a7baec15bbd6372ee674f2db63 — Jim Jones, Nov 12 '21 at 08:28
@JimJones yes it works now. but i have to questions please.1-i would like to set initial value to geometry column, in python, when i set it to None or "", i get an error. then i set it to `0101000020E6100000000000000000F03F0000000000000040` and it worked. but this value is not the correct value to initialize a geometry.how the geometry column can be initialized please.2-do you know how to use the result of ST_AsMVTGeomandST_AsMVT in openlayers? — Amrmsmb, Nov 12 '21 at 08:39
you mean you want to put a default value in your column, so that every time that a record is inserted and the geom column does not contain any value it becomes `0101000020E6100000000000000000F03F0000000000000040` ? — Jim Jones, Nov 12 '21 at 08:56
@JimJones yes, in other words, a value similar to null when the column contain no values"empty" — Amrmsmb, Nov 12 '21 at 08:59
normally you should post another question for this, but I'll grant you this one ;) https://dbfiddle.uk/?rdbms=postgres_12&fiddle=70e801989db9d4de2b0fa99e53f09f74 — Jim Jones, Nov 12 '21 at 09:04
@JimJones i will post a question for it, and explain it more clearer.but i ment, to set a default value to geom indicates that it is null or empty.for example, when a string initially is initialized to null or "" to indicate being void/empty. what is the equivalent to null/empty for geometries. is it clear — Amrmsmb, Nov 12 '21 at 09:11
it is here:https://stackoverflow.com/questions/69940446/what-is-the-suitable-value-to-initialize-an-empty-column-of-type-geometry — Amrmsmb, Nov 12 '21 at 09:14
This other answer of mine (a bit old) on ST_AsMVT might interest you as well: https://stackoverflow.com/a/60016291/2275388 — Jim Jones, Nov 12 '21 at 09:45

score 0 · Answer 1 · answered Nov 12 '21 at 08:49

It is an aggregate function like count or sum, and the first argument must be a composite type. (I wonder why PostGIS didn't declare the parameter as record.)

So you are expected call it like this, using a “whole-row reference”:

SELECT ST_AsMVT(grid_cell_data)
FROM grid_cell_data
[WHERE ...]
[GROUP BY ...]

Jim Jones · Answer 2 · 2021-11-12T09:20:44.813

0

The function ST_AsMVT expects a row containing a geometry, not simply a geometry. One option to use it is to select the records in a CTE or subquery, so that you can chose which columns of your table are going to be used, e.g. name and geometryOfCellRepresentativeToTreatment:

SELECT ST_AsMVT(g) FROM (
  SELECT name,geometryOfCellRepresentativeToTreatment
  FROM grid_cell_data) g;

Or if you wish to use the whole record:

SELECT ST_AsMVT(grid_cell_data) 
FROM grid_cell_data

Regarding ST_AsMVTGeom you must provide at least a geometry and a the geometric bounds of the tile contents to make it work:

SELECT
 ST_AsText(
   ST_AsMVTGeom(
     geometryOfCellRepresentativeToTreatment,
     -- change to the envelope that suits your data set
     ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)))) 
FROM grid_cell_data;

Or combining both functions (see documentation):

WITH j AS (
 SELECT
   ST_AsMVTGeom(
     geometryOfCellRepresentativeToTreatment,
     ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)))
 FROM grid_cell_data
)
SELECT ST_AsMVT(j.*) FROM j;

Demo: db<>fiddle

edited Nov 12 '21 at 09:20

answered Nov 12 '21 at 08:49

Jim Jones

18,404
3
35
44

would you please have a look at this question:https://stackoverflow.com/questions/69944107/st-asmvtgeom-returns-empty-column – Amrmsmb Nov 12 '21 at 14:02
hi,would you please have a look at this question:https://stackoverflow.com/questions/69984790/how-to-solve-parse-error-invalid-geometry – Amrmsmb Nov 16 '21 at 06:45
i got it thaks...i will delete the question – Amrmsmb Nov 16 '21 at 07:51
hi, would you please have a look at this question:https://stackoverflow.com/questions/70017681/how-to-convert-cte-to-nested-select – Amrmsmb Nov 18 '21 at 09:52

how to use `ST_AsMVTGeom` and `ST_AsMVT`

2 Answers2

Linked