gis.result

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

SELECT fid, NumInteriorRings(g) FROM gis_polygon;
fid	NumInteriorRings(g)
108	0
109	1
110	0
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
fid	AsText(InteriorRingN(g, 1))
108	NULL
109	LINESTRING(10 10,20 10,20 20,10 20,10 10)
110	NULL
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	3	
Warnings:
Note	1003	select astext(centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS `Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
SELECT fid, IsClosed(g) FROM gis_multi_line;
fid	IsClosed(g)
114	0
115	0
116	0
SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
fid	AsText(Centroid(g))
117	POINT(55.588527753042 17.426536064114)
118	POINT(55.588527753042 17.426536064114)
119	POINT(2 2)
SELECT fid, Area(g) FROM gis_multi_polygon;
fid	Area(g)
117	1684.5
118	1684.5
119	4.5
SELECT fid, NumGeometries(g) from gis_multi_point;
fid	NumGeometries(g)
111	4
112	4
113	2
SELECT fid, NumGeometries(g) from gis_multi_line;
fid	NumGeometries(g)
114	2
115	1
116	2
SELECT fid, NumGeometries(g) from gis_multi_polygon;
fid	NumGeometries(g)
117	2
118	2
119	1
SELECT fid, NumGeometries(g) from gis_geometrycollection;
fid	NumGeometries(g)
120	2
121	2
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	
Warnings:
Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point`
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
fid	AsText(GeometryN(g, 2))
111	POINT(10 10)
112	POINT(11 11)
113	POINT(4 10)
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
fid	AsText(GeometryN(g, 2))
114	LINESTRING(16 0,16 23,16 48)
115	NULL
116	LINESTRING(2 5,5 8,21 7)
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
fid	AsText(GeometryN(g, 2))
117	POLYGON((59 18,67 18,67 13,59 13,59 18))
118	POLYGON((59 18,67 18,67 13,59 13,59 18))
119	NULL
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
fid	AsText(GeometryN(g, 2))
120	LINESTRING(0 0,10 10)
121	LINESTRING(3 6,7 9)
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
fid	AsText(GeometryN(g, 1))
120	POINT(0 0)
121	POINT(44 6)
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	
Warnings:
Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point`
SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
first	second	w	c	o	e	d	t	i	r
120	120	1	1	0	1	0	0	1	0
120	121	0	0	1	0	0	0	1	0
121	120	0	0	1	0	0	0	1	0
121	121	1	1	0	1	0	0	1	0
explain extended SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	
Warnings:
Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
CREATE TABLE t1 (
gp  point,
ln  linestring,
pg  polygon,
mp  multipoint,
mln multilinestring,
mpg multipolygon,
gc  geometrycollection,
gm  geometry
);
SHOW FIELDS FROM t1;
Field	Type	Null	Key	Default	Extra
gp	point	YES		NULL	
ln	linestring	YES		NULL	
pg	polygon	YES		NULL	
mp	multipoint	YES		NULL	
mln	multilinestring	YES		NULL	
mpg	multipolygon	YES		NULL	
gc	geometrycollection	YES		NULL	
gm	geometry	YES		NULL	
ALTER TABLE t1 ADD fid INT NOT NULL;
SHOW FIELDS FROM t1;
Field	Type	Null	Key	Default	Extra
gp	point	YES		NULL	
ln	linestring	YES		NULL	
pg	polygon	YES		NULL	
mp	multipoint	YES		NULL	
mln	multilinestring	YES		NULL	
mpg	multipolygon	YES		NULL	
gc	geometrycollection	YES		NULL	
gm	geometry	YES		NULL	
fid	int(11)	NO		NULL	
DROP TABLE t1;
SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))
POINT(1 4)
explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))`
explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select astext(geometryfromwkb(aswkb(geometryfromtext(_latin1'POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))))`
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
SRID(GeomFromText('LineString(1 1,2 2)',101))
101
explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select srid(geometryfromtext(_latin1'LineString(1 1,2 2)',101)) AS `SRID(GeomFromText('LineString(1 1,2 2)',101))`
explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select issimple(multipoint(point(3,6),point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,issimple(point(3,6)) AS `issimple(Point(3, 6))`
create table t1 (a geometry not null);
insert into t1 values (GeomFromText('Point(1 2)'));
insert into t1 values ('Garbage');
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
insert IGNORE into t1 values ('Garbage');
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
alter table t1 add spatial index(a);
drop table t1;
create table t1(a geometry not null, spatial index(a));
insert into t1 values
(GeomFromText('POINT(1 1)')), (GeomFromText('POINT(3 3)')), 
(GeomFromText('POINT(4 4)')), (GeomFromText('POINT(6 6)'));
select AsText(a) from t1 where
MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
or
MBRContains(GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a);
AsText(a)
POINT(1 1)
POINT(3 3)
POINT(4 4)
select AsText(a) from t1 where
MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a)
and
MBRContains(GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a);
AsText(a)
POINT(1 1)
drop table t1;
CREATE TABLE t1 (Coordinates POINT NOT NULL, SPATIAL INDEX(Coordinates));
INSERT INTO t1 VALUES(GeomFromText('POINT(383293632 1754448)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(564952612 157516260)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(903994614 180726515)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(98128178 141127631)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(862547902 799334546)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(341989013 850270906)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(803302376 93039099)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(857439153 817431356)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(319757546 343162742)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(826341972 717484432)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(305066789 201736238)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(626068992 616241497)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(55789424 755830108)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(802874458 312435220)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(153795660 551723671)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(242207428 537089292)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(553478119 807160039)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(694605552 457472733)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(987886554 792733729)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(598600363 850434457)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(592068275 940589376)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(700705362 395370650)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(33628474 558144514)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(212802006 353386020)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(901307256 39143977)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(70870451 206374045)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(240880214 696939443)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(822615542 296669638)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(452769551 625489999)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(609104858 606565210)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(177213669 851312285)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(143654501 730691787)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(658472325 838260052)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(188164520 646358878)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(630993781 786764883)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(496793334 223062055)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(727354258 197498696)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(618432704 760982731)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(755643210 831234710)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(114368751 656950466)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(870378686 185239202)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(863324511 111258900)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(882178645 685940052)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(407928538 334948195)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(311430051 17033395)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(941513405 488643719)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(868345680 85167906)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(219335507 526818004)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(923427958 407500026)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(173176882 554421738)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(194264908 669970217)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(777483793 921619165)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(867468912 395916497)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(682601897 623112122)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(227151206 796970647)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(280062588 97529892)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(982209849 143387099)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(208788792 864388493)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(829327151 616717329)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(199336688 140757201)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(633750724 140850093)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(629400920 502096404)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(226017998 848736426)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(28914408 149445955)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(256236452 202091290)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(703867693 450501360)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(872061506 481351486)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(372120524 739530418)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(877267982 54722420)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(362642540 104419188)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(851693067 642705127)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(201949080 833902916)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(786092225 410737872)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(698291409 615419376)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(27455201 897628096)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(756176576 661205925)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(38478189 385577496)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(163302328 264496186)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(234313922 192216735)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(413942141 490550373)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(394308025 117809834)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(941051732 266369530)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(599161319 313172256)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(5899948 476429301)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(367894677 368542487)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(580848489 219587743)'));
INSERT INTO t1 VALUES(GeomFromText('POINT(11247614 782797569)'));
drop table t1;
create table t1 select GeomFromWKB(POINT(1,3));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `GeomFromWKB(POINT(1,3))` geometry default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo`
geometry NOT NULL default '') ENGINE=MyISAM ;
Warnings:
Warning	1101	BLOB/TEXT column 'geo' can't have a default value
insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363
36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163
36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363
36.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364
36.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882
36.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207
36.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017
36.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072
36.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755