判断船进去倾倒区次数

本文主要是介绍判断船进去倾倒区次数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本来想用点连接线来判断跟面相交多少次的，搞了很久搞不定，还是老老实实用点做轨迹判断

WITH a AS (
-- 保持不变
SELECT 0 AS intersect, *
FROM shiphistory AS lines
WHERE mssi = '412044750' AND create_time >= '2023-09-09 00:00' AND create_time <= '2023-09-10 00:00:00'
),
b AS (
-- 保持不变
SELECT 1 AS intersect, lines.create_time, ST_AsText(ST_Intersection(lines.location, polygon.geom)) AS segment_geom
FROM shiphistory AS lines
JOIN "public".dumpingarea AS polygon
ON ST_Intersects(lines.location, polygon.geom)
WHERE mssi = '412044750' AND create_time >= '2023-09-09 00:00' AND create_time <= '2023-09-10 00:00:00'
AND polygon.gid = 101
),
c AS (
SELECT (a.intersect + b.intersect) AS inside, a.*
FROM a
LEFT JOIN b ON a.create_time = b.create_time
)
SELECT
COUNT(*) AS change_count
FROM (
SELECT inside, create_time,
LAG(inside) OVER (ORDER BY create_time) AS previous_inside
FROM c
) subquery
WHERE inside IS DISTINCT FROM previous_inside;

改为函数

CREATE OR REPLACE FUNCTION "public"."calculate_change_count"("mssi_input" text, "create_time_start_input" varchar, "create_time_end_input" varchar, "workid_input" int4)
RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE
change_count_result integer;
BEGIN
WITH a AS (
SELECT 0 AS xianjiao, *
FROM shiphistory AS lines
WHERE lines.mssi = mssi_input
AND lines.create_time >= TO_TIMESTAMP(create_time_start_input, 'YYYY-MM-DD HH24:MI:SS')
AND lines.create_time <= TO_TIMESTAMP(create_time_end_input, 'YYYY-MM-DD HH24:MI:SS')
),
b AS (
SELECT 1 AS xianjiao, lines.create_time, ST_AsText(ST_Intersection(lines.location, polygon.geom)) AS segment_geom
FROM shiphistory AS lines
JOIN "public".dumpingarea AS polygon
ON ST_Intersects(lines.location, polygon.geom)
WHERE lines.mssi = mssi_input
AND lines.create_time >= TO_TIMESTAMP(create_time_start_input, 'YYYY-MM-DD HH24:MI:SS')
AND lines.create_time <= TO_TIMESTAMP(create_time_end_input, 'YYYY-MM-DD HH24:MI:SS')
AND polygon.workid = workid_input
),
c AS (
SELECT (a.xianjiao + b.xianjiao) AS inside, a.*
FROM a
LEFT JOIN b ON a.create_time = b.create_time
)
SELECT ROUND(COUNT(*)/2.0) INTO change_count_result
FROM (
SELECT inside, create_time,
LAG(inside) OVER (ORDER BY create_time) AS previous_inside
FROM c
) subquery
WHERE inside IS DISTINCT FROM previous_inside;

RETURN change_count_result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 1000

调用select * from calculate_change_count(
'412044750',
'2023-09-09 00:00',
'2023-09-10 00:00',
756)

计算证书下所有倾倒量：

with shipLicense as(
SELECT UNNEST(carrier) AS carrier,partition_id,TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
TO_CHAR(end_time,'YYYY-MM-DD HH24:MI:SS') end_time,approve_amount
FROM license
WHERE code = 'TEST')
select sum(calculate_change_count(ship.mssi,shipLicense.start_time,shipLicense.end_time,shipLicense.partition_id)*amount)
from ship inner join shipLicense on ship.id = shipLicense.carrier

函数获取证书下所有倾倒量

CREATE OR REPLACE FUNCTION "public"."calculate_license_data"("licensecode" text)
RETURNS TABLE("license_amount" text) AS $BODY$
BEGIN
RETURN QUERY
WITH shipLicense AS (
SELECT UNNEST(carrier) AS carrier, partition_id,
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
approve_amount AS license_approve_amount
FROM license
WHERE code = licensecode
)
SELECT
sum(calculate_change_count(ship.mssi, shipLicense.start_time, shipLicense.end_time, shipLicense.partition_id)*amount)::text
FROM ship
INNER JOIN shipLicense ON ship.id = shipLicense.carrier;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000

函数获取船倾倒统计

CREATE OR REPLACE FUNCTION "public"."calculate_ship_data"("licensecode" text)
RETURNS TABLE("mssi" text, "change_count" int4, "amount" text, "license_approve_amount" text) AS $BODY$
BEGIN
RETURN QUERY
WITH shipLicense AS (
SELECT UNNEST(carrier) AS carrier, partition_id,
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
approve_amount AS license_approve_amount
FROM license
WHERE code = licensecode
)
SELECT ship.mssi::text,
calculate_change_count(ship.mssi, shipLicense.start_time, shipLicense.end_time, shipLicense.partition_id)::integer, -- 强制转换为integer
ship.amount::text,
shipLicense.license_approve_amount::text
FROM ship
INNER JOIN shipLicense ON ship.id = shipLicense.carrier;