Solved

check if  a point is in the polygon in mssql

Posted on 2009-05-14
15
808 Views
Last Modified: 2012-05-07
I have two tables. one table with geographic information latitude and longitude. The other table contain polygon information. The polygon information is also in latitide and logitude. Do somebody have source code how to do that calculation in mssql.
0
Comment
Question by:erwingosepa
  • 8
  • 5
15 Comments
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
0
 

Author Comment

by:erwingosepa
Comment Utility
I need to check if the point is within the polygon, not the distance
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Can you post an example of what are you looking for? I'm sure the function I've showed you can make the trick.
 
0
 

Author Comment

by:erwingosepa
Comment Utility
May be I forgot to mention that the code should be for inregular polygons.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Again, please provide an example with dummy data, so I can provide you with an answer.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:erwingosepa
Comment Utility
The poligon information is the following:
point              longitude                            latitude
0      -64.042559081072355      23.234263809565476
1      -57.681113297939824      29.30655296619198
2      -41.488342213602479      26.921010797517283
3      -41.849787996735003      25.258360195107642
4      -53.705209683481989      27.282456580649814
5      -53.416053056975969      23.667998749324511
6      -48.283522936494037      22.800528869806438
7      -48.138944623241031      24.969203568601621
8      -42.138944623241031      24.174022845710052
9      -42.428101249747051      18.535468628842583
10      -52.83773980396392      20.487275857758245
11      -56.018462695530182      23.812577062577525
12      -56.018462695530182      27.571613207155835
13      -57.464245828060307      27.571613207155835
14      -59.343763900349458      25.258360195107642
15      -62.090751852156693      22.005348146914873

I want to know if point latitude 24.877956 longitude -54.456652 is within the polygon.
Do have code in stored procedure or function that can chec in the point is in the polygon?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
You will have to use CLR functions. Please check this link (Query 4 - advanced topics).
http://msdn.microsoft.com/en-us/library/aa964138(SQL.90).aspx#tblvalfuncsql_topic8
and this link to learn how to install the Spatial library:
http://msdn.microsoft.com/en-us/library/ms345264(SQL.90).aspx
0
 

Author Comment

by:erwingosepa
Comment Utility
point = latitude 24.877956 longitude -54.456652

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).
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
I'm not an expert in geography, so I will need your help here. In the example provided, can you explain in English how do I know if that point is in that polygon?
 
0
 

Author Comment

by:erwingosepa
Comment Utility
that is exacly what I'm asking to solve. I do have examples in other program languages but not as function or stored procedure in mssql.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Yeah, but in order for me to produce an algorithm I need to understand the logic of your problem. That's why I'm asking you to explain in English (not in SQL) how do I know whether a point is in one polygon or not.
Also, have you checked the spatial functions I've posted above? There's a specific CLR function that check point in polygon. Have a look again.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
AngelIII I disagree. I have indicated that to do this the asker will need to use the Spatial library in comment http:#a24391825
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now