CODEWITHSUNDEEP
mysqlarraysjson
ForJ9
ForJ9

Reputation: 745

Use JSON Array in MySQL

I have put data from openstreetmap to my database. My goal is it to find a street, which coordinates are given in a JSON-Array format and is in a radius of n-Meter.

The street data looks like this and is available in one cell.

[[7.9808973,52.4834961],[7.9807604,52.4819775],[7.9808057,52.4793785],[7.983775,52.4783549]]

What is the best way to decode this JSON-Array, so I can use this e.g. in a WHERE-Clause. I don't want to do this in a client, because of a lot of data.

Upvotes: 0

Views: 91

Answers (1)

Arth
Arth

Reputation: 13110

I'm assuming you have a bunch of streets in a table like this:

  • street - id, name, data

where data is a JSON array of points represented by an array of an x and a y coordinate.

This is going to be horribly complicated to work with and as you can't put an index on any of the spatial coordinates, performance will be severely limited.

At the very least I would suggest importing and storing the openstreetmap data differently, perhaps in two tables:

  • street - id, name
  • street_vertex - street_id, ordinal, x, y

Where ordinal is the position of the vertex in the street's list of vertices. This will make the data much easier to work with.

You could quite happily extend this to work for other line-like features (rivers?):

  • polyline - id, type_id, name
  • polyline_type - id, name
  • polyline_vertex - polyline_id, ordinal, vertex_id
  • vertex - id, x, y

If you are using more than a trivial amount of spatial information, however, I would suggest looking at spatial extensions to MySQL, which has a bunch of datatypes like this built-in.

Upvotes: 1

Related Questions