3

I have a JSON data in a text file which contains pair of latitude and longitude,Now I want to store this data into MYSQL DB,How to go about & what's the best approach please suggest.

Here is My JSON data

[{"latlon":{"lng":77.75124312,"lat":12.97123123},"type":"s"}, 
 {"latlon":{"lon":77.73004942455374,"lat":12.98227579706589},"type":"s"},
 {"latlon":{"lon":77.67374449291539,"lat":12.995490063545521},"type":"v"}, 
 {"latlon":{"lon":77.6097147993144,"lat":12.970900929013666},"type":"s"},
 {"latlon":{"lon":77.53933363476645,"lat":12.948316929346504},"type":"s"},
 {"latlon":{"lng":77.48213123,"lat":12.91213213},"type":"s"}
 .
 .
 .
 .
]
The String may go up to 50 points
Rakesh
  • 59
  • 1
  • 2
  • 7
  • create a clob column in table and store it. – Naveen Ramawat Jun 30 '15 at 05:41
  • 1
    You can store it in a TEXT column. If you have a need to search based on this data, sphinx has some very good functions to parse and search JSON data. – yetanotherse Jun 30 '15 at 05:41
  • I am using JAVA,Please suggest in that – Rakesh Jun 30 '15 at 05:43
  • 2
    If you use TEXT or blob you will loose the relational features of mysql. Better parse the JSON and insert the data into the table field wise. – ikrabbe Jun 30 '15 at 05:43
  • Note that mysql 5.7 will natively support JSON as a new type of column with new functions `JSN_*` and new ways of indexing, combining nosql performance advantages on huge read load with relational advantages of searching and manipulating smaller chunks of data. – piotrm Jun 30 '15 at 07:04

4 Answers4

11

Some comments have suggested leaving it in JSON format and storing it in a text column.

I'd suggest that if you have control over your schema, you should store it in two NUMERIC fields - one for latitude, and one for longitude. Fundamentally, that's the data you have. The fact that you've received it using JSON as a container format is irrelevant to the data itself.

If you store it as a latitude and a longitude, it's simpler to query over it, and you can still recreate the JSON later if you want to... but if you end up wanting to fetch it not as JSON, you'll be in a much better position to do so. It also means you're not tying yourself to JSON against future changes.

Personally I think it's always a good idea to mentally separate the intrinsic nature of the data from the way that you happen to receive it. Store the data in whatever format is idiomatic for that storage - so if you're storing a number, use one of the numeric data types. If you're storing a date and time, use datetime, etc. That way you don't end up with an extra layer between you and the data itself every time you want to access it.

This approach also means you're much more likely to spot bad data early on - if you just store the JSON directly without parsing it first, you could later find that the latitude or longitude isn't a valid number. Heck, the fact that your sample data sometimes has lon and sometimes has lng suggests you should do some data cleanup anyway.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Ok,Then I have to parse it and store one-by-one – Rakesh Jun 30 '15 at 05:48
  • @Rakesh: Yes, I would. Indeed, I'd probably create a class to store a latitude and longitude pair, and separate out the two aspects: parsing the JSON into objects, and storing the result into the database. That way you can test each part separately, and you're not tying the two entirely separate storage/transport formats together. – Jon Skeet Jun 30 '15 at 05:50
  • Ok got the logic thanks,But if am not using the pair in future then can we insert it as a JSON object – Rakesh Jun 30 '15 at 05:59
  • @Rakesh: What do you mean "if am not using the pair in future"? If you're not going to use the data, why store it at all? If you're going to read it later, why force all your various potential readers to parse the JSON each time they read it, instead of working with it in a natural, numeric form? – Jon Skeet Jun 30 '15 at 06:03
  • Sorry, got my previous question wrong. Thank You very much for Your invaluable support. Will get back to You, where ever I would need essential advice. – Rakesh Jun 30 '15 at 06:17
0

If you are using Java for application you can do something like this.

Convert JSONObject into String and save as TEXT/ VARCHAR. While retrieving the same column convert the String into JSONObject.

For example

Write into DB

String stringToBeInserted = jsonObject.toString();
//and insert this string into DB

Read from DB

String json = Read_column_value_logic_here
JSONObject jsonObject = new JSONObject(json);

Ref: how to store JSON object in SQLite database

Community
  • 1
  • 1
Avinash Sahu
  • 249
  • 3
  • 19
0

Basically, if that's a data that matters to your software (if there will be queries involving those data), you should considere parsing the JSON.

If not : store it as it is !

Mathieu
  • 365
  • 2
  • 9
0

From Mysql 5.7.10 and higher JSON support is native so you can have a JSON data type

Document Validation - Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data.

Efficient Access - More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.

Performance - Improve your query performance by creating indexes on values within the JSON columns. This can be achieved with “functional indexes” on virtual columns.

Convenience - The additional inline syntax for JSON columns makes it very natural to integrate Document queries within your SQL. For example (features.feature is a JSON column): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

Community
  • 1
  • 1
adnan
  • 1,385
  • 2
  • 17
  • 31