How to get all the objects in a table where the coordinates intersects with polygon with SQL Spatial Data

142 Views Asked by At

I have an SQL table which called DATAB which contains thousands of objects ordered by the following colunms: [id] [angel] [latitude] [longitude]

And i have this polygon set with STPolyFromText:

DECLARE @polygon geography;
SET @polygon = geography::STPolyFromText('POLYGON((35.22272601642959 32.78460617177918, 35.08671584135056 32.78345098011482, 35.11007112393986 32.873510861418836, 35.213109135363425 32.86716347866004, 35.22272601642959 32.78460617177918))', 4326);

Now i want to make a SELECT query that returns all the rows where the waypoints (consisting of the longitude and latitude variables that are in each row) are on the border of the polygon or within the area of the polygon.

Be aware that the variables latitude and longitude that are in each row are just numbers, not geography objects.

I tried to do somthing like this:

DECLARE @f geography;
SELECT * FROM DATAB
WHERE (SET @f = geography::STPointFromText('POINT('[longitude]' '[latitude]')', 4326)) ON (@g.STIntersects(@f) = 1)

Obviously, it failed.

Any ideas?

0

There are 0 best solutions below