CODEWITHSUNDEEP
postgresqlpostgisplpgsql
prasad katankot
prasad katankot

Reputation: 157

ST_SRID data type in Postgres

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.

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;

Upvotes: 0

Views: 1324

Answers (2)

Patrick
Patrick

Reputation: 32179

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.

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:

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.

Upvotes: 1

mlinth
mlinth

Reputation: 3108

From the documentation, st_setsrid returns type geometry.

Synopsis

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.

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.

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

Synopsis

integer ST_SRID(geometry 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”

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...

Upvotes: 0

Related Questions