Help on query with Point (x,y)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on query with Point (x,y)

Author

Topic

Stan92
Starting Member

3 Posts

Posted - 2009-06-18 : 08:58:01

Hi,
I have a table that looks like this

ItemID (int)
Name (varchar)
x1 (int)
y1 (int)
x2 (int)
y2 (int)
x3 (int)
y3 (int)
x4 (int)
y4 (int)

x(s) and y(s) represent points (x,y)(it's a quadrilateral form).

I want to retreive the records where a point (x,y) is inside this form (or surface)
I m not sure to be clear to my limited english..

Any help ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 09:39:02

Something like this if I remember my studies from early '90s...

CREATE FUNCTION dbo.fnIsInTriangle
(
	@px FLOAT,
	@py FLOAT,
	@x1 FLOAT,
	@y1 FLOAT,
	@x2 FLOAT,
	@y2 FLOAT,
	@x3 FLOAT,
	@y3 FLOAT
)
RETURNS BIT
AS
BEGIN
	DECLARE	@a0 FLOAT,
		@a1 FLOAT,
		@a2 FLOAT,
		@a3 FLOAT

	SELECT	@a0 = ABS(@x1 * @y2 + @x2 * @y3 + @x3 * @y1 - @x1 * @y3 - @x3 * @y2 - @x2 * @y1) / 2.0E,
		@a1 = ABS(@px * @y2 + @x2 * @y3 + @x3 * @py - @px * @y3 - @x3 * @y2 - @x2 * @py) / 2.0E,
		@a2 = ABS(@x1 * @py + @px * @y3 + @x3 * @y1 - @x1 * @y3 - @x3 * @py - @px * @y1) / 2.0E,
		@a3 = ABS(@x1 * @y2 + @x2 * @py + @px * @y1 - @x1 * @py - @px * @y2 - @x2 * @y1) / 2.0E

	IF ABS(@a0 - @a1 - @a2 - @a3) < 0.000001E
		RETURN 1

	RETURN 0
END

-- Peso
SELECT	*
FROM	Table
WHERE	dbo.fnIsInTriangle(@x, @y, x1, y1, x2, y2, x3, y3) = 1
	OR dbo.fnIsInTriangle(@x, @y, x2, y2, x3, y3, x4, y4) = 1
	OR dbo.fnIsInTriangle(@x, @y, x1, y1, x3, y3, x4, y4) = 1