Postgres中的ST_SRID数据类型

What data type should be a variable to contain ST_SRID? I have a function as follows and need to use a expression like srid := ST_SetSRID(ST_MakePoint(start_long, start_lat),4326);. What should be the data type of 'srid'? I tested with integer, point etc.

什么数据类型应该是包含ST_SRID的变量?我有一个如下函数,需要使用像srid这样的表达式:= ST_SetSRID(ST_MakePoint(start_long,start_lat),4326);.什么应该是'srid'的数据类型?我测试了整数,点等。

create function ATest3(start_lat double precision, start_long double precision) returns setof int as $$
declare
  r record;
  srid ?????????????????????????????
begin
  srid := ST_SetSRID(ST_MakePoint(start_long, start_lat), 4326);

  for r in select DISTINCT journey_id, ST_DWithin(srid, geom, 2/111.325), break_id from journey_break_points loop
    return next r.break_id;
  end loop;
  return;
end;
$$ language plpgsql;

2 个解决方案

#1

When you make a point in PostGIS - or any other geometry for that matter - it returns a geometry. So that is the type for your variable.

当您在PostGIS中创建一个点 - 或任何其他几何形状 - 它返回一个几何。这就是变量的类型。

create function ATest3(start_lat double precision, start_long double precision) returns setof int as $$
declare
  r   record;
  pnt geometry;
begin
  pnt := ST_SetSRID(ST_MakePoint(start_long, start_lat), 4326);

  for r in select distinct journey_id, ST_DWithin(pnt, geom, 2/111.325), break_id
           from journey_break_points
    loop
      return next r.break_id;
    end loop;
  return;
end;
$$ language plpgsql;

But you can greatly simplify and speed up this whole process with a simple stored SQL function:

但是,您可以使用简单的存储SQL函数大大简化和加速整个过程:

create function ATest4(start_lat double precision, start_long double precision) returns setof int as $$
  select break_id
  from journey_break_points
  where ST_DWithin(ST_SetSRID(ST_MakePoint($2, $1), 4326), geom, 2/111.325);
$$ language sql;

In the above, both ST_SetSRID() and ST_MakePoint() are defined to be IMMUTABLE, so given constant input they always produce the same output. The query optimizer evaluates the functions on ($2, $1) only once and uses that for all rows of the table in calculation ST_DWithin() with column geom.

在上面,ST_SetSRID()和ST_MakePoint()都被定义为IMMUTABLE,因此给定常量输入它们总是产生相同的输出。查询优化器仅对($ 2,$ 1)上的函数求值一次,并在计算ST_DWithin()中使用列geom的表的所有行。

#2

From the documentation, st_setsrid returns type geometry.

从文档中,st_setsrid返回类型几何。

Synopsis

geometry ST_SetSRID(geometry geom, integer srid);

geometry ST_SetSRID(geometry geom,integer srid);

Description

Sets the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries.

将几何上的SRID设置为特定的整数值。用于构造查询的边界框。

So, your line:

所以,你的行:

srid := ST_SetSRID(ST_MakePoint(start_long, start_lat),4326);

is assigning a geometry to the variable srid in the spatial reference system 4326.

将几何分配给空间参考系统4326中的变量srid。

There is a function st_srid, but that does something different:

有一个函数st_srid,但它做了一些不同的事情:

Synopsis

integer ST_SRID(geometry g1);

整数ST_SRID(几何g1);

Description

Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. Section 4.3.1, “The SPATIAL_REF_SYS Table and Spatial Reference Systems”

返回Spatial_ref_sys表中定义的ST_Geometry的空间参考标识符。第4.3.1节“SPATIAL_REF_SYS表和空间参考系统”

If you're only using your variable in that one statement, I'd be tempted not to bother with the variable and just put the statement in your select query. If you want to stick with the variable, I'd suggest using a different name (e.g. 'startpoint_geometry'), because "srid" means something specific in PostGIS and you risk confusion later on...

如果你只是在那个语句中使用你的变量,那么我很想不去理解变量,只需将语句放在你的select查询中。如果你想坚持使用变量,我建议使用不同的名称(例如'startpoint_geometry'),因为“srid”意味着PostGIS中特定的东西,你可能会在以后混淆......

#1