Solved

Test whether or not a coord exisits in polygon defined by geocodes

Posted on 2010-08-31
5
697 Views
Last Modified: 2012-05-10
I have a list of geocodes that define a polygon on a map and I want to find if a particular geocode lands within the values. The polygon shape in this case is like a 7 but changes in nearly all cases. We are using SQL 2008 and I can define the table any way needed to take a single geocode and return true or false.

-117.218499,33.099865,0
-117.216804,33.09991,0
-117.216182,33.099919,0
-117.215549,33.099811,0
-117.21498,33.099658,0
-117.215602,33.098624,0
-117.21557,33.097519,0
-117.215291,33.096692,0
-117.215055,33.095532,0
-117.215173,33.095317,0
-117.215184,33.095146,0
-117.21616,33.095038,0
-117.21689,33.097267,0
-117.216825,33.098786,0
-117.219089,33.098858,0
-117.218553,33.099847,0
-117.218499,33.099865,0
0
Comment
Question by:jgoodale
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 33565661
first transform you coords in one 2d coord system like utm, then you can use the follow function:

public bool isPointInside( Ponto ponto )
{
    int i, j;
    bool res = false;
    for ( i = 0, j = Count - 1; i < Count; j = i++ ) {
        if ( (((polygono[i].Y <= ponto.Y) && (ponto.Y < polygono[j].Y)) ||
        ((polygono[j].Y <= ponto.Y) && (ponto.Y < polygono[i].Y))) &&
        (ponto.X < (polygono[j].X - polygono[i].X) * (ponto.Y - polygono[i].Y) / (polygono[j].Y - polygono[i].Y) + polygono[i].X) )
            res = !res;
    }
    return res;
}
0
 

Author Comment

by:jgoodale
ID: 33568268
Can you clarify you answer more? I'm not sure what a 2d coord system like utm is..

Thanks!
0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 33569246

You cannot use this function if you coord are in a form of degrees
0
 

Accepted Solution

by:
jgoodale earned 0 total points
ID: 33569427
Ok, I found my own solution using spacial data in MSSQL server 2008. This allows my to take a simple geocode string and test to find if the coords fit inside a polygon using MSSQL.

First off I created a table that contains defined polygons of the areas I want to segment:

CREATE TABLE [dbo].[lku_subdivision](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [coords] [geometry] NULL,
      [subdivision] [varchar](50) NULL,
 CONSTRAINT [PK_lku_subdivision] PRIMARY KEY CLUSTERED
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Then I insert a polygon I defined using this Google map tool: http://www.birdtheme.org/useful/googletool.html?myzoom=3#
I copied the coords from the list in that tool and formatted them for the insert below.

INSERT INTO lku_subdivision (coords,subdivision) VALUES (geometry::STGeomFromText ('POLYGON ((-117.215066 33.094867 ,-117.211483 33.094903 ,-117.210753 33.094112 ,-117.210538 33.093213 ,-117.210538 33.092458 ,-117.211611 33.091775 ,-117.213328 33.091506 ,-117.215302 33.092153 ,-117.216675 33.09307 ,-117.216611 33.093897 ,-117.216311 33.094508 ,-117.21586 33.094885 ,-117.215323 33.094903 ,-117.215066 33.094867 ,-117.215066 33.094867))', 0),'Mahogany');


Now I have a table with my defined polygon and I want to find if a geocoded point fits within the table, If so update a field in my database with the associated name of the subdivision from the table where my polygon is defined:

-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE updatePropertySubdivision
@pointStr varchar(100),
@sysid int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      DECLARE @newSubDivision Varchar(100)
      DECLARE @point1 AS GEOMETRY = 'POINT(' + @pointStr + ')'
            
    -- Insert statements for procedure here
   
   
      SELECT @newSubDivision=subdivision from lku_subdivision WHERE coords.STContains((@point1))=1;
      If @newSubDivision IS NOT NULL
            UPDATE properties SET subdivisionManual=@newSubDivision WHERE sysid=@sysid;
END
GO

The simple test to see if the coords are inside the polygon is below (note that the STContains needs a point and not a string):
SELECT subdivision from lku_subdivision WHERE coords.STContains((@point1))=1






0
 

Author Closing Comment

by:jgoodale
ID: 33569437
Solved
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed in sql query 4 29
Change this SQL to get all nodes 3 40
SQL Instance service gone? 5 38
Converting Stored Procedure to SQL Statement 5 43
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

733 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