For my application, I need to create reports that show which points are contained within a polygon.
The coordinate system for this example is Latitude/Longitude. To define the polygon, there is a table MapcountiesPoints. In this table, there is a FeatureId (to identify the county), a point number field to identify the order in which points are put together, a latitude and longitude to identify the location.
Sample data looks like this....
A polygon is made up from a series of lines. To visualize the boundary, imagine a line drawn from point 1 to point 2, then point 2 to point 3, then point 3 to point 4, and so on. To close the boundary, the last point is connected to the first point.
Download the county data
There is another table, Location that identifies points we want to test. In this table, there is a unique identifier LocationId, and Latitude & longitude to identify the location.
Download the location data
Here's the task....
Write a stored procedure that
1. Accepts FeatureId as an argument
2. Returns a list of LocationId's that are contained within the polygon identifed in the MapCountiesPoints table.
This functionality already works within my application, so I will humbly exclude myself from this puzzle.
Remember, fastest execution time wins. If you have any questions, just ask.
-George
Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
The coordinate system for this example is Latitude/Longitude. To define the polygon, there is a table MapcountiesPoints. In this table, there is a FeatureId (to identify the county), a point number field to identify the order in which points are put together, a latitude and longitude to identify the location.
Code:
CREATE TABLE [MapCountiesPoints] (
[FeatureId] [int] NOT NULL ,
[PointNumber] [int] NOT NULL ,
[Latitude] [decimal](8, 5) NOT NULL ,
[Longitude] [decimal](8, 5) NOT NULL ,
CONSTRAINT [PK_MapCountiesPoints] PRIMARY KEY CLUSTERED
(
[FeatureId],
[PointNumber]
) ON [PRIMARY]
) ON [PRIMARY]
Sample data looks like this....
Code:
FeatureId PointNumber Latitude Longitude
----------- ----------- ---------- ----------
6 1 40.22638 -75.60358
6 2 40.22615 -75.60386
6 3 40.22580 -75.60429
6 4 40.22574 -75.60436
6 5 40.22562 -75.60452
6 6 40.22544 -75.60476
6 7 40.22538 -75.60488
6 8 40.22534 -75.60493
A polygon is made up from a series of lines. To visualize the boundary, imagine a line drawn from point 1 to point 2, then point 2 to point 3, then point 3 to point 4, and so on. To close the boundary, the last point is connected to the first point.
Download the county data
There is another table, Location that identifies points we want to test. In this table, there is a unique identifier LocationId, and Latitude & longitude to identify the location.
Code:
Create TABLE [Location] (
[LocationId] [int] NOT NULL ,
[Longitude] [decimal](8, 5) NULL ,
[Latitude] [decimal](8, 5) NULL ,
CONSTRAINT [PK_Location_LocationId] PRIMARY KEY CLUSTERED
(
[LocationId]
) ON [PRIMARY]
) ON [PRIMARY]
Download the location data
Here's the task....
Write a stored procedure that
1. Accepts FeatureId as an argument
2. Returns a list of LocationId's that are contained within the polygon identifed in the MapCountiesPoints table.
This functionality already works within my application, so I will humbly exclude myself from this puzzle.
Remember, fastest execution time wins. If you have any questions, just ask.
-George
Strong and bitter words indicate a weak cause. - Fortune cookie wisdom