5

In my Rails app, I want to store the geographical bounds of places column fields in a database. E.g., the boundary of New York is represented as a polygon: an array of arrays.

I have declared my model to serialize the polygons, but I am unsure whether I should even store them like this. The size of these serialized polygons easily exceed 100,000 characters, and MySQL only can store about 65000 characters in a standard TEXT field.

Now I know MySQL also has a LONGTEXT field. But I really want my app to be database-agnostic. How does Rails handle this by itself? Will it switch automatically to LONGTEXT fields? What about when I start using PostgreSQL?

Community
  • 1
  • 1
Maarten
  • 6,894
  • 7
  • 55
  • 90

4 Answers4

3

At this point I suggest you ask yourself - does this data need to be stored, or should be store in a database in this format?

I propose 2 possible solutions:

  1. Store your polygons in the filesystem, and reference them from the database. Such large data items are of little use in a database - it's practically pointless to query against them as text. The filesystem is good at storing files - use it.

  2. If you do need these polygons in the database, store them as normalised data. Have a table called polygon, and another called point, deserialize the polygons and store it in a way that reflects the way that databases are intended to be used.

Hope this is of help.

thomasfedb
  • 5,990
  • 2
  • 37
  • 65
  • #2 is an interesting idea, though unless the polygons are being modified frequently, it is a more complicated approach. – benzado Feb 06 '13 at 16:43
2

Postgresql has a library called PostGIS that my company uses to handle geometric locations and calculations that may be very helpful in this situation. I believe postgresql also has two data types that allow arrays and hashes. Arrays are declared, as an example, like text[] where text could be replaced with another data type. Hashes can be defined using the hstore module.

Brad Rice
  • 1,334
  • 2
  • 17
  • 36
2

This question answers part of my question: Rails sets a default byte limit of 65535, and you can change it manually.

All in all, whether you will run into trouble after that depends on the database you're using. For MySQL, Rails will automatically switch to the appropriate *TEXT field. MySQL can store up to 1GB of text.

But like benzado and thomasfedb say, it is probably better to store the information in a file so that the database doesn't allocate a lot of memory that might not even be used.

Community
  • 1
  • 1
Maarten
  • 6,894
  • 7
  • 55
  • 90
1

Even though you can store this kind of stuff in the database, you should consider storing it externally, and just put a URL or some other identifier in the database.

If it's in the database, you may end up loading 64K of data into memory when you aren't going to use it, just because you access something in that table. And it's easier to scale a collection of read-only files (using something like Amazon S3) than a database table.

benzado
  • 82,288
  • 22
  • 110
  • 138