如何設計一個數據庫的表來以父子關係儲存mysql空間數據？

我想以父子關係儲存位置和多邊形數據。我在 MySQL 和Google地圖中使用空間數據作為多邊形。

例子：

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

等等。

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

您的位置表應該有一個自我參考。將 Polygon 1類型用於您的地理位置。

例如：

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

所以，你的桌子可能有：

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, ?

從您的幾何表中填充下表

CREATE TABLE location (
   parentLocationID INT, 
   LocationId INT NOT NULL, 
   LocationName VARCHAR(100) NOT NULL, 
   Location GEOMETRY NOT NULL
);

以下插入應該可以工作。編輯：我添加了一個緩衝區來解決 ST_CONTAINS錯誤連結的錯誤

-- Insert Top Level Locations
-- This is all geometries that are not contained by another geometry
INSERT INTO location (LocationId, LocationName, Location)
SELECT pp.id, pp.location, pp.g
FROM geom AS pp
WHERE NOT EXISTS (
   SELECT 1
   FROM geom AS pt
   WHERE ST_CONTAINS(Buffer(pt.g,1), pp.g) = 1 AND -- Choose appropriate buffer value
       pt.id != pp.id
   );

-- Insert children
-- The join will return all ancestor geometries for the pc geom
-- The not exists statement removes all but the immediate ancestors for the pc geom
-- This is likely to run for quite some time
INSERT INTO location (parentLocationID, LocationId, LocationName, Location)
SELECT 
    pp.id , pc.id id, pc.location, pc.g
FROM geom AS pp
   INNER JOIN geom AS pc ON ST_CONTAINS(Buffer(pp.g,1),pc.g) = 1 AND -- Choose appropriate buffer value
       pp.id != pc.id
WHERE NOT EXISTS (
   SELECT 1
   FROM geom AS pt
   WHERE ST_CONTAINS(Buffer(pt.g,1),pc.g) = 1 AND -- Choose appropriate buffer value
       ST_CONTAINS(Buffer(pp.g,1),pt.g) = 1 AND -- Choose appropriate buffer value
       pt.id != pc.id AND 
       pt.id != pp.id
   );

我已經在 SQL Server 上使用以下虛擬多邊形對它進行了 4 個級別的測試，但要注意表中是否有多個相等的幾何圖形。它可能會創建一個循環關係。

INSERT INTO geom (g, location)
VALUES
(GeomFromText('POLYGON ((0 0, 1000 0, 1000 1000, 0 1000, 0 0))'),'Level 1 Root')
,(GeomFromText('POLYGON ((750 750, 950 750, 950 950, 750 950, 750 750))'),'Level 2 NE')
,(GeomFromText('POLYGON ((50 750, 250 750, 250 950, 50 950, 50 750))'),'Level 2 NW')
,(GeomFromText('POLYGON ((400 400, 600 400, 600 600, 400 600, 400 400))'),'Level 2 Center')
,(GeomFromText('POLYGON ((50 50, 250 50, 250 250, 50 250, 50 50))'),'Level 2 SW')
,(GeomFromText('POLYGON ((750 50, 950 50, 950 250, 750 250, 750 50))'),'Level 2 SE')
,(GeomFromText('POLYGON ((100 100, 250 100, 250 250, 100 250, 100 100))'),'Level 3 SW1')
,(GeomFromText('POLYGON ((50 50, 100 50, 100 100, 50 100, 50 50))'),'Level 3 SW2')
,(GeomFromText('POLYGON ((750 50, 850 50, 850 100, 750 100, 750 50))'),'Level 3 SE1')
,(GeomFromText('POLYGON ((900 100, 950 100, 950 150, 900 150, 900 100))'),'Level 3 SE2')
,(GeomFromText('POLYGON ((150 150, 200 150, 200 200, 150 200, 150 150))'),'Level 4 SW1');

如果 mySQL 的語法不正確，我深表歉意。

引用自：https://dba.stackexchange.com/questions/73725