1

I am doing a study on taxi demand using block level census data and taxi drop off and pick data in the form of Latitude and Longitude around the united states.

I am hoping to create a map that shows block level counts for taxi demands based on pickup and drop data.

I am trying to aggregate taxi data at the block level then visualize it using arcmap or other visualization tools.

Due to the massive size of my data for the taxi data, I am trying to focus first on Delaware to test the waters.

I have two databases loaded up im MySQL with the Census data and the Taxi data.

How do I go about aggregating data at the census block level?

I have my script for the county level but I do not know how to do it for the block level.

Maybe we can modify my county level script to a block level script.

select * from [dbo].[BLOCK2010]
where STATEFP10 = 10

select geometry::UnionAggregate(Geom) as geom from [dbo].[BLOCK2010] where STATEFP10 = 10
Group By COUNTYFP10

declare @DE geometry = (select geometry::UnionAggregate(Geom) as geom from [dbo].[CB2010_PH] where STATEFP10 = 10)
--select @DE
SELECT [GEOM_BeginTrip] from [dbo].[TAXI_DATA] where [GEOM_BeginTrip].STIntersects(@DE) = 1 union all select @DE
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
ABaut
  • 101
  • 1
  • 4

1 Answers1

0

Aggregating data is pretty simple in ArcGIS. You use 'select by location' to select all points within a given block, and then use 'Dissolve', selecting the 'sum' statistic. Then just make a loop to do the same for each block.

Mox
  • 1,036
  • 6
  • 15