Syntax error when trying to create a stored procedure in MySQL

Question

I'm trying to create a stored procedure in MySQL to check if a given POINT is inside a POLYGON but I get the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7

This is my query:

CREATE PROCEDURE IsInsidePolygon(
    IN polygon_coordinates VARCHAR(200),
    IN point_coordinates VARCHAR(200)
)
BEGIN
    DECLARE
        polygonData GEOMETRYCOLLECTION; 
    DECLARE 
        pointData POINT;
    SET
        polygonData = ST_GEOMFROMTEXT(
            CONCAT('POLYGON((', polygon_coordinates, '))')
        );
    SET
        pointData = ST_GEOFROMTEXT(
            CONCAT('POINT(', point_coordinates, ')')
        );
    SELECT
        post_id
    FROM
        wp_postmeta
    WHERE
        ST_CONTAINS(polygonData, pointData);
END;

I'm a beginner in SQL so I can't tell what I'm doing wrong here. Thanks in advance!

What query tool are you running this in?

Caius Jard
– Caius Jard

2020-08-06 15:56:16 +00:00
Commented Aug 6, 2020 at 15:56 — Caius Jard
– Caius Jard, Commented Aug 6, 2020 at 15:56
I'm using 'phpmyadmin'.

Jon
– Jon

2020-08-06 15:58:04 +00:00
Commented Aug 6, 2020 at 15:58 — Jon
– Jon, Commented Aug 6, 2020 at 15:58

Caius Jard · Accepted Answer · 2020-08-06 15:58:14Z

1

You had an undeclared variable coordinates that is subbed with polygon_coordinates and a missing semicolon after the query at the end:

CREATE PROCEDURE IsInsidePolygon(
    IN polygon_coordinates VARCHAR(200),
    IN point_coordinates VARCHAR(200)
)
BEGIN
    DECLARE
        polygonData GEOMETRYCOLLECTION; 
    DECLARE 
        pointData POINT;
    SET
        polygonData = ST_GEOMFROMTEXT(
            CONCAT('POLYGON((', polygon_coordinates, '))')
        );
    SET
        pointData = ST_GEOFROMTEXT(
            CONCAT('POINT(', point_coordinates, ')')
        );
    SELECT
        post_id
    FROM
        wp_postmeta
    WHERE
        ST_CONTAINS(polygonData, pointData);
END

answered Aug 6, 2020 at 15:58

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jon Over a year ago

Thanks, I corrected the ones you mentioned but I still get the same error at the same line. Will update the question now.

Caius Jard Over a year ago

You might need to set the DELIMITER to something else at the start of the script/in phpmyadmin e.g. set it to // and then adjust your END to be END//. You could also try the "Add Routine" facility instead (Routines tab)

Jon Over a year ago

That was it! All I had to do was add "DELIMITER $$" at the beginning. Thanks a lot!

Jon · Accepted Answer · 2020-08-06 16:12:39Z

The problem was a typo in the naming of one of the variable and a missing 'DELIMITER $$' at the beginning.

DELIMITER $$

CREATE PROCEDURE IsInsidePolygon(
    IN polygon_coordinates VARCHAR(200),
    IN point_coordinates VARCHAR(200)
)
BEGIN
    DECLARE
        polygonData GEOMETRYCOLLECTION; 
    DECLARE 
        pointData POINT;
    SET
        polygonData = ST_GEOMFROMTEXT(
            CONCAT('POLYGON((', polygon_coordinates, '))')
        );
    SET
        pointData = ST_GEOFROMTEXT(
            CONCAT('POINT(', point_coordinates, ')')
        );
    SELECT
        post_id
    FROM
        wp_postmeta
    WHERE
        ST_CONTAINS(polygonData, pointData);
END

Thanks everyone for the help!

Ps. This query was run in "phpmyadmin".

Collectives™ on Stack Overflow

Syntax error when trying to create a stored procedure in MySQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related