Point in Polygon with TSQL

I've got a table with lat/longs for specific points:

CREATE TABLE [Point] (
[PointID] [int],
[Lat] [numeric](10, 6),
[Lon] [numeric](10, 6)
)

I have another table with polygon borders as lat/longs:

CREATE TABLE [Polygon] (
[PolygonID] [int],
[PointNum] [int],
[Lat] [numeric](10, 6),
[Lon] [numeric](10, 6)
)

What I need to do, is come up with a script that will determine which Polygon contains each point. I'd like to be able to assign a PolygonID to each record in the Point table, or give it a 0 if there is no polygon that contains that point.

I'm looking for a way to do this within SQL Server (or possibly with the use of an extended stored procedure if necessary).

Thanks for your help and suggestions!

TylerIf anyone has an interest in this, I suggest you have a look here:

http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=8833

Discussions are on-going and I've found a solution that is working... so far... :)sql

Pls, help. TSQL way, Limit the select of a result set by type and quantity

/*
Thanks in advance for your time and knowledge you would share.
Definition:
I have actions, that are of certain type, and workload of these actions to
process
The request comes as a recordset in request table.
How do I produce a record set that has in it quantity of records less or
equal to request quantities by type
I know that I can open a cursor, use (set rowcount @.memvar) and loop to
accumulate final resultset.
I know that if I define an identity field (or select into temp table with
identity) and count these records by selfjoining
But is there a better non-temp table solution?
Thanks again for your ideas
*/
set nocount on
declare @.work table (orderID int, status int, actionID int, primary key
(orderid, status,actionID))
declare @.actions table (actionID int, actionType int, primary key
(actionID))
declare @.request table (actionType int, RecQty int)
declare @.temp table (rec int identity, actionType int , orderID int, status
int, actionID int)
insert @.request values (1,3) -- deliver at least 3 records of this type
insert @.request values (2,8) -- deliver at least 8 records of this type
insert @.request values (3,3) -- deliver at least 3 records of this type
insert @.actions values (1, 1)
insert @.actions values (2, 1)
insert @.actions values (3, 2)
insert @.actions values (4, 2)
insert @.actions values (5, 2)
insert @.actions values (6, 3)
insert @.work values (1, 1, 1)
insert @.work values (1, 1, 2)
insert @.work values (1, 1, 3)
insert @.work values (2, 1, 1)
insert @.work values (2, 1, 2)
insert @.work values (2, 2, 3)
insert @.work values (2, 2, 4)
insert @.work values (2, 1, 6)
insert @.work values (3, 1, 1)
insert @.work values (3, 3, 2)
insert @.work values (3, 1, 5)
insert @.work values (3, 1, 6)
insert @.work values (4, 1, 1)
insert @.work values (4, 1, 2)
insert @.work values (4, 1, 5)
insert @.work values (4, 1, 6)
insert @.work values (4, 2, 6)
insert @.work values (4, 3, 6)
-- Possible solution
-- Total records
select actionType, count(*) recordcount
from @.actions a
join @.work w on w.actionID= a.actionID
group by actionType
insert @.temp (actionType, orderID, status, actionID)
select a.actionType, orderID, status, w.actionID
from @.actions a
join @.work w on w.actionID= a.actionID
-- This is what I am after, but any way not to use temp table as this would
cause procedure recompiles and this is often called one.
select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID, count(*)
from @.temp s1
join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
join @.request r on r.actionType = s1.actionType
group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
having count(*) <= r.RecQtyTry that based on the example of the oubs database:
DECLARE @.RANK INT
SET @.rank = 5
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
HAVING count(*) < @.Rank
To be found on:
http://support.microsoft.com/defaul...b;en-us;Q186133
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
--
"Farmer" <someone@.somewhere.com> schrieb im Newsbeitrag
news:Of0pCjyUFHA.544@.TK2MSFTNGP15.phx.gbl...
> /*
> Thanks in advance for your time and knowledge you would share.
> Definition:
> I have actions, that are of certain type, and workload of these actions to
> process
> The request comes as a recordset in request table.
> How do I produce a record set that has in it quantity of records less or
> equal to request quantities by type
> I know that I can open a cursor, use (set rowcount @.memvar) and loop to
> accumulate final resultset.
> I know that if I define an identity field (or select into temp table with
> identity) and count these records by selfjoining
> But is there a better non-temp table solution?
> Thanks again for your ideas
> */
> set nocount on
> declare @.work table (orderID int, status int, actionID int, primary key
> (orderid, status,actionID))
> declare @.actions table (actionID int, actionType int, primary key
> (actionID))
> declare @.request table (actionType int, RecQty int)
> declare @.temp table (rec int identity, actionType int , orderID int,
> status int, actionID int)
> insert @.request values (1,3) -- deliver at least 3 records of this type
> insert @.request values (2,8) -- deliver at least 8 records of this type
> insert @.request values (3,3) -- deliver at least 3 records of this type
>
> insert @.actions values (1, 1)
> insert @.actions values (2, 1)
> insert @.actions values (3, 2)
> insert @.actions values (4, 2)
> insert @.actions values (5, 2)
> insert @.actions values (6, 3)
> insert @.work values (1, 1, 1)
> insert @.work values (1, 1, 2)
> insert @.work values (1, 1, 3)
> insert @.work values (2, 1, 1)
> insert @.work values (2, 1, 2)
> insert @.work values (2, 2, 3)
> insert @.work values (2, 2, 4)
> insert @.work values (2, 1, 6)
> insert @.work values (3, 1, 1)
> insert @.work values (3, 3, 2)
> insert @.work values (3, 1, 5)
> insert @.work values (3, 1, 6)
> insert @.work values (4, 1, 1)
> insert @.work values (4, 1, 2)
> insert @.work values (4, 1, 5)
> insert @.work values (4, 1, 6)
> insert @.work values (4, 2, 6)
> insert @.work values (4, 3, 6)
> -- Possible solution
> -- Total records
> select actionType, count(*) recordcount
> from @.actions a
> join @.work w on w.actionID= a.actionID
> group by actionType
> insert @.temp (actionType, orderID, status, actionID)
> select a.actionType, orderID, status, w.actionID
> from @.actions a
> join @.work w on w.actionID= a.actionID
> -- This is what I am after, but any way not to use temp table as this
> would cause procedure recompiles and this is often called one.
> select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID,
> count(*)
> from @.temp s1
> join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
> join @.request r on r.actionType = s1.actionType
> group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
> having count(*) <= r.RecQty
>

Please help! TSQL problems!

hi everyone,

although, this is not an asp.net problem but i really need your help!
For example, i had 3 tables (TableA, TableB, TableC)

(Relationship example)

(TableA) (TableB) (TableC)
ID 1--- 1 --1
TableAID ---ID -
TableCID ID----

if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field.

(Sql statement) 'This is my sql statement that cannot output anydata from TableB
select * from TableB where
TableA.ID = TableB.TableAID And
TableC.ID = TableB.TableCID

Thanks everyoneYou need to create an inner join.

Are you using SQL Server?

If so you can do this graphically by creating a new view. Then add the tables you want to use and then select the columns you want to pull data from. Sql then generates the syntax for you.

You have to relate your tables. Each must carry a PrimaryKey and a Foreign key that relates to the PrimaryKey on the other table. eg

Table1
PK_Tbl1
FK_Tbl2_ID

Table2
PK_Tbl2_ID
FK_Tbl3_ID

Table3
PK_Tbl3_ID

An inner join is much more efficient than a cross join.|||I'm struggling to undestand the question, but given...
>> if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field

...sounds like you need to look at LEFT joins.|||thanks for the reply!!!

actually, my project has multiple tables need to be join like this with relationship missing in tables....i am also wondering how to use left or outer join to join multi tables.

thanks|||Reading midi25s reply has made me wonder, do you actually need to have this "missing data"? Are the tables there already and you're working with them or are you also trying to create a data schema?|||i am generating reports from those tables...the report that i am dealing with now is approximately 7 tables, some of the table has missing data and need to be present in the report.
OK for example. i make a sale, but the customer did not put in the shipping address...therefore the shipping address id is missing from the sales table. but in my case, i had around 7 tables with this kind of senerio...

i think i should use left join to solve this problem but do you have any example using left join to join mulitple tables

thank you for helping and hope you can solve my problem