基于空间数据的TRIGGER

发布于 2024-10-27 03:20:23 字数 783 浏览 1 评论 0原文

我有一个名为 pano_raw 的表，它有 3 列 lat、lng 和 latlng。

lat 和 lng 只是小数 (10,3)，latlng 是 POINT 类型的空间索引。

这是一个示例语句，它将根据纬度和经度更新数据库。

INSERT INTO pano_raw (latlng) VALUES( GeomFromText( 'POINT(-72.34 32.45)' ));

我正在尝试根据上面的语句创建一个触发器，当我分别更新纬度和经度时，它将自动更新列。由于查询是专门化的，每次使用 SQL 更新空间数据列是一件很痛苦的事情。编写一个触发器（理论上）要容易得多，当我向表中写入一个简单的十进制值时，该触发器就会更新。

我遇到的问题是语法。这是我最好的成绩（2小时的成绩）。

SET @sql := CONCAT('POINT(',pano_raw.lng,' ',pano_raw.lat,')');

CREATE TRIGGER trig_pano_raw BEFORE INSERT ON pano_raw
FOR EACH ROW
BEGIN
    SET pano_raw.latlng = GeomFromText( @sql ) );
END;
CREATE TRIGGER trig_pano_raw BEFORE UPDATE ON pano_raw
FOR EACH ROW
BEGIN
    SET pano_raw.latlng = GeomFromText( @sql ) );
END;

我非常感谢您帮助我完成这项工作。

原文

I have a table called pano_raw that has 3 columns lat, lng, and latlng.

lat and lng are just decimals (10,3) and latlng is a spatial index of type POINT.

This is an example statement that would update the database based on lat and lng.

INSERT INTO pano_raw (latlng) VALUES( GeomFromText( 'POINT(-72.34 32.45)' ));

I'm trying to create a trigger based off the statement above, that will automatically update the column when I update lat and lng separately. It's a pain updating the spatial data column everytime with SQL since the query is specialized. It's much easier to just write a trigger (in theory) that will update when I write a simple decimal value to the table.

The problem I'm having is with the syntax. Here's my best (2 hour shot at it).

SET @sql := CONCAT('POINT(',pano_raw.lng,' ',pano_raw.lat,')');

CREATE TRIGGER trig_pano_raw BEFORE INSERT ON pano_raw
FOR EACH ROW
BEGIN
    SET pano_raw.latlng = GeomFromText( @sql ) );
END;
CREATE TRIGGER trig_pano_raw BEFORE UPDATE ON pano_raw
FOR EACH ROW
BEGIN
    SET pano_raw.latlng = GeomFromText( @sql ) );
END;

I would really appreciate help getting this working.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰葑 2024-11-03 03:20:23

这不行吗？

CREATE TRIGGER trig_pano_raw BEFORE INSERT ON pano_raw
FOR EACH ROW
BEGIN
    SET NEW.latlng = PointFromWKB( POINT( NEW.lat, NEW.lng ) );
END;

关于更新触发器，请注意

第一，它必须有不同的名称，
第二，您可能需要检查哪个字段已更新，例如this：

更新触发器

DELIMITER $
CREATE TRIGGER trig_Update_pano_raw BEFORE UPDATE ON pano_raw
FOR EACH ROW
BEGIN
    IF ((NEW.lat != OLD.lat) OR (NEW.lng != OLD.lng))
    THEN
        SET NEW.latlng = PointFromWKB( POINT( NEW.lat, NEW.lng ) );
    ELSEIF (NEW.latlng != OLD.latlng)
    THEN
        BEGIN
            SET NEW.lat = X(NEW.latlng);
            SET NEW.lng = Y(NEW.latlng);
        END;
    END IF;
END;$
DELIMITER ;

This doesn't work?

CREATE TRIGGER trig_pano_raw BEFORE INSERT ON pano_raw
FOR EACH ROW
BEGIN
    SET NEW.latlng = PointFromWKB( POINT( NEW.lat, NEW.lng ) );
END;

Regarding the Update trigger, note that

1st, it has to have a different name and
2nd, you may want to check which field is updated, like this:

update trigger

DELIMITER $
CREATE TRIGGER trig_Update_pano_raw BEFORE UPDATE ON pano_raw
FOR EACH ROW
BEGIN
    IF ((NEW.lat != OLD.lat) OR (NEW.lng != OLD.lng))
    THEN
        SET NEW.latlng = PointFromWKB( POINT( NEW.lat, NEW.lng ) );
    ELSEIF (NEW.latlng != OLD.latlng)
    THEN
        BEGIN
            SET NEW.lat = X(NEW.latlng);
            SET NEW.lng = Y(NEW.latlng);
        END;
    END IF;
END;$
DELIMITER ;

回复收藏 0 原文

~没有更多了~