1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
# Create a table with three columns:
# 1) Normal POINT column without SRID specification
# 2) Normal POINT column with SRID 0
# 3) Normal POINT column with SRID 4326
CREATE TABLE t1 (no_srid POINT DEFAULT NULL,
srid_0 POINT SRID 0 DEFAULT NULL,
srid_4326 POINT SRID 4326 DEFAULT NULL);
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 srid_0 0 point
def test t1 srid_4326 WGS 84 4326 point
# Insert some data with correct SRID into these columns
INSERT INTO t1 (no_srid, srid_0, srid_4326)
VALUES (ST_GeomFromText('POINT(0 0)', 0),
ST_GeomFromText('POINT(0 0)', 0),
ST_GeomFromText('POINT(0 0)', 4326));
INSERT INTO t1 (no_srid, srid_0, srid_4326)
VALUES (ST_GeomFromText('POINT(1 1)', 4326),
ST_GeomFromText('POINT(1 1)', 0),
ST_GeomFromText('POINT(1 1)', 4326));
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 srid_0 0 point
def test t1 srid_4326 WGS 84 4326 point
SELECT ST_SRID(no_srid) AS no_srid,
ST_SRID(srid_0) AS srid_0,
ST_SRID(srid_4326) AS srid_4326 FROM t1;
no_srid srid_0 srid_4326
0 0 4326
4326 0 4326
[Connection Master]
# Removing the SRID specification from a column should work just fine.
ALTER TABLE t1 CHANGE COLUMN srid_4326 no_srid_2 POINT DEFAULT NULL;
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 no_srid_2 NULL NULL point
def test t1 srid_0 0 point
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 no_srid_2 NULL NULL point
def test t1 srid_0 0 point
SELECT ST_SRID(no_srid) AS no_srid,
ST_SRID(no_srid_2) AS no_srid_2,
ST_SRID(srid_0) AS srid_0 FROM t1;
no_srid no_srid_2 srid_0
0 4326 0
4326 4326 0
[Connection Master]
DELETE FROM t1;
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
[Connection Master]
ALTER TABLE t1 CHANGE COLUMN srid_0 srid_4326 POINT SRID 4326 DEFAULT NULL;
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 no_srid_2 NULL NULL point
def test t1 srid_4326 WGS 84 4326 point
INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
VALUES (ST_GeomFromText('POINT(0 0)', 2000),
ST_GeomFromText('POINT(0 0)', 0),
ST_GeomFromText('POINT(0 0)', 4326));
INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
VALUES (ST_GeomFromText('POINT(1 1)', 4326),
ST_GeomFromText('POINT(1 1)', 2000),
ST_GeomFromText('POINT(1 1)', 4326));
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 no_srid NULL NULL point
def test t1 no_srid_2 NULL NULL point
def test t1 srid_4326 WGS 84 4326 point
SELECT ST_SRID(no_srid) AS no_srid,
ST_SRID(no_srid_2) AS no_srid_2,
ST_SRID(srid_4326) AS srid_4326 FROM t1;
no_srid no_srid_2 srid_4326
2000 0 4326
4326 2000 4326
[Connection Master]
# Cleanup
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
# The SRID property on a generated column should work
[Connection Master]
CREATE TABLE t1 (col1 POINT SRID 4326,
col2 POINT AS (ST_SRID(col1, 0)) SRID 0);
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 4326));
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 col1 WGS 84 4326 point
def test t1 col2 0 point
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 col1 WGS 84 4326 point
def test t1 col2 0 point
SELECT ST_SRID(col1) AS srid_4326, ST_SRID(col2) AS srid_0 FROM t1;
srid_4326 srid_0
4326 0
4326 0
[Connection Master]
DELETE FROM t1;
ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 2000;
ALTER TABLE t1 CHANGE COLUMN col2 col2 POINT AS (ST_SRID(col1, 4326)) SRID 4326;
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 2000));
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 2000));
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
def test t1 col2 WGS 84 4326 point
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/diff_tables.inc [master:t1, slave:t1]
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
def test t1 col2 WGS 84 4326 point
SELECT ST_SRID(col1) AS srid_2000, ST_SRID(col2) AS srid_4326 FROM t1;
srid_2000 srid_4326
2000 4326
2000 4326
[Connection Master]
# Cleanup
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
include/rpl_end.inc
|
