Query to find all zip4's inside polygon

[Chris]

I need to find a way to run a SQL query to find all lat/long points in a
table which are within a polygon which is defined by several lat/long
points.

For example, I have this table which includes ALL ~700,000,000 possible
Zip+4 positions with the corresponding lat/long values.

CREATE TABLE [dbo].[Zip4Coordinates](
[Zip4] [varchar](10) NOT NULL,
[Latitude] [decimal](12, 9) NOT NULL,
[Longitude] [decimal](12, 9) NOT NULL)

And say I have 4 lat/long points (moving in clockwise pattern on map) which
make up my polygon.
In this case, it's 4 lat/long points, but could be up to 12 lat/long points.
Here are the polygon sample points:
40.127462000 -75.140711000
40.136618000 -75.141250000
40.136219000 -75.163252000
40.120532000 -75.162281000

I want to write 1 SQL query which selects all records from [Zip4Coordinates]
which exist INSIDE the polygon consisting of the 4 lat/long points.

Thanks,
Chris

[Erland Sommarskog]

If you are on SQL 2005, get a copy of Adam Machanic's Expert SQL Server
Programminh which includes a good chapter on spatial programming.
(Written by Hugo Kornelis.)

If you are on SQL 2008, you should look into the spatial datatypes,
geography and geometry. Not simple stuff (it goes waaaaay over my
head), but if you are into these problems, learning them is surely
going to pay off.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx