how to design a database's table to store mysql spatial data in a parent child relation? I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon.

Examples:

1. World
1.1 US
1.2 China
1.3 Japan
1.4 South Africa
1.4.1 Pretoria
1.4.2 Cape Town
1.5 India
1.5.1 New Delhi
1.5.2 Chennai


etc.

CREATE TABLE IF NOT EXISTS `geom` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `g` geometry DEFAULT NULL,

  `location` varchar(100) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;


select * 
from geom 
where st_contains(g,(select g from geom where id =3))and id!=3
Your location table should have a self-reference. Use the Polygon1 type for your geography.

eg:

LocationId (Primary key, not null)
LocationSequence (int, not null)
LocationName (string, not null)
parentLocationId (int, can be null)
Geographical data (POLYGON , not null)


So, your table might have:

1001, 1, World, null, ?
1002, 1, US, 1001, ?
1003, 2, China, 1001, ?
1004, 3, Japan, 1001, ?
1005, 4, South Africa, 1001, ?
1006, 1, Pretoria, 1005, ?
1007, 2, Cape Town, 1005, ?
1008, 5, India, 1001, ?
1009, 1, New Delhi, 1008, ?
1010, 2, Chennai, 1008, ?