Solved

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

Posted on 2010-08-31
5
694 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
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.

919 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

14 Experts available now in Live!

Get 1:1 Help Now