March 10, 2014 at 6:09 pm
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:
ZONENAMEEASTWESTNORTHSOUTH
East 6699000
West 0438100
North 0084540
South 0002623
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
March 10, 2014 at 8:28 pm
Hi
Without anything to test this against, I think that you will need to join to the Area table for each of the pickup and dropoff.
This might do the trick for you.
WITH tripEnds AS (
SELECT t.TRIP_ID, pu.ID pickupZID, pu.ZONENAME pickupZone, do.ID droffoffZID
FROM TRIP t
INNER JOIN AREA pu ON pu.geom.STIntersects(Geometry::Point(t.PICKUP_ADDRESS_GEOCODE_X / 1000000.0, t.PICKUP_ADDRESS_GEOCODE_Y / 1000000.0, 0)) = 1
INNER JOIN AREA do ON do.geom.STIntersects(Geometry::Point(t.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0, t.DROPOFF_ADDRESS_GEOCODE_Y / 1000000.0, 0)) = 1
)
SELECT pickupZone
,SUM(CASE WHEN droffoffZID = 1 THEN 1 ELSE 0 END) East
,SUM(CASE WHEN droffoffZID = 2 THEN 1 ELSE 0 END) West
,SUM(CASE WHEN droffoffZID = 3 THEN 1 ELSE 0 END) North
,SUM(CASE WHEN droffoffZID = 4 THEN 1 ELSE 0 END) South
FROM tripEnds
GROUP BY pickupZone;
I didn't put the STBuffer in as you should need that. Also you may want to consider making Geometries for your pickup and dropoff addresses and spatial indexing them, but that would depend on how much data you have in your trip table and how often it is added to.
Hope this is what you were after
Edit: Fixed bad aliases and a few more
March 11, 2014 at 10:27 am
Thank you for your reply ,but i got same results with '0's
March 11, 2014 at 12:13 pm
Barcelona10 (3/11/2014)
Thank you for your reply ,but i got same results with '0's
Sorry, my bad:crying:. I had the alias wrong for the dropoffZID. I have fixed that up and a couple of other syntax errors in my original post. I tested it against a tiny test set that I created and it should work for you now.
March 11, 2014 at 2:01 pm
Can i see it ?
March 11, 2014 at 2:23 pm
Hi
I edited my previous post, so there wasn't a bad query in the thread.
Here's the latest.WITH tripEnds AS (
SELECT t.TRIP_ID, pu.ID pickupZID, pu.ZONENAME pickupZone, do.ID droffoffZID
FROM TRIP t
INNER JOIN AREA pu ON pu.geom.STIntersects(Geometry::Point(t.PICKUP_ADDRESS_GEOCODE_X / 1000000.0, t.PICKUP_ADDRESS_GEOCODE_Y / 1000000.0, 0)) = 1
INNER JOIN AREA do ON do.geom.STIntersects(Geometry::Point(t.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0, t.DROPOFF_ADDRESS_GEOCODE_Y / 1000000.0, 0)) = 1
)
SELECT pickupZone
,SUM(CASE WHEN droffoffZID = 1 THEN 1 ELSE 0 END) East
,SUM(CASE WHEN droffoffZID = 2 THEN 1 ELSE 0 END) West
,SUM(CASE WHEN droffoffZID = 3 THEN 1 ELSE 0 END) North
,SUM(CASE WHEN droffoffZID = 4 THEN 1 ELSE 0 END) South
FROM tripEnds
GROUP BY pickupZone;
March 11, 2014 at 3:01 pm
It's a same query
March 11, 2014 at 3:24 pm
Yes, but there is a change to what I originally posted before I edited it to the select clause in the CTE.
I orignally had SELECT t.TRIP_ID, pu.ID pickupZID, pu.ZONENAME pickupZone, pu.ID droffoffZIDThis meant that it was returning the same zone for the dropoff as the pickup.
That has been changed to SELECT t.TRIP_ID, pu.ID pickupZID, pu.ZONENAME pickupZone, do.ID droffoffZID
Running this against a small test set that I cooked up for this:
Area
1EastPOLYGON ((10 10, 20 0, 20 20, 10 10))
2WestPOLYGON ((10 10, 0 0, 0 20, 10 10))
3NorthPOLYGON ((10 10, 20 20, 0 20, 10 10))
4SouthPOLYGON ((10 10, 0 0, 20 0, 10 10))
Trip12014-01-01 00:00:00.0002000000 5000000 2000000 15000000
22014-01-01 00:00:00.0002000000 5000000 2000000 10000000
32014-01-01 00:00:00.0002000000 5000000 1800000015000000
42014-01-01 00:00:00.0002000000 5000000 100000002000000
52014-01-01 00:00:00.000180000005000000 1800000015000000
62014-01-01 00:00:00.000180000005000000 2000000 5000000
I get the following ResultspickupZoneEastWestNorthSouth
East 1100
West 1201
Try the latest and see if it works for you. If not, please post some sample data as insert statements and I will investigate further.
March 11, 2014 at 4:02 pm
It WORKED!!!
Thank you very very much.
675169113071298
6934392315846
131730384862427
122277722123238
March 11, 2014 at 4:22 pm
No problem
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply