14 Nov
2013
14 Nov
'13
1:19 p.m.
I downloaded the Maryland state raw data (the whole enchilada) that
Penelope was good enough to provide me. It came with documentation that
clearly explains what needs to be done but I am being hampered by the sheer
size of the dataset. It's 10 GB and that's without going into joining
tables, transposing them to meet my needs, etc. Even breaking the parts
into different databases it can't be handled in Access. I can fit Part 1
into an ESRI geodatabase but I don't have the flexibility in linking tables
that Access has.
Does anyone have any suggestions for dealing with large databases? SQL
server is one option. Are there others?
*Mara Kaminowitz, GISP*GIS Coordinator
.........................................................................
*Baltimore Metropolitan Council*
Offices @ McHenry Row
1500 Whetstone Way
Suite 300
Baltimore, MD 21230
410-732-0500 ext. 1030
mkaminowitz(a)baltometro.org
www.baltometro.org
14 Nov
14 Nov
8:58 p.m.
Mara
Besides SQL server I have the following suggestions:
1) the ff package in R (
http://www.bnosac.be/index.php/blog/22-if-you-are-into-large-data-and-work-…
)
2) HDF5 seems like a decent option though I have not used it. Link to rhdf5
( http://bioconductor.org/packages/release/bioc/html/rhdf5.html). Also,
SFCTA has some code for getting data into and out of HDF5 (
https://github.com/sfcta/TAutils/tree/master/hdf5)
3) I have found TransCAD to be efficient in processing large datasets.
Hope this helps.
Krishnan
I downloaded the Maryland state raw data (the whole enchilada) that
Penelope was good enough to provide me. It came with documentation that
clearly explains what needs to be done but I am being hampered by the sheer
size of the dataset. It's 10 GB and that's without going into joining
tables, transposing them to meet my needs, etc. Even breaking the parts
into different databases it can't be handled in Access. I can fit Part 1
into an ESRI geodatabase but I don't have the flexibility in linking tables
that Access has.
Does anyone have any suggestions for dealing with large databases? SQL
server is one option. Are there others?
*Mara Kaminowitz, GISP *GIS Coordinator
.........................................................................
*Baltimore Metropolitan Council*
Offices @ McHenry Row
1500 Whetstone Way
Suite 300
Baltimore, MD 21230
410-732-0500 ext. 1030
mkaminowitz(a)baltometro.org
www.baltometro.org
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
15 Nov
15 Nov
8:03 a.m.
Krishnan,
HDF was data format, all climate data is stored on HDF format (I
experience writing C code for this data format on SPARC computers long
time ago and from HDF I converted to MySQL ! ). CTPP is just a flat
data set, txt. Converting into HDF is a different complicated story
and on a wrong direction.
Linking data tables from many tables has to be done using database
engine. MS Access won't cut it. M$ SQL is expensive. Free, Open Source
is the only way and fast.
TransCAD... I never test the limit. But linking simple tables is ok.
Large and many tables ? Better use data modeler software and link to
database engine.
Hary(ono) Prawiranata
Transportation Analyst/Modeler
Tri-County Regional Planning Commission
3135 Pine Tree Rd. Ste 2C
Lansing, MI 48911
On Thu, Nov 14, 2013 at 9:58 PM, Krishnan Viswanathan
<krisviswanathan(a)gmail.com> wrote:
Mara
Besides SQL server I have the following suggestions:
1) the ff package in R (
http://www.bnosac.be/index.php/blog/22-if-you-are-into-large-data-and-work-…)
2) HDF5 seems like a decent option though I have not used it. Link to rhdf5
( http://bioconductor.org/packages/release/bioc/html/rhdf5.html). Also,
SFCTA has some code for getting data into and out of HDF5 (
https://github.com/sfcta/TAutils/tree/master/hdf5)
3) I have found TransCAD to be efficient in processing large datasets.
Hope this helps.
Krishnan
I downloaded the Maryland state raw data (the whole enchilada) that Penelope
was good enough to provide me. It came with documentation that clearly
explains what needs to be done but I am being hampered by the sheer size of
the dataset. It's 10 GB and that's without going into joining tables,
transposing them to meet my needs, etc. Even breaking the parts into
different databases it can't be handled in Access. I can fit Part 1 into an
ESRI geodatabase but I don't have the flexibility in linking tables that
Access has.
Does anyone have any suggestions for dealing with large databases? SQL
server is one option. Are there others?
Mara Kaminowitz, GISP
GIS Coordinator
.........................................................................
Baltimore Metropolitan Council
Offices @ McHenry Row
1500 Whetstone Way
Suite 300
Baltimore, MD 21230
410-732-0500 ext. 1030
mkaminowitz(a)baltometro.org
www.baltometro.org
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
3:39 p.m.
Hary
Thanks for the clarification and pointing out the drawbacks with using
HDF5. I am all for open source software so if MySQL etc. works then great.
I found TransCAD really fast in processing large (though not using 10 GB)
datasets esp. when sorting and found it much faster than standard
statistical packages.
Krishnan
On Fri, Nov 15, 2013 at 9:03 AM, hprawiranata mitcrpc.org <
hprawiranata(a)mitcrpc.org> wrote:
Krishnan,
HDF was data format, all climate data is stored on HDF format (I
experience writing C code for this data format on SPARC computers long
time ago and from HDF I converted to MySQL ! ). CTPP is just a flat
data set, txt. Converting into HDF is a different complicated story
and on a wrong direction.
Linking data tables from many tables has to be done using database
engine. MS Access won't cut it. M$ SQL is expensive. Free, Open Source
is the only way and fast.
TransCAD... I never test the limit. But linking simple tables is ok.
Large and many tables ? Better use data modeler software and link to
database engine.
Hary(ono) Prawiranata
Transportation Analyst/Modeler
Tri-County Regional Planning Commission
3135 Pine Tree Rd. Ste 2C
Lansing, MI 48911
On Thu, Nov 14, 2013 at 9:58 PM, Krishnan Viswanathan
<krisviswanathan(a)gmail.com> wrote:
--
Krishnan Viswanathan
5628 Burnside Circle
Tallahassee FL 32312
Mara
Besides SQL server I have the following suggestions:
1) the ff package in R (
http://www.bnosac.be/index.php/blog/22-if-you-are-into-large-data-and-work-…
)
2) HDF5 seems like a decent option though I have
not used it. Link to
rhdf5
(
http://bioconductor.org/packages/release/bioc/html/rhdf5.html). Also,
SFCTA has some code for getting data into and out of HDF5 (
https://github.com/sfcta/TAutils/tree/master/hdf5)
3) I have found TransCAD to be efficient in processing large datasets.
Hope this helps.
Krishnan
I downloaded the Maryland state raw data (the whole enchilada) that
Penelope
was good enough to provide me. It came with
documentation that clearly
explains what needs to be done but I am being hampered by the sheer size
of
the dataset. It's 10 GB and that's
without going into joining tables,
transposing them to meet my needs, etc. Even breaking the parts into
different databases it can't be handled in Access. I can fit Part 1
into an
ESRI geodatabase but I don't have the
flexibility in linking tables that
Access has.
Does anyone have any suggestions for dealing with large databases? SQL
server is one option. Are there others?
Mara Kaminowitz, GISP
GIS Coordinator
.........................................................................
Baltimore Metropolitan Council
Offices @ McHenry Row
1500 Whetstone Way
Suite 300
Baltimore, MD 21230
410-732-0500 ext. 1030
mkaminowitz(a)baltometro.org
www.baltometro.org
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
7:52 a.m.
My experience handling large data set (climate data set, on linux,
with parallel computers, and with R, Python) was using free software
MySQL (or PostgreSQL, mostly I use MySQL), for easy access and
management use MySQL workbench (
http://www.mysql.com/products/workbench/ ).
If you already install MOVES 2010, you can use MOVES MySQL engine to
dump your data. I use this MySQL to process vehicle registration data
(~9 million records of Michigan vehicles), it is faster than MS Access
cannot handle it.And it is on Windows.. (I have another version on
Linux, too).
I'm interested to dump Michigan data too, how big is this ? Penelope ?
Hary(ono) Prawiranata
Transportation Analyst/Modeler
Tri-County Regional Planning Commission
3135 Pine Tree Rd. Ste 2C
Lansing, MI 48911
On Thu, Nov 14, 2013 at 2:19 PM, Mara Kaminowitz
<mkaminowitz(a)baltometro.org> wrote:
I downloaded the Maryland state raw data (the whole
enchilada) that Penelope
was good enough to provide me. It came with documentation that clearly
explains what needs to be done but I am being hampered by the sheer size of
the dataset. It's 10 GB and that's without going into joining tables,
transposing them to meet my needs, etc. Even breaking the parts into
different databases it can't be handled in Access. I can fit Part 1 into an
ESRI geodatabase but I don't have the flexibility in linking tables that
Access has.
Does anyone have any suggestions for dealing with large databases? SQL
server is one option. Are there others?
Mara Kaminowitz, GISP
GIS Coordinator
.........................................................................
Baltimore Metropolitan Council
Offices @ McHenry Row
1500 Whetstone Way
Suite 300
Baltimore, MD 21230
410-732-0500 ext. 1030
mkaminowitz(a)baltometro.org
www.baltometro.org
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
9:57 a.m.
Actually, I have downloaded whole Michigan CTPP flat file ! Now, in
extracting process. processing it, total data 15,523 MB !, I will dump
it into MySQL and play with data modeler tools I have.
Thanks !
Hary(ono) Prawiranata
Transportation Analyst/Modeler
Tri-County Regional Planning Commission
3135 Pine Tree Rd. Ste 2C
Lansing, MI 48911
On Fri, Nov 15, 2013 at 8:52 AM, hprawiranata mitcrpc.org
<hprawiranata(a)mitcrpc.org> wrote:
My experience handling large data set (climate data
set, on linux,
with parallel computers, and with R, Python) was using free software
MySQL (or PostgreSQL, mostly I use MySQL), for easy access and
management use MySQL workbench (
http://www.mysql.com/products/workbench/ ).
If you already install MOVES 2010, you can use MOVES MySQL engine to
dump your data. I use this MySQL to process vehicle registration data
(~9 million records of Michigan vehicles), it is faster than MS Access
cannot handle it.And it is on Windows.. (I have another version on
Linux, too).
I'm interested to dump Michigan data too, how big is this ? Penelope ?
Hary(ono) Prawiranata
Transportation Analyst/Modeler
Tri-County Regional Planning Commission
3135 Pine Tree Rd. Ste 2C
Lansing, MI 48911
On Thu, Nov 14, 2013 at 2:19 PM, Mara Kaminowitz
<mkaminowitz(a)baltometro.org> wrote:
> I downloaded the Maryland state raw data (the whole enchilada) that Penelope
> was good enough to provide me. It came with documentation that clearly
> explains what needs to be done but I am being hampered by the sheer size of
> the dataset. It's 10 GB and that's without going into joining tables,
> transposing them to meet my needs, etc. Even breaking the parts into
> different databases it can't be handled in Access. I can fit Part 1 into an
> ESRI geodatabase but I don't have the flexibility in linking tables that
> Access has.
>
>
>
> Does anyone have any suggestions for dealing with large databases? SQL
> server is one option. Are there others?
>
>
>
> Mara Kaminowitz, GISP
> GIS Coordinator
> .........................................................................
> Baltimore Metropolitan Council
> Offices @ McHenry Row
> 1500 Whetstone Way
> Suite 300
> Baltimore, MD 21230
> 410-732-0500 ext. 1030
> mkaminowitz(a)baltometro.org
> www.baltometro.org
>
>
>
>
> _______________________________________________
> ctpp-news mailing list
> ctpp-news(a)ryoko.chrispy.net
> http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
>
8:23 a.m.
My first thought would be installing MySQL, though I believe PostgrelSQL is supposedly the
better choice at the moment. Obviously if you already have a SQL Server implementation,
that would be the easiest path to follow, though I believe SQL Server Express has the same
file size limitations as Access. Somebody please correct me if I am mistaken.
John-Paul Hopman
> I downloaded the Maryland state raw data (the
whole enchilada) that
> Penelope was good enough to provide me. It came with documentation that
> clearly explains what needs to be done but I am being hampered by the
> sheer sizeof the dataset. It's 10 GB and that's without going into
> joining tables, transposing them to meet my needs, etc. Even breaking the
> parts into different databases it can't be handled in Access. I can fit
> Part 1 into an ESRI geodatabase but I don't have the flexibility in
> linking tables that Access has.
>
> Does anyone have any suggestions for dealing with large databases? SQL
> server is one option. Are there others?
>
> Mara Kaminowitz, GISP
> GIS Coordinator
> .........................................................................
> Baltimore Metropolitan Council
> Offices @ McHenry Row
> 1500 Whetstone Way
> Suite 300
> Baltimore, MD 21230
> 410-732-0500 ext. 1030
> mkaminowitz(a)baltometro.org
> www.baltometro.org
>
> _______________________________________________
> ctpp-news mailing list
> ctpp-news(a)ryoko.chrispy.net
> http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
8:41 a.m.
I think SQL Server express is 10GB and Access is 2GB. So SQL would work
for me if I broke the databases up into Parts 1-3, which I was going to do
anyway.
-----Original Message-----
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces@chrispy.net]
On Behalf Of John-Paul Hopman
Sent: Friday, November 15, 2013 9:24 AM
To: CTPP News
Subject: Re: [CTPP] Adventures in large datasets
My first thought would be installing MySQL, though I believe PostgrelSQL
is supposedly the better choice at the moment. Obviously if you already
have a SQL Server implementation, that would be the easiest path to
follow, though I believe SQL Server Express has the same file size
limitations as Access. Somebody please correct me if I am mistaken.
John-Paul Hopman
> I downloaded the Maryland state raw data (the
whole enchilada) that
> Penelope was good enough to provide me. It came with documentation
> that clearly explains what needs to be done but I am being hampered
> by the sheer sizeof the dataset. It's 10 GB and that's without going
> into joining tables, transposing them to meet my needs, etc. Even
> breaking the parts into different databases it can't be handled in
> Access. I can fit Part 1 into an ESRI geodatabase but I don't have
> the flexibility in linking tables that Access has.
>
> Does anyone have any suggestions for dealing with large databases?
> SQL server is one option. Are there others?
>
> Mara Kaminowitz, GISP
> GIS Coordinator
>
.........................................................................
> Baltimore Metropolitan Council
> Offices @ McHenry Row
> 1500 Whetstone Way
> Suite 300
> Baltimore, MD 21230
> 410-732-0500 ext. 1030
> mkaminowitz(a)baltometro.org
> www.baltometro.org
>
> _______________________________________________
> ctpp-news mailing list
> ctpp-news(a)ryoko.chrispy.net
> http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
_______________________________________________
ctpp-news mailing list
ctpp-news(a)ryoko.chrispy.net
http://ryoko.chrispy.net/mailman/listinfo/ctpp-news
4010
days inactive
4011
days old
7 comments
4 participants
participants (4)
-
hprawiranata mitcrpc.org -
John-Paul Hopman -
Krishnan Viswanathan -
Mara Kaminowitz