CODEWITHSUNDEEP
phpjavascriptsqldatabasejson
nielsv
nielsv

Reputation: 6820

Collection of coordinates in table field (database)

I have a JSON file like this:

{
    "wijken": {
        "11": {
            "coords": "3.79073170001967,51.1717753664505,0 3.79020920176376,51.1723018883706,0 3.78989543642226,51.1729670713336,0 3.78983091856725,51.1736482209016,0 3.79035112720225,51.174896701853,0 3.79089521566258,51.1755715564749,0 3.7915999971046,51.1759188204827,0 3.79248490395883,51.1767623261609,0 3.79147318323356,51.1776832394604,0 ",
            "id": "kml_1",
            "fid": "0",
            "wijziging": "Ja",
            "nieuwnr": "11",
            "naam": "Noordoost",
            "wijk": "Kanaaldorpen en -zone",
            "wijknr": "11",
            "objectid": "1",
            "area": "0",
            "len": "0"
        }
...

Now I want to load this data in my database. This works for "id" and "wijk". But how can I load "coords" into my table field?

And what are the properties of the field? (decimal, int, ... ?)

Upvotes: 1

Views: 223

Answers (4)

Minko Gechev
Minko Gechev

Reputation: 25682

Following the database normalization principles (more accurate the first normal form) you should have anatomical structure of your fields, i.e. each column should be responsible for the representation of a single value.

Depending on your domain each coordinate can be inside different column or even table...

For example if you have a domain object City then the table City might look something like:

________________________________________________
|---|----------|-----------|------|------------|
|id | latitude | longitude | name | population |
|___|__________|___________|______|____________|
  • Note the following principles are applies for relational databases.

Upvotes: 0

Hieu Le
Hieu Le

Reputation: 8415

What is you DBMS?

If you use a Relational DBMS like MySQL, save the data in a text field and you PHP implode and explode function to manipulate this data. You can also save it in another separated table and call the JOIN expression in SQL query (with the help of GROUP BY and group_concat()) to get the data.

If you use a document-based DBMS like MongoDB, feel free to save the coords field as a array of float.

Upvotes: 0

César García Tapia
César García Tapia

Reputation: 3376

What database are you using? Some engines (like MS SQL Server) have their own type for that: GEOGRAPHY

If you are using MySQL or postgreSQL the usual way is creating a new table for that, as you can see in the Google Maps API documentation:

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL ,
  `type` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

Upvotes: 1

Dutow
Dutow

Reputation: 5668

You could lood coords as a single text field, or you may create a separate table for it.

Upvotes: 1

Related Questions