GEOMETRY Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GEOMETRY Query help

Author

Topic

hayko98
Starting Member

29 Posts

Posted - 2014-03-10 : 19:48:41

Hi,
I have 2 tables:
CREATE TABLE AREA
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ZONENAME] [nvarchar](255) NULL,
[geom] [geometry] NULL

)
GO
CREATE TABLE TRIP
(
[TRIP_ID] [int] NOT NULL,
[DUE_TIME] [datetime] NULL,
[PICKUP_ADDRESS_GEOCODE_X] [int] NULL,
[PICKUP_ADDRESS_GEOCODE_Y] [int] NULL,
[DROPOFF_ADDRESS_GEOCODE_X] [int] NULL,
[DROPOFF_ADDRESS_GEOCODE_Y] [int] NULL

)
Area table has 4 polygons:East,West,North and South.Clients are traveling from one area to other as well as inside their area.i need to find trip COUNTs based on their Pickup(PU) and Dropoff(DO) area.Foe exaple:
ZONENAME EAST WEST NORTH SOUTH
East 6699 0 0 0
West 0 4381 0 0
North 0 0 8454 0
South 0 0 0 2623
All trips PU East to DO East is 6699..
All trips PU West to Do West is 4381.But i also need counts from PU East to DO West...Another words Find '0's
here is what i come up with so far:
SELECT A.ZONENAME ,
SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND A.ID=1
THEN 1 ELSE 0 END ) AS EAST,
SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND A.ID=2)
THEN 1 ELSE 0 END ) AS NORTH,
SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND A.ID=3)
THEN 1 ELSE 0 END ) AS SOUTH ,
SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) = 1
AND A.ID=4)
THEN 1 ELSE 0 END ) AS WEST
FROM TRIP T,AREA A
GROUP BY A.ZONENAME

A.ID
1----EAST
2----WEST
3----NORTH
4----SOUTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources