cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Spacial, Error when Using ST_AsWKT().

Guillaume2
Participant
0 Kudos

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

Guillaume2
Participant
0 Kudos

Hi Mathias,

Thanks, for your quick answer. So if I understand well, I can do a point.ST_AsWKT() and the result will be a String (NVARCHAR(5000)).

So I have added that, just for testing pupose:

declare str_point NVARCHAR(5000);
str_point = point.ST_AsWKT();

But I still get an error at build time:

Database error 266: : inconsistent datatype: NVARCHAR type is incompatible with ST_GEOMETRY type.

Guillaume2
Participant

Mathias,

Sorry, it works fine when I use the str_point I get into it 'POINT (2.3116883663460612 48.894058648496866)'; I was writing something else into my procedure and didn't see the other mistake.

Thanks for your clear explanation.

mkemeter
Product and Topic Expert
Product and Topic Expert
0 Kudos

Glad that worked out!

I thought about your initial problem (just out of curiosity). You are probably aware, that in corner cases your algorithm would be (almost) non-deterministic. I think there is a way to generate points, which are guaranteed to be within the polygon. I have to admit, that it is a bit cumbersome and also I am not clear on how random the distribution of points over the polygon is.

Here is my thought:

  1. Get outer shell of the polygon by using ST_ExteriorRing()
  2. Get two random points on the exterior ring by using ST_LineInterpolatePoint(RAND())
  3. Use ST_MakeLine() to construct a line between the points
  4. Use ST_Intersection() with the original polygon. Note, that the intersection is guaranteed to be non-empty.
  5. Use ST_GeometryN() in combination with ST_NumGeometries() and Rand() to choose a random geometry out of the intersection
  6. Again, use ST_LineInterpolatePoint(RAND()) to get a random point on that geometry
  7. Et voilà - you at least have a pseudo-random point within the polyon

The advantage of this approach is, that you can use it within an ordinary SQL query and you do not have to use SQLScript.

The statement looks a bit cumbersome and I am not sure, if all the corner cases are covered. But still it may be helpful:

SELECT 
	CASE WHEN single_line_or_point.ST_GeometryType() = 'ST_Point'
		THEN single_line_or_point
		ELSE single_line_or_point.ST_LineInterPolatePoint(rand())
	END AS random_point
FROM 
(
	SELECT 
		CASE WHEN lines_and_points.ST_GeometryType() LIKE 'ST_Multi%' OR lines_and_points.ST_GeometryType() LIKE 'ST_GeometryCollection'
		THEN 
			lines_and_points.ST_GeometryN(round(1 + lines_and_points.ST_NumGeometries() * rand(), 0, ROUND_DOWN))
		ELSE
			lines_and_points
		END AS single_line_or_point
	FROM 
	(
		SELECT
			ST_MakeLine(
				geom.ST_ExteriorRing().ST_LineInterPolatePoint(rand()),
				geom.ST_ExteriorRing().ST_LineInterPolatePoint(rand())
			)
			.ST_Intersection(geom) AS lines_and_points
		FROM 
		(
			SELECT 
				ST_GeomFromGeoJson('{ "type": "Polygon", "coordinates": [ [ [ 8.638684451580048, 49.29385299650895 ], [ 8.63823115825653, 49.29367631867302 ], [ 8.638174831867218, 49.2937008087079 ], [ 8.638204336166382, 49.29404541719405 ], [ 8.637970983982086, 49.29404366791846 ], [ 8.637995123863218, 49.29368681440373 ], [ 8.637944161891937, 49.293674569384336 ], [ 8.637635707855225, 49.293807515146376 ], [ 8.637515008449554, 49.29368331582707 ], [ 8.637740314006805, 49.293597600621375 ], [ 8.63773763179779, 49.29352063173825 ], [ 8.637340664863586, 49.29337893870729 ], [ 8.637442588806152, 49.293251239700815 ], [ 8.637930750846863, 49.29343666554686 ], [ 8.637965619564056, 49.293419172572314 ], [ 8.637944161891937, 49.29305181867286 ], [ 8.638161420822144, 49.29305006936203 ], [ 8.638158738613127, 49.29341392467874 ], [ 8.63819897174835, 49.293429668357774 ], [ 8.638657629489899, 49.29322500013798 ], [ 8.638786375522614, 49.29334745131177 ], [ 8.638292849063873, 49.29352587962047 ], [ 8.638298213481903, 49.2935696119505 ], [ 8.638794422149658, 49.29372529873058 ], [ 8.638684451580048, 49.29385299650895 ] ] ] }', 1000004326) AS geom
			FROM DUMMY 
		)
	)
)

Accepted Solutions (0)

Answers (1)

Answers (1)

mkemeter
Product and Topic Expert
Product and Topic Expert

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