none
Geometry query help

    Question

  • 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.For example:
    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 i have to fill in '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
    Tuesday, March 11, 2014 12:47 AM

Answers