on 02-17-2021 3:51 PM
Hello,
I try to create a procedure (hdbprocedure) to randomly generate a point inside a Polygon. Here is My code:
PROCEDURE "generate_points"( IN city NVARCHAR(30), IN npoints Integer, out ex_points table (id integer, newpoint ST_GEOMETRY))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*************************************
Write your procedure logic
*************************************/
declare polyg ST_GEOMETRY;
declare xmin DOUBLE;
declare ymin DOUBLE;
declare xmax DOUBLE;
declare ymax DOUBLE;
declare dx DOUBLE;
declare dy DOUBLE;
declare xp DOUBLE;
declare yp DOUBLE;
declare xrand DOUBLE;
declare yrand DOUBLE;
declare i Integer;
declare point ST_GEOMETRY;
i = 0;
--Get the Polygon from the City Name in my reference Table
select "POLYGON" into polyg From "CDS_CITIES" where "CITY_NAME" = :city;
--Transform to use the ST_Within
polyg = polyg.ST_Transform(1000004326);
--get the polygon boundaries
xmin = polyg.ST_XMin();
xmax = polyg.ST_XMax();
dx = (xmax - xmin);
ymin = polyg.ST_YMin();
ymax = polyg.ST_YMax();
dy = (ymax - ymin);
--loop while we have the correct number of points...
WHILE i <= npoints DO
xrand = Rand();
yrand = Rand();
xp = xmin + (dx * Rand());
yp = ymin + (dy * Rand());
--Generate the Point
select ST_GeomFromText('POINT(' || xp || ' ' || yp || ')',1000004326) into point from DUMMY;
IF point.ST_Within(polyg) = 1 THEN
--The point is in the polygon
i = i + 1;
--Transform it to the correct SRID
select point.ST_Transform(4326) into point from dummy;
--Insert into the result.
:ex_points.INSERT((i,point),i);
END IF;
END WHILE;
END;
This is working fine and Gives me for example the following result:
1 010100000000006078567E02400000888370724840
2 010100000000002078CEE602400000022FC9684840
But I would like to get the WKT instead, So I modified my procedure like that:
:ex_points.INSERT((i, point.ST_AsWKT()), i);
But Now When I build I get the following error:
Database error 1291: : expression is of wrong type: cannot convert 'locator' to 'st_geometry'
I do not understand Why. I tried a lot of thing but nothing works.
Changing the Point creation as follow:
select ST_GeomFromText('POINT(' || xp || ' ' || yp || ')',1000004326).ST_AsWKT() into point from DUMMY;
=> Same Error.
Adding the following after:
select point.ST_AsWKT() into point from dummy; => Same error
Changing the Point Creation as follow:
select ST_GeomFromText('POINT(' || xp || ' ' || yp || ')',1000004326).ST_AsWKT() as point from DUMMY;
=> Then it compiles OK, but When I run I always get NULL in point.
I am a new in SQLScript Dev, I didn't find anything about the 'locator" error.
Could Someone Help me to get the WKT in the result Table or point the error I made.
Thanks
Hi Guillaume,
ex_points is a typed return table:
out ex_points table (id integer, newpoint ST_GEOMETRY)
When you use the WKT (Well-known Text) representation of the point, it is not of type ST_GEOMETRY anymore. When doing point.ST_ASWKT(), you need to simultaneously change the return type to something like NVARCHAR(5000).
out ex_points table (id integer, newpoint NVARCHAR(5000))
If this issue still persists, would it be possible to strip down the example, so that I can run it on my machine?
Regards,
Mathias
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.