栅栏阵列上的ST_DWITHIN

Question

我正在尝试评估数组中的ST_WITHIN，但是我在谷歌上找不到解决方案，然后我试图找到有关它的帮助

我已经附加了我的代码，我的目标是从一个表中获得栅栏，其中存储栅栏和每个他们我收到的位置，纬度，经度比较我的栅栏，知道这是进入这个栅栏

CREATE OR REPLACE FUNCTION insert_position(
    _imei character varying(8),
    _datagram text,
    _created timestamp with time zone,
    _updated timestamp with time zone,
    _date_time_process timestamp with time zone,
    _latitude numeric,
    _longitude numeric,
    _course integer,
    _speed integer,
    _mileage integer,
    _gps_signal integer,
    _gsm_signal integer,
    _alarm_status boolean,
    _gps_status boolean,
    _vehicle_status boolean,
    _alarm_over_speed boolean,
    _other text
    ) RETURNS integer AS $$ 
DECLARE
    device_id integer;
BEGIN
    SELECT gpstracking_devices.id INTO device_id FROM gpstracking_devices WHERE gpstracking_devices.imei = $1;
    INSERT INTO gpstracking_device_tracks (device_id, imei, datagram, created, updated, date_time_process, latitude, longitude, course, speed, mileage, gps_signal, gsm_signal, alarm_status, gps_status, vehicle_status, alarm_over_speed, other, point) 
    VALUES (device_id, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, ST_Transform(ST_SetSRID(ST_MakePoint($6, $7), 4326),26913));
    SELECT geometry WHERE gpstracking_fences.id in ( SELECT gpstracking_fences_devices.fence_id from gpstracking_fences_devices WHERE gpstracking_fences_devices.device_id = device_id)

    -- I need to iterate this geometris into a ST_WITHIN

    RETURN device_id;
END
$$ 
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

Answer 1

我不确定我是否完全理解您的用例，但也许您可以使用Postgres循环，如下所示：

CREATE OR REPLACE FUNCTION insert_position(
  -- your args 
DECLARE
 device_id integer;
 intersect_count
BEGIN
    --
    -- initial logic
    --
    FOR mygeom IN SELECT geometry WHERE gpstracking_fences.id in ( SELECT gpstracking_fences_devices.fence_id from gpstracking_fences_devices WHERE gpstracking_fences_devices.device_id = device_id)
    LOOP
      IF  (ST_Within(ST_GeomFromText('POINT(-1.234 4.321)', YOUR_SRID), mygeom))  THEN
         intersect_count = intersect_count+1;
      END IF;
    END LOOP;
    RETURN device_id;
END
-- ....