Solved

check if  a point is in the polygon in mssql

Posted on 2009-05-14
15
812 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
ID: 24386255
0
 

Author Comment

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

Expert Comment

by:ralmada
ID: 24387035
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Expert Comment

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

Expert Comment

by:ralmada
ID: 24390863
0
 

Author Comment

by:erwingosepa
ID: 24391188
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
ID: 24391825
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
ID: 24421861
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
ID: 24422175
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
ID: 24422999
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
ID: 24423261
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
ID: 25576239
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
sql 2014,  lock limit 5 30
sql select record as one long string 21 22
T-SQL:  Collapsing 9 22
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

785 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