Hi,
I have data that I'd like to organize spatially by date. So far, I'm only succeeding in displaying the number of times something occurs out of the whole dataset.
Here's an example of what I want to do.
343 items that occurred at different times from june through july.
multiple different keywords that are within the items
I want to organize the data into items that occurred in the month of june with dates as the columns (so 30 columns) and keywords that occur in the items as the row...to look like this:
PRB | RBL | SAC | SFO |
June 1 2 | 4 | 5 | 2 |
June 2 1 | 3 | 4 | 0 |
June 3 0 | 2 | 1 | 2 |
So far, here's the query I'm using to display one row of all items with certain keywords, but I've failed at trying to make multiple rows by date.
Here's the query I'm using so far:
select (select count (*) from zoa_pireps where raw_text like '%RBL%') as RBL, (select count(*) from zoa_pireps where raw_text like '%RBL%') as PRB;
Further down the road I want to do spatial relationships linking to a different georeferenced table, but I think if I can get the above example working, the joining shouldn't be overly difficult.
Thanks for your time and let me know if you need more details...the computer I'm doing the queries on is a different one than I'm typing this email, so thus the crude examples.
Shad
Windows Live™ SkyDrive™: Get 25 GB of free online storage.
Get it on your BlackBerry or iPhone.
